I speak as someone who has taken much interest in the CLI and its improvement, and also with some charter to do so. Please do not take this response to indicate lack of appreciation regarding improvement suggestions.

Your suggestion, (irrespective of its simple implementation), is attractive. I am tempted to advocate it to the project team. But I have a serious reservation that you or others might be willing to address.¹ I will frame it as a reason to not add the feature, even though that decision has not yet been made.

Currently, the .import command has a narrowly defined function -- to accept well-formed CSV, or equivalents with different field and record separators, and insert them as values into a specified table. It is that simple, without handy features that could be considered an unbounded set when the multitude of semi-structured text input formats are taken into account.

The present (and only) way to deal with adaptation of .import simplicity/limitation to the morass of table-like text formats is to import into a temporary table, then formulate a query to populate a less transient table using that temporary table as a source in the query. Of course, this is some extra "work", but it has a virtue that is often under-appreciated: The technique is simple composition from simpler and logically independent operations.² This simplicity of the individual operations reduces the mental work needed to get the present task done, and certainly reduces the documentation and .help text for the .import command.

If I was assigned the role of arguing against such a change, I would cite that simplicity and assert that a subsequent "transform/insert query" is going to often be necessary anyway. And I would add that transforming fields which equal the present null output representation can be accomplished trivially as part of such a query.

If one accepts the fact that .import is not going to grow ever-more complex to deal with the vast variety of table-like text formats, what argument supports the proposition that this NULL transformation deserves to become part of the now-empty handy feature set?

1. ^{^} I suspect this reservation has already occurred with the other project team members.
2. ^{^} This building block simplicity became popularized with the introduction of Unix to the computing world, and is part of what is known as "the Unix way", or "the Unix philosophy.

Thanks for the careful and thoughtful comments. I see this handling of .nullvalue during .import to be the missing flip side of its handling during printing ("exporting") of query results. It enables round tripping, for example:

$ sqlite3 "" \
    ".mode csv" \
    ".headers on" \
    "create table a (x text, y text, z text);" \
    "insert into a values ('foo', null, 'bar'), ('baz', 'bat', null);" \
    "select * from a;" \
    >/tmp/a

$ cat /tmp/a
x,y,z
foo,,bar
baz,bat,

$ sqlite3 "" \
    ".mode csv" \
    ".headers on" \
    ".import /tmp/a a" \
    "select * from a;" \
x,y,z
foo,"",bar
baz,bat,""

$ sqlite3 "" \
    ".mode csv" \
    ".headers on" \
    ".import --nulls /tmp/a a" \
    "select * from a;" \
x,y,z
foo,,bar
baz,bat,

I see your point, anonymous, but I suppose I see nulls as a special case here: integers and reals (and numeric) benefit from column affinity when importing text values into an existing table. Nulls do not.

Blobs do not either, but their handling is less straightforward than nulls as it's not simple string equality against a known, user-controlled value; it instead requires making a heuristic decision about which values are blobs (all of them? only ones that can't be represented as text?) and which aren't (if any).

Allowing nulls to be simply handled at import time seems like an unqualified win (if a small one) to me. Also, I simply found it strange that .import respects .mode and .separator but not .nullvalue. It seemed like an omission. (There are no equivalents for blob representations.)

I don't really care to argue this much further. I offered a gentle suggestion in the form of a working patch, and it's for the maintainers (or community members) to take or leave as they please.

May I add the observation that MySQL uses "LOAD DATA INFILE" as an analogy to ".import", and it permits a field value == backslash+"N" as a representation of the null value to be stored. So there is a long-standing precedent for the suggestion made by larrybr.

I would like to point out that this is also supported in Postgres, where the COPY command supports a NULL option with a default of \N, used for dumping / loading data to / from a file.

I would personally love to see this patch added to SQLite -- it would simplify a corner case when dealing with NULL values in data dumps.