Outputting to CSV - row is not quoted when there are no spaces

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
dan
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Outputting to CSV - row is not quoted when there are no spaces

dan
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Outputting to CSV - row is not quoted when there are no spaces

Richard Hipp-3
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, [hidden email] <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
dan
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Outputting to CSV - row is not quoted when there are no spaces

dan
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, [hidden email] <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Outputting to CSV - row is not quoted when there are no spaces

Ketil Froyn
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,  <[hidden email]> 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, [hidden email] <[hidden email]> 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
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
-Ketil
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Outputting to CSV - row is not quoted when there are no spaces

Hick Gunter
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:[hidden email]] Im Auftrag von Ketil Froyn
Gesendet: Mittwoch, 14. Juni 2017 10:03
An: SQLite mailing list <[hidden email]>
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,  <[hidden email]> 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, [hidden email] <[hidden email]> 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
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
-Ketil
_______________________________________________
sqlite-users mailing list
[hidden email]
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: [hidden email]

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
dan
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Outputting to CSV - row is not quoted when there are no spaces

dan
In reply to this post by Ketil Froyn
Thanks Ketil I'll definitely take a look!

FYI my script is available here: https://github.com/64b2b6d12b/spotkey

Thanks,
Dan

On 2017-06-14 18:02, Ketil Froyn wrote:

> 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,  <[hidden email]> 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, [hidden email] <[hidden email]> 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
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
dan
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Outputting to CSV - row is not quoted when there are no spaces

dan
In reply to this post by Hick Gunter
Thanks Hick, I've actually never hear of Virtual Tables so I'll take a
look at play around with this.

Thanks,
Dan

On 2017-06-14 18:44, Hick Gunter wrote:

> 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:[hidden email]] Im Auftrag von
> Ketil Froyn
> Gesendet: Mittwoch, 14. Juni 2017 10:03
> An: SQLite mailing list <[hidden email]>
> 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,  <[hidden email]> 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, [hidden email] <[hidden email]> 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
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> -Ketil
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> 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: [hidden email]
>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...