> Op 11 mrt. 2019, om 13:20 heeft Graham Holden <[email protected]> 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
