The doc on this shell feature shows what is happening. I agree that it would be more convenient if the .import could figure out which columns in the receiving table are susceptible to update, then try the insert if the .csv has the right column count. It is unlikely to do something that sophisticated, which would require parsing the schema.

If your .csv had a column-name header, it would .import very easily into a to-be-created temp table, from which an INSERT statement with a select can take values from that temp table. This can also be easily written. This ease and modularity takes some of the steam out of creating .import enhancements.

In my enhanced shell, when '.header always' is in effect, the .import creates INSERT statement(s) using the .csv (or .tsv) header to name incoming columns, without being picky about mapping them one-to-one with receiving table columns. This is convenient when columns have default or generated values and the file data is short some column(s) as in your case. Unfortunately, I have fallen behind on keeping that shell updated with the last year of enhancements made to shell.c published by the SQLite project.

Another alternative, which I have used, is to create a view and a trigger attached, and then to import into the view. (I did this because I needed special parsing and conversion, although it would probably work for this purpose too.)

That is a pretty clever idea! I'll file it away for future use. In this particular case, it seems to me that the approach I outlined in a sibling post to yours is easier. But yours seems superior if one has to do many of these imports into the same table, as the creation of the view and trigger only has to be done once.

Edit, much later:

I just had an opportunity to try out this idea, and it worked great! Also, after reading up on triggers, it turned out to be almost ridiculously easy to implement. (And if the format of the csv file ever changes, I can just recreate the view to fit the new format.) Thank you!

Ah, thanks; I hadn't quite stopped to think about the difference between information available to the shell and what the core library can discover so easily. Still, however, I don't think the docs explains (to my level of understanding) what happened. I think it all depends what mechanism the shell is using to communicate the tuples to the database layer and telling it what to do with it. As I haven't studied the source, and am not at all familiar with the C API, I really can't know.

Unfortunately, my csv files come from spreadsheets not of my making, so the column headers won't match. But your response (and the other followup) got a handful of my brain cells working again, so I found a better way, demonstrated here with my toy example.

I start sqlite3 with no argument, thus working at first with the in-memory database, then:

▶▶▶ .shell cat n.csv
"fee fie foe foo",bar
1,2
3,4
▶▶▶ .import -csv n.csv tmpn
▶▶▶ attach 'n.db' as n;
▶▶▶ .schema n
CREATE TABLE n.n(a,b,c as (a+b) stored);
▶▶▶ insert into n.n select * from tmpn;
▶▶▶ select * from n;
a  b  c
-  -  -
1  2  3
3  4  7

This seems to me the easiest approach, as the critical parts (.import, attach, insert) are the same no matter the particulars of the database schema and the details of the csv file, so long as the number of columns matches.

In cases where I didn't control the columns of the original database, I often give up trying to import the CSV file directly. Instead import into a table with columns which match the CSV file, then use

INSERT INTO table SELECT ...

to get the data where I want it, then

DELETE FROM …

to remove all rows from the temporary table.

This gives you extreme simplicity of code, making it easy for other people to figure out how things work, but also lets you do almost everything inside SQLite, exploiting how good it is at manipulating data.