I'm using sqlite 3.0.7 on OS X and I'm having a hard time using the .import function to import data which includes NULL values.
I made a test database to describe the problem.
This is the schema of the table:
sqlite> .schema
CREATE TABLE quest (id integer primary key default null, sesso, eta, dom1, dom2, dom3, dom4);
As you can see it's pretty basic, the only notable thing is the auto-incremental "id" field.
The "quest" table already has some data, and inserting new records using SQL works just fine:
sqlite> select * from quest;
1|m|40|a|b|a|c
2|m|40|a|b|a|c
sqlite> insert into quest values(null,'f',25,'a','b','c','d');
sqlite> insert into quest(sesso,eta,dom1,dom2,dom3,dom4) values('f', 32, 'c', 'd', 'a', 'a');
sqlite> select * from quest;
1|m|40|a|b|a|c
2|m|40|a|b|a|c
3|f|25|a|b|c|d
4|f|32|c|d|a|a
The problem arises when using .import. I have a hand-made CSV file, and it looks like this:
innocence:~/Temp jollino$ cat valori.csv null,f,35,c,d,a,a null,f,48,a,b,c,d null,m,22,b,c,d,a
but sqlite doesn't like it:
sqlite> .import valori.csv quest Error: datatype mismatch
I tried removing "null" from, therefore having a literally null field, and it still doesn't work:
innocence:~/Temp jollino$ cat valori.csv ,f,35,c,d,a,a ,f,48,a,b,c,d ,m,22,b,c,d,a
sqlite> .import valori.csv quest Error: datatype mismatch
I even tried using a 0 for that field, but of course it complains:
innocence:~/Temp jollino$ cat valori.csv 0,f,35,c,d,a,a 0,f,48,a,b,c,d 0,m,22,b,c,d,a
sqlite> .import valori.csv quest Error: PRIMARY KEY must be unique
And I tried removing the field altogether, to no avail:
innocence:~/Temp jollino$ cat valori.csv f,35,c,d,a,a f,48,a,b,c,d m,22,b,c,d,a
sqlite> .import valori.csv quest valori.csv line 1: expected 7 columns of data but found 6
So the question is: how do I import NULL values?
I searched the mailing list archives and I found that the same question has been asked a month ago, but it got no replies. (http://www.mail-archive.com/sqlite-users@sqlite.org/msg05168.html)
I also had a look at the documentation but I couldn't find any detailed explanation for the .dot commands at all, but I might have missed it.
Could anyone point me to the right direction, please?
Thanks
Daniele -- Daniele Nicolucci (Jollino) Photo portfolio: http://www.nicolucci.net Sarà anche vero che vivere sulla Terra è caro, ma il prezzo include un viaggio gratis intorno al Sole ogni anno.
