SQLite import csv file with comma in text fields

Question

I want to import csv file into SQLite db using

sqlite> .separator ,
sqlite> .mode csv data
sqlite> .import test.csv data

where data is the table name with three columns, just like the file.

The file has some string value that are encapsulated using double quotes. Some of the string values have commas in them (actual example from the file "Bond\, James") which should be treated as a single column, but SQLite produces an error

Error: test.csv line 2: expected 3 columns of data but found 4

How can I make SQLite import these values correctly?

Answer 1

I know this is a bit old, but this was the first relevant google search result, so I wanted to share my solution.

Use a different separator, and remove the quotes around values.

sed -i -e 's/","/|/g' -e 's/"$//g' -e 's/^"//g' file.csv

sqlite> .separator "|"
sqlite> .import file.csv tablename

Answer 2

I've experienced this issue myself and found it much much easier to modify my script so that it dumps sql queries as opposed to csv delimited values.

There are problems importing csv data into sqlite3 not only with commas, but also with new line characters.

I would suggest the following:

• Modify your script to produce sql dumps
• Convert the csv dump to sql queries and feed it to sqlite3