SQLite - insert a row with new rows into a database from a csv file

Question

Everything,

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

I can get new lines if I do INSERT manually:

INSERT INTO "LOGENTRY" VALUES(5,40,'PLACE','line1 line2 line4 ',1283990533315,'4A','TEXT','',NULL); 

but if I have equivalent text in a CSV file and try to import it into a table, I get a message that it expects more columns than exists (as soon as a new line is encountered, the program assumes the end of the input and, therefore, the absence of columns).

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

Answer 1

Your problem in csv: sqlite format does not support values ​​containing newlines.

In the sqlite dialog box:

• Block individual fields

• Double quotation marks can be used to group a field containing commas.

• New lines restrict entries

You need to either print / delete new lines, or use another more suitable file format.

The next (sed) regular expression will "delete" unwanted lines.

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

Answer 2

Just a simple googling gave me the result

http://www.mail-archive.com/ [e-mail] /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 this helps!

Answer 3

If it is included in the application, I would use a parameterized / prepared statement, at least for those values ​​that are not constants:

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

Answer 4

Try "echo -n" or use something like echo "file" | tr '\ n' ''

Sincerely.