Difference between ".import" and tcl/sqlite3 "copy"

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Difference between ".import" and tcl/sqlite3 "copy"

Graham Holden
I'm using SQLite through Tcl, and am having a problem with the
sqlite3/Tcl "copy" command (similar to the shell's ".import" command).

Given "test.csv"
1,"aaa","bbb ccc"

Using the shell, I get the following -- the double-quotes from the CSV
are not stored in the database:

create table test ( id integer primary key, str1 text, str2 text ) ;
.mode csv
.import test.csv test
.mode column
select * from test ;
1           aaa         bbb ccc

but using the following Tcl script:

package require sqlite3
sqlite3 db :memory:
db eval "create table test ( id integer primary key, str1 text, str2 text )"
db copy ignore test test.csv ","
db eval "select * from test" { puts "|$id|$str1|$str2:" }
db eval "update test set str1='aaa', str2='bbb ccc'"
db eval "select * from test" { puts "|$id|$str1|$str2:" }

produces:
|1|"aaa"|"bbb ccc":
|1|aaa|bbb ccc:

showing the double-quotes are stored in the database.

Is there a way to not get the double-quotes stored? NOTE: It may be a
"version thing"... the version of SQLite bundled-in with the copy of
Tcl I'm using is a little old (3.8.7.1) whereas the shell is 3.27.2.
Unfortunately, I can't easily switch the Tcl version at the moment
(but I might have to if it is something that's been fixed).

On a related note, under Windows, with CR-LF terminated lines in the
CSV file, the CR also gets stored in the string (that's the reason for
the ":" in the above test script: the value for 'str2' becomes

      "bbb ccc"\r

(with an embedded carriage-return), but I can work around this by
switching to LF-terminated lines.

TIA for any help,
Graham



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Difference between ".import" and tcl/sqlite3 "copy"

E.Pasma

> Op 11 mrt. 2019, om 13:20 heeft Graham Holden <[hidden email]> het volgende geschreven:
>
> I'm using SQLite through Tcl, and am having a problem with the
> sqlite3/Tcl "copy" command (similar to the shell's ".import" command).
>
> Given "test.csv"
> 1,"aaa","bbb ccc"
>
> Using the shell, I get the following -- the double-quotes from the CSV
> are not stored in the database:
>
> create table test ( id integer primary key, str1 text, str2 text ) ;
> .mode csv
> .import test.csv test
> .mode column
> select * from test ;
> 1           aaa         bbb ccc
>
> but using the following Tcl script:
>
> package require sqlite3
> sqlite3 db :memory:
> db eval "create table test ( id integer primary key, str1 text, str2 text )"
> db copy ignore test test.csv ","
> db eval "select * from test" { puts "|$id|$str1|$str2:" }
> db eval "update test set str1='aaa', str2='bbb ccc'"
> db eval "select * from test" { puts "|$id|$str1|$str2:" }
>
> produces:
> |1|"aaa"|"bbb ccc":
> |1|aaa|bbb ccc:
>
> showing the double-quotes are stored in the database.
>
> Is there a way to not get the double-quotes stored? NOTE: It may be a
> "version thing"... the version of SQLite bundled-in with the copy of
> Tcl I'm using is a little old (3.8.7.1) whereas the shell is 3.27.2.
> Unfortunately, I can't easily switch the Tcl version at the moment
> (but I might have to if it is something that's been fixed).
>
> On a related note, under Windows, with CR-LF terminated lines in the
> CSV file, the CR also gets stored in the string (that's the reason for
> the ":" in the above test script: the value for 'str2' becomes
>
>      "bbb ccc"\r
>
> (with an embedded carriage-return), but I can work around this by
> switching to LF-terminated lines.
>
> TIA for any help,
> Graham

Hello,
I can confirm that this has nothing to do with the sqlite version, as it is so in the tcl binding from the current release (3.28.0.). There is no "mode" method or paramater like in the shell.
I'd probably work around this by an update in SQL, after the import. Or import into a view, with an "instead of insert" trigger to insert cleaned data into the table.
Sorry, E. Pasma.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Difference between ".import" and tcl/sqlite3 "copy"

Graham Holden
Monday, March 11, 2019, 6:59:56 PM, E.Pasma <[hidden email]> wrote:

> I can confirm that this has nothing to do with the sqlite version,
> as it is so in the tcl binding from the current release (3.28.0.).
> There is no "mode" method or paramater like in the shell.  I'd
> probably work around this by an update in SQL, after the import. Or
> import into a view, with an "instead of insert" trigger to insert
> cleaned data into the table.  
> Sorry, E. Pasma.

Thanks for the confirmation. For what I'm doing (a mostly one-off
data import) a sufficient work-around is to use Tcl to "exec" the
SQLite shell and have it run ".import".

Regards,
Graham Holden



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users