Sqlite3 - How to import NULL values from csv

Question

I have dumped a mysql table as CSV. In this CSV file, the NULL values are written as \N
Now I want to import this data into sqlite database, but I am unable to tell sqlite that \N are null values. It is treating it as a string and that column value is stored as "\N" instead of NULL.

Can anyone guide in how to use .nullvalue dot command from sqlite. I am unable to set \N as nullvalue.

sqlite> .show
nullvalue: ""
sqlite> .nullvalue \N
sqlite> .show
nullvalue: "N"
sqlite> .nullvalue '\N'
sqlite> .show
nullvalue: "\\N"
sqlite> .nullvalue "\N"
sqlite> .show
nullvalue: "N"
sqlite> .nullvalue \\N
sqlite> .show
nullvalue: "\\N"
sqlite> .nullvalue '\'N
Usage: .nullvalue STRING
sqlite> .nullvalue '\\'N
Usage: .nullvalue STRING
sqlite> .nullvalue \\N
sqlite> .show
nullvalue: "\\N"
sqlite>

This is the output after every value of nullvalue

sqlite> .import /tmp/mysqlDump.csv employee
sqlite> select count(*) from employee where updatedon='\N';
94143
sqlite> select count(*) from employee where updatedon is null;
0

How can I tell sqlite to treat \N as NULL value? I cannot use empty string as NULL value as my data contains empty strings.

Answer 1

When sqlite is configured to check foreign key references, a method that relies on doing an update after the import is not possible, because the import would fail (the foreign key constraint would fail) and there would be no row to update.

For that, and the cases where doing an update after the import is unacceptable, you must modify the shell.c file in the amalgamation (as shown below), and compile a new sqlite(.exe) binary.

The change to be made is to bind the parameter for the field to NULL when the field is empty (with sqlite3_bind_null) instead of unconditionally binding it as a text field with sqlite3_bind_text as is currently done.

An example of making that change to sqlite version v 3.33.0 2020-08-14 is below (as a patch diff).

The example has the changes behind a new compile-time option, SQLITE_IMPORT_NULL_IF_EMPTY, so to enable it you'd need to define it when compiling like so:

cc -DSQLITE_IMPORT_NULL_IF_EMPTY <other options> shell.c sqlite3.c -o sqlite3

Example full compilation command with recommended options (and a few others set):

cc -Os -DSQLITE_IMPORT_NULL_IF_EMPTY -DSQLITE_DQS=0 -DSQLITE_THREADSAFE=0 -DSQLITE_DEFAULT_MEMSTATUS=0 -DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1 -DSQLITE_LIKE_DOESNT_MATCH_BLOBS -DSQLITE_MAX_EXPR_DEPTH=0 -DSQLITE_OMIT_DECLTYPE -DSQLITE_OMIT_DEPRECATED -DSQLITE_OMIT_PROGRESS_CALLBACK -DSQLITE_OMIT_SHARED_CACHE -DSQLITE_USE_ALLOCA -DSQLITE_OMIT_AUTOINIT -DSQLITE_DEFAULT_FOREIGN_KEYS=1 -DSQLITE_ENABLE_NULL_TRIM -DSQLITE_ENABLE_RBU -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_DBSTAT_VTAB -DSQLITE_ENABLE_EXPLAIN_COMMENTS -DHAVE_USLEEP -DHAVE_READLINE shell.c sqlite3.c -lreadline -lncurses -o sqlite3

Patch file contents:

--- sqlite-amalgamation-3330000/shell.c 2020-08-14 13:42:48.000000000 +0000
+++ shell.c     2020-10-07 13:23:39.000000000 +0000
@@ -17845,7 +17845,12 @@
         ** the remaining columns.
         */
         if( p->mode==MODE_Ascii && (z==0 || z[0]==0) && i==0 ) break;
+#ifdef SQLITE_IMPORT_NULL_IF_EMPTY
+        if (z==0 || z[0]=='\0') sqlite3_bind_null(pStmt, i+1);
+        else sqlite3_bind_text(pStmt, i+1, z, -1, SQLITE_TRANSIENT);
+#else
         sqlite3_bind_text(pStmt, i+1, z, -1, SQLITE_TRANSIENT);
+#endif
         if( i<nCol-1 && sCtx.cTerm!=sCtx.cColSep ){
           utf8_printf(stderr, "%s:%d: expected %d columns but found %d - "
                           "filling the rest with NULL\n",

Answer 2

CSV files contain only text values. It is not possible to import NULL values from a CSV file.

To convert the \N values into NULLs, just use UPDATE afterwards:

UPDATE employee SET updatedon = NULL WHERE updatedon = '\N';