Sqlite3 - How to import NULL values from csv

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",

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';