Re: [sqlite] csv files with unquoted data containing comma's

[Dennis Cote]

Frank van Vugt wrote:
L.S.
Comma seperated files tend to become a bit too seperated when the field data 
contains the character used for seperation while not being quoted ;)

Sqlite 3.5.4 uses shell.c::needsCsvQuote() to determine whether or not to 
quote the field contents, but it doesn't check for the comma...... the 
following patch changes that:

--- shell.c.orig        2007-11-30 02:28:11.000000000 +0100
+++ shell.c     2008-01-30 11:29:29.000000000 +0100
@@ -441,7 +441,7 @@
 static const char needCsvQuote[] = {
   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,
   1, 1, 1, 1, 1, 1, 1, 1,   1, 1, 1, 1, 1, 1, 1, 1,
-  1, 0, 1, 0, 0, 0, 0, 1,   0, 0, 0, 0, 0, 0, 0, 0,
+  1, 0, 1, 0, 0, 0, 0, 1,   0, 0, 0, 0, 1, 0, 0, 0,
   0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0,
   0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0,
   0, 0, 0, 0, 0, 0, 0, 0,   0, 0, 0, 0, 0, 0, 0, 0,


I understand a number of open tickets exist related to csv-behaviour, so the 
above might be combined with those (the particular problem didn't seem to be 
mentioned earlier, though).





Frank,

Your change assumes the separator is always a comma. SQLite does not 
make that assumption. It uses the following code to check the array and 
the currently defined separator string before quoting a field.

      if( needCsvQuote[((unsigned char*)z)[i]]
         || (z[i]==p->separator[0] &&
             (nSep==1 || memcmp(z, p->separator, nSep)==0)) ){

The usual case for CSV files is that the separator is a single comma, 
and this code will quote such field correctly.

Dennis Cote



-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------