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

user_11626568 0
Viewed 863

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.

csv sqlite
2 Answers

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.

user_99521960

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

user_5591440
Related