SQLite Forum

.import csv and NULL values
Login

.import csv and NULL values

(1) By Wolfgang Oertl (w.oertl) on 2021-04-03 20:18:32 [link] [source]

There doesn't seem a way to import NULL values in the shell using the .import command. Missing values are treated as empty strings.

When I import into a table with constraints this might fail if either NULL or a valid value is enforced, with an empty string not being valid. Furthermore, I have to replace empty strings in nullable fields with NULL using UPDATE commands. I think that .import is much faster than .read with lots of INSERT statements.

So, I suggest that .import should provide a new option --nullvalue or so, just like the .nullvalue command is used for exporting. Another idea is that a missing value is NULL, and '' is an empty string. I could probably provide a patch unless there is some objection or a better idea.

(2) By Simon Slavin (slavin) on 2021-04-05 01:27:21 in reply to 1 [link] [source]

I assume you're talking about CSV file format, since your title refers to it.

CSV files can't contain NULL values. The standard describes no way to express NULL in that format. The standard is described here:

https://tools.ietf.org/html/rfc4180

see also

https://en.wikipedia.org/wiki/Comma-separated_values

By all means, invent your own file format, encode NULL however you want, and write your own importer.

In most of the computing languages I use, the NULL value is considered to be different to the empty string "", just as you say.

(3) By Larry Brasfield (larrybr) on 2021-04-05 02:10:59 in reply to 1 [source]

One of the extensions published here, "sqlite3vsv.c" written by Keith Medcalf, creates a virtual table reflecting the content of a delimited text file. Among its useful options is one causing empty fields to be exposed as NULLs.

(4) By Wolfgang Oertl (w.oertl) on 2021-04-05 20:21:10 in reply to 3 [link] [source]

It is an excellent answer to treat empty values as NULL, and "" as empty strings, as no explicit NULL string is required. Of course this behaviour needs to be off by default, only to be enabled with an option. As Simon says, this isn't in the standard, but OTOH it doesn't say there can't be missing values. I'll give it a try.