SQLite - inserting a string with newlines into database from csv file

Question

All,

I am trying to inject a long text entry into a SQLite database, in a TEXT field. This text has new lines in it (i.e. it spans multiple paragraphs).

I can get the new lines to show up if I do the INSERT manually:

INSERT INTO "LOGENTRY" VALUES(5,40,'PLACE','line1
line2

line4
',1283990533315,'4A','TEXT','',NULL);

but if I have the equivalent text in a CSV file and attempt to .import it into the table, I get an error that it's expecting more columns than exist (as soon as the new line is encountered, the program assumes it's the end of the input and thus it's missing columns).

Is this possible? Or is the only way to manually do each INSERT?

Answer 1

Your problem is the csv format: sqlite doesn't support values containing newlines.

In the sqlite dialect:

• Commas separate fields

• Double quotes can be used to group a field which contains commas.

• New lines delimit records

You either need to escape/remove your new lines, or, use some other more suitable file format.

The following (sed) regular expresion will "delete" unwanted line feeds

s/\(,"[^",]*\)$/\1\1/g

Answer 2

Just simple googling gave me the result

http://www.mail-archive.com/[email protected]/msg43557.html

Yes you can:

SQLite version 3.6.14.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table x ( a );
sqlite> INSERT INTO x VALUES('line1
   ...> line2');
sqlite> SELECT a FROM x;
line1
line2
sqlite> SELECT hex(a) FROM x;
6C696E65310A6C696E6532
sqlite>

Hope it helps!