Import .csv file into table by header in sqlite3

Question

I need to import a .csv file into a table based on the headers of the .csv file. The header of the .csv file contains the fields of the sql table but does not have optional fields. Does sqlite3 have the ability to do this?

For example, the table has the following schema:

CREATE TABLE "Names" (
    id INTEGER PRIMARY KEY,
    first TEXT NOT NULL,
    middle TEXT,
    last TEXT NOT NULL
);

The .csv has contains:

id,first,last
1,"Jane","Doe"
2,"John","Doe"
3,"Nikola","Tesla"

Does sqlite have the ability to load a .csv file that has missing fields into a sqlite table based on the header line in the .csv?

Something along the lines of this: How to import load a .sql or .csv file into SQLite?

you mean from console client? check this out: sqlitetutorial.net/sqlite-import-csv — Flash Thunder
thanks for the link, but that won't work with my dataset since the csv does not include the optional middle field. I'd like to be able to insert fields based on the matching columns specified in the csv header — EarthIsHome

CL. CL. · Accepted Answer · 2017-10-23T08:07:09

The sqlite3 command-line shell can create the table from the column headers.

But if the table already exists, it assumes that all lines in the file contain data, and that the number of columns is the same.

You have to use (or write) some different import tool.

Import .csv file into table by header in sqlite3

1 Answers