Kees Nuyt wrote:
On Wed, 19 Dec 2007 00:14:42 +1100, T&B <[EMAIL PROTECTED]>
wrote:
I think I've discovered yet another bug in the CSV output using the
sqlite3 command line tool.
Here's a sample of the new bug:
.mode csv
select 'a=1,234', 'b=5';
gives:
a=1,234,b=5
but should give:
"a=1,234",b=5
Since no replies, I'll assume this is a bug. I've reported it as:
http://www.sqlite.org/cvstrac/tktview?tn=2850
CSV seems to have quite a history of bugs in SQLite :-/
Tom
Just for the record:
There is a fix in CSV, which will be included in the next
version.
Related Check-ins:
2007-Dec-18 15:41 Check-in [4638] : In the CLI, quote strings
that contain the separator character. Ticket #2850. (By drh)
Ummmm what about quoting the quote character? Line-ending characters
e.g. \r and \n?
sqlite> .mode csv
sqlite> select 1, 'He said "Hello".', 3;
1,"He said "Hello".",3
should be
1,"He said ""Hello"".",3
FWIW here's pseudocode for the "algorithm" that I would use for
determining what to do with each field:
if the field contains a quote_char:
replace each quote-char by TWO quote-chars
prepend and append a quote-char
else if the field contains a separator-char or \r or \n :
prepend and append a quote-char
else:
no action required
Doubling the quote-char may seem to be a weird concept but it's
necessary for unambiguous recovery of the original data. Another example
of the use of this is not far away:
sqlite> select 'O''Brien';
"O'Brien"
sqlite>
Consider what happens when the following lines are fed to an application
that purports to be able to read CSV data (e.g. Open Office Calc, MS
Excel, Gnumeric):
1a,"He said "Hello"."
1b,"He said ""Hello""."
2a,"He said "Hello", she said "Ciao"."
2b,"He said ""Hello"", she said ""Ciao""."
3a,""Dunromin", 123 Main Street"
3b,"""Dunromin"", 123 Main Street"
Only the "b" versions have any chance of being translated back into an
exact copy of what was in the database originally. The "a" versions are
likely to be mangled. The commas in 2a and 3a are likely to cause the
line to be interpreted as containing 3 columns, instead of two.
HTH,
John
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
