How to import csv file into sqlite except the first row of csv?

Question

I am trying to import a CSV file into my SQLite table.I have created my SQLite table as:

CREATE TABLE car(id INTEGER PRIMARY KEY, name TEXT, model TEXT);

My CSV file is cars.csv:

Id            Name          Model
1             Car 1         BMW
2             Car 2         Mercedes
3             Car 3         BMW

Now, I am importing the CSV into SQLite using .import cars.csv but it imports all the 4 rows of the CSV file. I am not able to figure out how to import the CSV file without the first row of headers.

Shawn · Accepted Answer · 2022-05-21T07:26:48.660

8

With the sqlite3 shell's .import command, if the first character of a quote-enclosed filename is a |, the rest of the filename is instead treated as a shell command that is executed to produce the data to be imported. So, what I do in this situation is:

sqlite> .import '| tail -n +2 cars.csv' car

The tail invocation will print all but the first line of the file.

If you're using Sqlite 3.32.0 or newer (Released May 2020), the shell can natively ignore a given number of initial lines:

sqlite> .import -skip 1 cars.csv car

It also accepts a --csv option to force CSV mode for just that import, without having to do a .mode csv first.

edited May 21 '22 at 07:26

answered Feb 27 '20 at 17:19

Shawn

47,241
3
26
60

1

Note: this isn't documented anywhere. I discovered it when I went to add that feature. Mentioned it on the mailing list but the devs never updated the docs. – Shawn Feb 27 '20 at 18:09
this doesn't seem to work any more. Tried it on sqlite3. – sandeepkunkunuru May 21 '22 at 05:40
@sandeepkunkunuru Works fine on the latest version. There are better ways in newer versions though. – Shawn May 21 '22 at 07:21
1

(Better way was added a few months after I wrote that answer. Updated to include it as an alternative) – Shawn May 21 '22 at 07:32
the pipe variant didn't work in 3.7.17 verision of sqlite3. I found -skip variant here : https://stackoverflow.com/a/61981659/294552 that didn't work too in 3.7.17 version. However as mentioned in that answer tried it again after upgrading to 3.38.5 and that worked. – sandeepkunkunuru May 21 '22 at 17:35
3.7.17 is ancient. Piping was probably added in the almost decade since then. – Shawn May 21 '22 at 18:06

score -1 · Answer 2 · answered Feb 27 '20 at 15:52

if you can skip the create table step and import the file into a new table which does not exist before, you can import the file, create the table and skip the header row all in one step, if you must create the table before, like in case you do multiple imports of multiple files into same table, then the only option available seems to be import everything and delete the record associated with the first header row ( you know values in there anyway, so it is easy to find and delete ), see here for examples:

SQLite3 Import CSV & exclude/skip header

How to import csv file into sqlite except the first row of csv?

2 Answers2