3

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.

Gulbahar
  • 5,343
  • 20
  • 70
  • 93

2 Answers2

6

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.

Ebow Halm
  • 321
  • 2
  • 5
  • Thanks for the explanation. Do you know if ascii mode can read in utf8 in the non-ascii range correctly? – Mark Jun 26 '21 at 00:15
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
);
Vijay B
  • 342
  • 2
  • 10
  • The original question states, the error message is "input.csv:23: unescaped " character" That means, line 23 is to be checked. I have experienced such error if there were space present out of double quote: "123"; "Foo" – Balazs Papp Jul 15 '21 at 20:46