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

2017-06-15 Thread dan 
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 (  );

Opens the named file, reads the first line and interprets the contents
as a list of field names. You can then

INSERT INTO   SELECT  FROM csv_input;


CREATE VIRTUAL TABLE csv_output USING csv ( ,  );

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 
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,   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  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


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2017-06-15 Thread dan 

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,   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  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


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2017-06-14 Thread 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 (  );

Opens the named file, reads the first line and interprets the contents as a 
list of field names. You can then

INSERT INTO   SELECT  FROM csv_input;


CREATE VIRTUAL TABLE csv_output USING csv ( ,  );

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 
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,   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  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

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

2017-06-14 Thread 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,   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  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

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

2017-06-13 Thread 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, 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

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

2017-06-12 Thread Richard Hipp 
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  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
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

2017-06-12 Thread 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users