Re: [sqlite] [3.5.4] Fails importing CSV file
Gilles wrote: > At 22:47 04/02/2008 +1100, John Machin wrote: > >> Well, obviously(?) you are closer to success with "\t" than with '\t'. >> You probably have an extra unseen TAB. It won't like that NULL. >> > > Thanks, but no matter what I try, it doesn't work: > - just two columns, assuming it will just increment the primary key since > it didn't find anything for this column > - three columns, with col#1 set to "NULL" > - three columns, with col#1 set to just ASCII 09, ie. TAB > If you are using TAB as the separator, col#1 can't be set to TAB -- I presume that you mean that col#1 is empty i.e. a zero-length string. > How is your "barzot.tsv" formatted? How do you handle the first column that > works as an auto-incremented primary key? > Like I said in my message: """ Here's what works for me: C:\junk>type barzot.tsv 1 bar zot 42 plugh xyzzy 666 far narkle = Yes, there are 2 TABs per line. C:\junk>sqlite3 """ It seems that .import doesn't do auto increment. You will have to use Dennis's two-pass technique, or write a small one-pass script in e.g. Python to read your file and insert the rows into the table. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [3.5.4] Fails importing CSV file
Gilles wrote: > At 22:47 04/02/2008 +1100, John Machin wrote: >> Well, obviously(?) you are closer to success with "\t" than with '\t'. >> You probably have an extra unseen TAB. It won't like that NULL. > > Thanks, but no matter what I try, it doesn't work: > - just two columns, assuming it will just increment the primary key since > it didn't find anything for this column > - three columns, with col#1 set to "NULL" > - three columns, with col#1 set to just ASCII 09, ie. TAB > > How is your "barzot.tsv" formatted? How do you handle the first column that > works as an auto-incremented primary key? > Gilles, You can't import into an auto-incremented field. The import command always inserts a value into each column of the table that is being imported into. There is no way to import a null value. Your datatype mismatch error is caused by your attempt to insert the string 'NULL' into the id column. Because this column is declared as integer primary key it can only store integer row id values (unlike all other columns in SQLite). You will need to do your import in two steps. First import into a temp table without the integer primary key column. CREATE TEMP TABLE temp_customer ( tel VARCHAR(32), name VARCHAR(255)); .sepatator \t .import test.csv temp_customer Then copy the data from the temp table into the final table leaving the id unassigned, and finally drop the temp table insert into customer select null, tel, name from temp_customer; drop table temp_customer; HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [3.5.4] Fails importing CSV file
At 22:47 04/02/2008 +1100, John Machin wrote: >Well, obviously(?) you are closer to success with "\t" than with '\t'. >You probably have an extra unseen TAB. It won't like that NULL. Thanks, but no matter what I try, it doesn't work: - just two columns, assuming it will just increment the primary key since it didn't find anything for this column - three columns, with col#1 set to "NULL" - three columns, with col#1 set to just ASCII 09, ie. TAB How is your "barzot.tsv" formatted? How do you handle the first column that works as an auto-incremented primary key? Thanks anyway. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [3.5.4] Fails importing CSV file
Gilles wrote: > At 02:27 04/02/2008 +0100, Gilles wrote: > >> Thanks for the tip, but I tried that too, with no success: >> > > I tried both: > > sqlite> .separator "\t" > sqlite> .import test.csv customer > test.csv line 1: expected 3 columns of data but found 4 > > sqlite> .separator '\t' > sqlite> .import test.csv customer > test.csv line 1: expected 3 columns of data but found 1 > > > Well, obviously(?) you are closer to success with "\t" than with '\t'. You probably have an extra unseen TAB. It won't like that NULL. Here's what works for me: C:\junk>type barzot.tsv 1 bar zot 42 plugh xyzzy 666 far narkle = Yes, there are 2 TABs per line. C:\junk>sqlite3 SQLite version 3.3.6 Enter ".help" for instructions sqlite> CREATE TABLE customer (id INTEGER PRIMARY KEY AUTOINCREMENT, tel VARCHAR (32), name VARCHAR(255)); sqlite> .separator "\t" sqlite> .import barzot.tsv customer sqlite> select * from customer; 1 bar zot 42 plugh xyzzy 666 far narkle sqlite> .separator , sqlite> select * from customer; 1,bar,zot 42,plugh,xyzzy 666,far,narkle sqlite> .quit HTH, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [3.5.4] Fails importing CSV file
At 02:27 04/02/2008 +0100, Gilles wrote: >Thanks for the tip, but I tried that too, with no success: I tried both: sqlite> .separator "\t" sqlite> .import test.csv customer test.csv line 1: expected 3 columns of data but found 4 sqlite> .separator '\t' sqlite> .import test.csv customer test.csv line 1: expected 3 columns of data but found 1 Gilles. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [3.5.4] Fails importing CSV file
At 23:42 03/02/2008 +, Robert Wishlaw wrote: >Instead of > >.separator "\t" > >try > >.separator '\t' Thanks for the tip, but I tried that too, with no success: # cat test.csv NULL123-4567John Doe (Note: I just replaced 09 with to show you) # sqlite3 db.sqlite SQLite version 3.5.4 Enter ".help" for instructions sqlite> .schema CREATE TABLE customer (id INTEGER PRIMARY KEY AUTOINCREMENT, tel VARCHAR(32), name VARCHAR(255)); CREATE INDEX index_tel ON customer (tel); sqlite> .separator '\t' sqlite> .import test.csv customer test.csv line 1: expected 3 columns of data but found 1 Gilles. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
