Insert rewrites valid escaped entries wrong

[derphilipp]

Situation: We have a valid file, with newlines in a string, we can query:

file:

ID,Username,Text,Counter
1,Laura Bow,"What a nice 

newline",0

query:

csvq 'SHOW FIELDS FROM `new.csv`'

results in

                  Fields in new.csv
-----------------------------------------------------
 Type: File
 Path: /Users/phw/dev/csvq/new.csv
 Format: CSV     Delimiter: ','   Enclose All: false
 Encoding: UTF8  LineBreak: LF    Header: true
 Status: Fixed
 Fields:
   1. ID
   2. Username
   3. Text
   4. Counter

also

csvq 'SELECT * FROM `new.csv`'

results in

ID,Username,Text,Counter
1,Laura Bow,What a nice 

newline,0

But if we now enter a new entry:

csvq -k 'INSERT into `new.csv` (ID, Username, Text, Counter) VALUES(2,\'Gloria\',\'sometext\', 0);'

it broke our existing file! (note the missing quotes in the "What a nice ..... newline" line)

ID,Username,Text,Counter
1,Laura Bow,What a nice 

newline,0
2,Gloria,sometext,0

This happens in the database-driver version as well as in the command line client.
Am I missing a parameter / using it wrong?

Thanks for your awesome work!

[mithrandie]

It's a bug.

Csvq automatically encloses a field when delimiters or enclosures are present in the field data. However, if a field contains newline characters, the field is not enclosed.

As a workaround, you can enclose all fields by setting the "Enclose All" attribute of the table to "true" before updating the data.

csvq 'ALTER TABLE `new.csv` SET ENCLOSE_ALL TO TRUE; INSERT into `new.csv` (ID, Username, Text, Counter) VALUES(2, "Gloria", "sometext", 0);'

[derphilipp]

Thank you very much, that workaround really helps a ton!