I have found it much simpler and more robust to write a CSV virtual table to handle the formatting.
e.g. CREATE VIRTUAL TABLE csv_input USING csv ( <filename> ); Opens the named file, reads the first line and interprets the contents as a list of field names. You can then INSERT INTO <table> <fieldlist> SELECT <fieldlist> FROM csv_input; CREATE VIRTUAL TABLE csv_output USING csv ( <filename>, <fieldlist> ); Creates the named file and writes the fieldlist, You can then INSERT INTO csv_output SELECT ...; For ease of implementation I just handle NULL (do not write anything), TEXT (including text rendering of numeric values; write the result of sqlite3_value_text) and BLOB (encode the result of sqlite3_value_blob in x'...' format). Any TEXT that contains non-printable characters or the chosen delimiter is treated as a BLOB. No fussing around with quotes, doubling quotes, escaping, etc. and the conversion of values is handled by SQLite. The same approach can be used to implement whatever flavor of CSV you prefer. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Ketil Froyn Gesendet: Mittwoch, 14. Juni 2017 10:03 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Outputting to CSV - row is not quoted when there are no spaces Dan, I'd recommend using a tool that actually understands CSV, and make it parse the input and create new quoted output. I haven't seen your sed and awk, but I'm pretty sure it's easy to find some special cases where the text includes comma, quotes or even newlines that will break your output. A simple stab at what I think you need would be something like this python3 script: $ cat quotecsv.py #!/usr/bin/env python3 import csv import sys csv_in = csv.reader(sys.stdin, delimiter=',', quotechar='"') csv_out = csv.writer(sys.stdout, delimiter=',', quotechar='"', quoting=csv.QUOTE_ALL) for row in csv_in: csv_out.writerow(row) ### That's it ### Then you can pipe some csv through this, here's a sample line where fields are only quoted where necessary, and the result after piping through the python script: $ echo 'a,b,c,"d e","f, g"' a,b,c,"d e","f, g" $ echo 'a,b,c,"d e","f, g"' | ./quotecsv.py "a","b","c","d e","f, g" Cheers, Ketil On 14 June 2017 at 07:46, <d...@dan.bz> wrote: > Thanks Richard, in the end I added the quotes using some SED and AWK > as I'm using SQLite as part of a BASH script. > > Thanks > > > On 2017-06-13 14:36, Richard Hipp wrote: >> >> SQLite does not provide that capability, that I recall. >> >> But surely it would not be too difficult for you to do your own >> custom patch, or even to write a short program to output the data in >> the precise format you desire? >> >> On 6/12/17, d...@dan.bz <d...@dan.bz> wrote: >>> >>> Hi, >>> >>> When outputting to CSV with '.mode csv' is there a way that all rows >>> can be quoted even if there are no spaces? For example, here is a 1 >>> line from the output: >>> >>> spotify:track:5vlDIGBTQmlyfERBnJOnbJ,Kiso,Circles,100.019 >>> >>> I would like it to output: >>> >>> "spotify:track:5vlDIGBTQmlyfERBnJOnbJ","Kiso","Circles",100.019 >>> >>> Thanks! >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- -Ketil _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
