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

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!

Answer 2

If it's within the application, I would use a parameterized / prepared statement, at least for those values that are not constant:

db.execSQL("INSERT INTO LOGENTRY " +
    "VALUES(?, ?, ?, ?, ?, ?, ?, '', NULL)", 
    new Object[]{5, 40, place, text, num, x, t2});