How to append data from csv file to sqlite?

Question

I'm trying to add rows from a CSV file to an existing table.

In Mac Terminal, I start sqlite3:

sqlite> .open database.db

sqlite> .import test.csv table

. . .

test.csv:43200: expected 22 columns but found 1 - filling the rest with NULL

sqlite>

Notice there are 43200 rows in the csv file. The file itself has 22 comma separated columns, but sqlite only thinks there is 1 column.

Maybe your CSV file is not well formatted. Try to open it with notepad and check if every column are separated with comma. — probitaille
Here are the first 2 columns from the first row: 2019/03/21 00:00:01.813,-0.142040252686 all subsequent columns have the comma. In the first column is my timestamp. Is it possible that the space between the date and time is causing the problem? — cgaters
Your CSV is correct. I think your problem is related to this one: stackoverflow.com/questions/8878697/… — probitaille
In terminal, at the sqlite> prompt, type: .mode csv By default the mode is list. In list mode, the separator appears to be a pipe "|" — cgaters

probitaille probitaille · Accepted Answer · 2019-03-27T13:04:09

According to the finds of Cgaters and the official documentation of SQLite:

Use the ".import" command to import CSV (comma separated value) data into an SQLite table. The ".import" command takes two arguments which are the name of the disk file from which CSV data is to be read and the name of the SQLite table into which the CSV data is to be inserted.

Note that it is important to set the "mode" to "csv" before running the ".import" command. This is necessary to prevent the command-line shell from trying to interpret the input file text as some other format.

Example:

sqlite> .mode csv
sqlite> .import C:/work/somedata.csv tab1

How to append data from csv file to sqlite?

1 Answers