Import CSV to SQLite

Question

I'm trying to import a csv file to an SQLite table.

Example csv:

1,2
5,6
2,7

Example command:

sqlite> create table foo(a, b);
sqlite> separator ,
sqlite> .import test.csv foo
Error: test.csv line 1: expected 2 columns of data but found 4

I'm not even sure why it would find four columns with six pieces of data and two columns. Any help? :)

Answer 1

I am merging info from previous answers here with my own experience. The easiest is to add the comma-separated table headers directly to your csv file, followed by a new line, and then all your csv data.

If you are never doing sqlite stuff again (like me), this might save you a web search or two:

In the Sqlite shell enter:

$ sqlite3 yourfile.sqlite
sqlite>  .mode csv
sqlite>  .import test.csv yourtable
sqlite>  .exit

If you haven't got Sqlite installed on your Mac, run

$ brew install sqlite3

You may need to do one web search for how to install Homebrew.

Answer 2

before .import command, type ".mode csv"

Answer 3

Here's how I did it.

• Make/Convert csv file to be seperated by tabs (\t) AND not enclosed by any quotes (sqlite interprets quotes literally - says old docs)

• Enter the sqlite shell of the db to which the data needs to be added

  sqlite> .separator "\t" ---IMPORTANT! should be in double quotes sqlite> .import afile.csv tablename-to-import-to

Answer 4

What also is being said in the comments, SQLite sees your input as 1, 25, 62, 7. I also had a problem with , and in my case it was solved by changing "separator ," into ".mode csv". So you could try:

sqlite> create table foo(a, b);
sqlite> .mode csv
sqlite> .import test.csv foo

Answer 5

TERMSQL

With termsql you can do it in one line:

termsql -i mycsvfile.CSV -d ',' -c 'a,b' -t 'foo' -o mynewdatabase.db

Answer 6

I had exactly same problem (on OS X Maverics 10.9.1 with SQLite3 3.7.13, but I don't think SQLite is related to the cause). I tried to import csv data saved from MS Excel 2011, which btw. uses ';' as columns separator. I found out that csv file from Excel still uses newline character from Mac OS 9 times, changing it to unix newline solved the problem. AFAIR BBEdit has a command for this, as well as Sublime Text 2.