Patch sqlite-3.6.21 to import csv with ” for string

Below patch against sqlite-3.6.21 should handle

  • “field1”,123,abc,…
  • abc,…,””,123,….
  • abc,…,””abc””,…..

.mode csv
.import file table

to skip header in the import
.header on

Index: shell.c
===================================================================
— shell.c (revision 539)
+++ shell.c (working copy)
@@ -2496,7 +2496,7 @@
sqlite3_stmt *pStmt = NULL; /* A statement */
int nCol; /* Number of columns in the table */
int nByte; /* Number of bytes in an SQL string */
– int i, j; /* Loop counters */
+ int i, j, k; /* Loop counters */
int nSep; /* Number of bytes in p->separator[] */
char *zSql; /* An SQL statement */
char *zLine; /* A single line of input from the file */
@@ -2567,8 +2567,32 @@
char *z;
i = 0;
lineno++;
– azCol[0] = zLine;
+ if (lineno == 1 && p->showHeader == 1)
+ continue;
+ if (p->mode == MODE_Csv && zLine[0] == ‘”‘) {
+ k = 1;
+ azCol[0] = zLine+1;
+ } else {
+ k = 0;
+ azCol[0] = zLine;
+ }
for(i=0, z=zLine; *z && *z!=’n’ && *z!=’r’; z++){
+ //skip to end of double quote
+ if (p->mode == MODE_Csv && k != 0) {
+ if (i != 0)
+ k = 0;
+ while (z[k]!='”‘ && z[k]!=’n’ && z[k]!=’r’)
+ k++;
+ while (z[k]=='”‘ && z[k]!=’n’ && z[k]!=’r’)
+ k++;
+ if (z[k]!='”‘)
+ k–;
+// if (k > 2)
+// fprintf(stderr, “skip to end of ” (%d:%d:%d) %sn”,lineno,i,k,azCol[i]);
+ z[k] = 0;
+ z += k;
+ k = 0;
+ }
if( *z==p->separator[0] && strncmp(z, p->separator, nSep)==0 ){
*z = 0;
i++;
@@ -2577,9 +2601,24 @@
z += nSep-1;
}
}
+ if (p->mode == MODE_Csv && *z == ‘”‘) {
+ k=0;
+ azCol[i] = z+1;
+ while(z[k]=='”‘)
+ k++;
+ if (k == 2)
+ k = 1;
+ z += k-1;
+// if (k > 2)
+// fprintf(stderr, “move to start of “: (%d:%d:%d) %sn”,lineno,i,k,azCol[i]);
+ }
} /* end for */
*z = 0;
if( i+1!=nCol ){
+ fprintf(stderr, “k = %dn”,k);
+ for (k=0;k<=i;k++) {
+ fprintf(stderr, “Column (%d): %sn”,k,azCol[k]);
+ }
fprintf(stderr,
“Error: %s line %d: expected %d columns of data but found %dn”,
zFile, lineno, nCol, i+1);

Leave a Reply

Your email address will not be published. Required fields are marked *