Is it possible to import a CSV file to an existing table without the headers being included?

Question

I'm trying to import a CSV file to a table that is empty but already exists in an SQLite database. For example:

sqlite> CREATE TABLE data (...);
sqlite> .mode csv
sqlite> .import mydata.csv data

I have created the table in advance because I'd like to specify a primary key, data types, and foreign key constraints. This process works as expected, but it unfortunately includes the header row from the CSV file in the table.

Here's what I've learned from the SQLite docs regarding CSV imports:

There are two cases to consider: (1) Table "tab1" does not previously exist and (2) table "tab1" does already exist.

In the first case, when the table does not previously exist, the table is automatically created and the content of the first row of the input CSV file is used to determine the name of all the columns in the table. In other words, if the table does not previously exist, the first row of the CSV file is interpreted to be column names and the actual data starts on the second row of the CSV file.

For the second case, when the table already exists, every row of the CSV file, including the first row, is assumed to be actual content. If the CSV file contains an initial row of column labels, that row will be read as data and inserted into the table. To avoid this, make sure that table does not previously exist.

So basically, I get extra data because I've created the table in advance. Is there a flag to change this behavior? If not, what's the best workaround?

Fuegian

Answer 1

The sqlite3 command-line shell has no such flag.

If you have a sufficiently advanced OS, you can use an external tool to split off the first line:

sqlite> .import "|tail -n +2 mydata.csv" data

Thorbert

this is the best answer i have seen on the net – Sensorimotor

Answer 2

You can also use the --skip 1 option with .import as documented on the sqlite3 website and this SO Answer. So, you can use the following command

.import --csv --skip 1 mydata.csv data

Whicker

Answer 3

If you skip the headers using |tail or --skip 1, you need to make sure the order of columns in the CSV file matches the order of columns in the SQLite schema. Otherwise, your values will all be silently scrambled.

If they don't match, you can import into a temporary table and then copy:

.import --csv mydata.csv tmp
INSERT INTO data(c1, c2, c3) SELECT c1, c2, c3 FROM tmp;
DROP TABLE tmp

Lumpy