"unescaped " character" when importing text data into SQLite

Question

I'm trying to import a semicolon-separated text file where each line ends in CRLF. The first line contains the fields, and the data start at line 2:

"Field1";"Field2"
"123";"Foo"
"456";"Bar"

I tried the following, but it fails with "input.csv:23: unescaped " character":

sqlite3.exe
sqlite> .separator ";"
sqlite> .mode csv MyTable
sqlite> .import input.csv MyTable

Any idea what I am doing wrong?

Thank you.

Answer 1

For sqlite, when the data to be imported may contain the double-quote character ("), do not use csv mode. The code that reads each CSV field csv_read_one_field looks for it and when it finds it, ensures that it is terminated or expects it to be quoted.

Changing the column separator to ';' won't help because that code will still be executed.

On the other hand, the code that reads each field in ascii mode ascii_read_one_field only uses the column and row separators to determine the field contents.

So, Use ascii mode and set the column and row separators to semi-colon and end of line like this:

*nix:

sqlite> .mode ascii
sqlite> .separator ";" "\n"
sqlite> .import input.csv MyTable

windows:

sqlite> .mode ascii
sqlite> .separator ";" "\r\n"
sqlite> .import input.csv MyTable

However, that will not strip out the double-quotes surrounding your data; they are considered part of your data.

Answer 2

This worked for me:

$ sqlite3 input.sqlite
sqlite>.mode csv
sqlite>.separator ;
sqlite>.import .input.csv input

which created the following schema

sqlite> .schema
CREATE TABLE input(
"Field1" TEXT,
"Field2" TEXT
);

I had similar issues when trying to load a larger CSV file. In that case, csvsql (part of csvkit) did the trick

csvsql --db sqlite:///mydb.sqlite --insert input.csv

This approach had the added benefit of detecting the data types for each column.

sqlite> .schema
CREATE TABLE input (
"Field1" DECIMAL NOT NULL,
"Field2" VARCHAR NOT NULL
);