Hmm. Ok I'll think about munging the data. If I find some time perhaps I'll submit some code to support quoted values in the sqlite3 command line tool.
RW Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald Sent: Thursday, August 21, 2008 5:38 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] .import with .separator and quoted strings Hi Ron, I've encountered that as well. You're using the sqlite3 commandline interface program, I'm sure. I think it was intended as a test and demo utility, but it's found its way into a number of released products. I'm not aware of a way to make this work with the current utility. Since the source is available, you may want to modify it as needed. I know you prefer not to pre-process your input file, so enhancing the source may be your best option. In my case, *all* the fields were quoted in the input file, and so I replaced occurances of: "," (quote comma quote) with a vertical bar | and trimmed the quotes from the beginning and end of each line. You can even perform this using an sqlite3 script itself if you don't mind a bit of madness. -Set the separator to something very odd such as '@$%' -Import the original text to a temporary table with a single field to contain the entire row. -UPDATE each row, using REPLACE() to change "," to | (perhaps after first checking for any actual virgules in the original data). -Use SUBSTR() to remove the two remaining quotes at each end of the line. -Set the separator to | and export to a temp file. Delete the temporary table and .import the data into your real table. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wilson, Ron P Sent: Thursday, August 21, 2008 4:37 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] .import with .separator and quoted strings Here is an easy way to reproduce the symptom. Given the following file as input for the .import command: ---csvtest.csv--- "1","wilson, ron" "2","momma, your" ----------------- Here is the sqlite output: SQLite version 3.5.9 Enter ".help" for instructions sqlite> create table names (id integer, name); .mode csv .import sqlite> csvtest.csv names csvtest.csv line 1: expected 2 columns of data but found 3 sqlite> .quit Clearly it is parsing the comma in the name column as a record delimiter. Is there a mode that causes the .import command to honor quoted entries? RW sqlite>select level from sqlGuruOMeter where name="Ron Wilson"; 2 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wilson, Ron P Sent: Tuesday, August 19, 2008 4:49 PM To: sqlite-users@sqlite.org Subject: [sqlite] .import with .separator and quoted strings I'm trying to import a table using the command line tool. sqlite> .separator , sqlite> .import export.csv library export.csv line 1: expected 53 columns of data but found 77 sqlite> .mode csv sqlite> .import export.csv library export.csv line 1: expected 53 columns of data but found 77 All entries are quoted strings, but some of them have commas within the strings. It appears that SQLite is ignoring the string quoting and taking all commas literally. Is this intended? The same import works fine in Excel with 53 columns resulting. I have also tried tab delimited and apparently some of the strings in this dataset also contain tabs. sqlite> .mode tabs sqlite> .import export.txt library export.txt line 162: expected 53 columns of data but found 55 I don't control the data source, and I would really like to avoid pre-munging the data. RW sqlite>select level from sqlGuruOMeter where name="Ron Wilson"; 2 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
