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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
