Hi Daniele,
Daniele Nicolucci (Jollino) wrote:
Hello,
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?
I checked the code (in src/shell.c). As far as I can see, what you are trying to do isn't possible with the current implementation. The code in question is line 1094 (in version 3.0.8), which I've marked with a star below:
if( i+1!=nCol ){
fprintf(stderr,"%s line %d: expected %d columns of data but found %d\n",
zFile, lineno, nCol, i+1);
zCommit = "ROLLBACK";
break;
}
for(i=0; i<nCol; i++){
* sqlite3_bind_text(pStmt, i+1, azCol[i], -1, SQLITE_STATIC);
}
sqlite3_step(pStmt);
rc = sqlite3_reset(pStmt);
free(zLine);
As you can see, sqlite3_bind_text is used for all columns. You would have to write some ad-hoc code inside the loop that checked whether the value in azCol[i] was "null" or "NULL", and then used sqlite3_bind_null if that was the case, instead of sqlite3_bind_text.
Search for the string "import" in the shell.c code, and you will find the place where this is implemented.
HTH
Ulrik P.
-- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
