Re: [sqlite] Difference between ".import" and tcl/sqlite3 "copy"
Monday, March 11, 2019, 6:59:56 PM, E.Pasma 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Difference between ".import" and tcl/sqlite3 "copy"
> Op 11 mrt. 2019, om 13:20 heeft Graham Holden 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Difference between ".import" and tcl/sqlite3 "copy"
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
