sqlite3 import with quotes

Question

I am trying to import a collection of data that has quotes within the fields. They are currently tab separated.

From what I can understand according to the docs (http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles), the sqlite shell should interpret quotes literally and I assume that means I shouldn't have a problem.

I've been running into a problem on this line:

1193782372  Lips Like Sugar (12" Mix)   Echo & the Bunnymen 80's/12": The Extended Collection   a76d9b04-51d9-4672-801f-356ab36dbae7    ccd4879c-5e88-4385-b131-bf65296bf245    1abb270a-e791-407f-a989-ff3ad6f8401c

Since it isn't clear where the tabs are, I've included them in this following line.

1193782372\tLips Like Sugar (12" Mix)\tEcho & the Bunnymen\t80's/12": The Extended Collection\ta76d9b04-51d9-4672-801f-356ab36dbae7\tccd4879c-5e88-4385-b131-bf65296bf245\t1abb270a-e791-407f-a989-ff3ad6f8401c

I'm trying to do the following but getting an error.

sqlite> .separator \t
sqlite> .import ./file.txt table
Error: ./file.txt line n: expected 7 columns of data but found 5

It works without the double quotes, but the quotes are important. I don't seem to be able to escape the quotes with \" either.

How can I properly import the data I want?

Answer 1

Sqlite3's .import tool behaves as if its input is in comma-separated-values format, even if .separator is not a comma. You can escape quotes (") by doubling them ("") and quoting the entire field they occur in.

I got a clean .import of your test data after running it through the following filter:

sed 's/"/""/g;s/[^\t]*/"&"/g' file.txt >quoted.txt

Answer 2

The sqlite3 command-line tool isn't very flexible in what import formats it supports.

You could

• change the import file to add double quotes around and escape double quotes in fields; or

• convert the import file into a series of SQL statements:

  INSERT INTO MyTable VALUES(
      1193782372,
      'Lips Like Sugar (12" Mix)',
      'Echo & the Bunnymen 80''s/12": The Extended Collection',
      'a76d9b04-51d9-4672-801f-356ab36dbae7',
      'ccd4879c-5e88-4385-b131-bf65296bf245',
      '1abb270a-e791-407f-a989-ff3ad6f8401c');
  

  or

• write your own import tool.