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
