While looking in csv1.test, I found a solution to the case when header row contains spaces: just wrap the column name with double quotes.
Index: ext/csv/csv.c =================================================================== --- ext/csv/csv.c +++ ext/csv/csv.c @@ -158,11 +158,11 @@ pCSV->zRow[n] = '\0'; bEol = -1; } } if( bShrink ){ - pCSV->zRow = realloc( pCSV->zRow, n+1 ); + pCSV->zRow = sqlite3_realloc( pCSV->zRow, n+1 ); pCSV->maxRow = n+1; } return bEol ? pCSV->zRow : 0; } @@ -608,11 +608,11 @@ *pzErr = sqlite3_mprintf("%s", aErrMsg[4]); sqlite3_free(zSql); csvRelease( pCSV ); return SQLITE_ERROR; } - zSql = sqlite3_mprintf("%s%s%s", zTmp, zCol, zTail); + zSql = sqlite3_mprintf("%s\"%s\"%s", zTmp, zCol, zTail); }else{ zSql = sqlite3_mprintf("%scol%d%s", zTmp, i+1, zTail); } sqlite3_free(zTmp); } Index: ext/csv/csv1.test =================================================================== --- ext/csv/csv1.test +++ ext/csv/csv1.test @@ -23,10 +23,11 @@ # # csv-1.*: Creating/destroying csv tables. # csv-2.*: Linear scans of csv data. # csv-3.*: Test renaming an csv table. # csv-4.*: CREATE errors +# csv-5.*: Dirty header and long line. # ifcapable !csv { finish_test return @@ -36,10 +37,12 @@ set test1csv [file join [file dirname [info script]] test1.csv] # This file is delimited by '|' and has quoted fields. set test2csv [file join [file dirname [info script]] test2.csv] # This file is delimited by '|'. It does NOT have quoted fields. set test3csv [file join [file dirname [info script]] test3.csv] +# This file contains a dirty header and one long line. +set test4csv [file join [file dirname [info script]] test4.csv] #---------------------------------------------------------------------------- # Test cases csv-1.* test CREATE and DROP table statements. # @@ -249,5 +252,14 @@ catchsql " CREATE VIRTUAL TABLE t1 USING csv('foo') " } {1 {Error opening CSV file: 'foo'}} do_test csv-4.1.3 { catchsql " CREATE VIRTUAL TABLE t1 USING csv(foo foo) " } {1 {Error opening CSV file: 'foo foo'}} + +#---------------------------------------------------------------------------- +# Test cases csv-5.* test file with dirty header and long line. +# + +do_test csv-5.1.1 { + execsql " CREATE VIRTUAL TABLE t1 USING csv('$test4csv') " + execsql " CREATE VIRTUAL TABLE t2 USING csv('$test4csv', ',', USE_HEADER_ROW) " +} {} ADDED ext/csv/test4.csv col 1,col.2,col-3,col!4,c...@5,col;6,col%7,col*8,col=9,col'10 123456789,123456789,123456789,123456789,123456789,123456789,123456789,123456789,123456789,123456789 I tried to handle double-quoted fields with embedded line breaks without success but I am not stuck yet. Regards. On Wed, Apr 21, 2010 at 8:22 PM, gwenn <gwenn.k...@gmail.com> wrote: > Thanks for this great extension. > It works smoothly with 500Mo files. > > And it's a workaround to some shortcomings of the '.import' command: > - no need to create a table before, > - no need to delete the header row before/after, > - no error if the number of columns is not homogeneous, > - ... > It's a nightmare to work with the CSV format but I have to. > > I made a quick and dirty fix to the USE_HEADER_ROW mode to replace > whitespaces, slashes or hyphens by underscores. > But I look for a better solution. Is there any way to make sure a string is > a valid column name? > > Regards > > On Mon Apr 19 17:32:13 GMT 2010, Shane Harrelson <shane at sqlite.org> > wrote: > > Thanks for the report. The extension is still very a much a > > work-in-progress and any feedback is greatly appreciated. > > > > -Shane > > > > > > > On Sun, Apr 18, 2010 at 12:51 PM, gwenn <gwenn.kahz at gmail.com> wrote: > > > Hello, > > > There is a little bug/typo in the csv extension when lines exceed 100 > > > characters: > > > *** glibc detected *** sqlite3: realloc(): invalid pointer: > > > 0x0000000000ad1a78 *** > > > ======= Backtrace: ========= > > > /lib/libc.so.6[0x7f6dab009d16] > > > /lib/libc.so.6(realloc+0x321)[0x7f6dab00fda1] > > > ./libSqliteCsv.so[0x7f6da9ef9dbf] > > > > > > A possible patch is: > > > --- /tmp/SQLite-d474195a997b9d94/ext/csv/csv.c 2009-11-05 > 05:14:30.000000000 > > > +0100 > > > +++ csv.c 2010-04-18 18:48:04.000000000 +0200 > > > @@ -160,7 +160,7 @@ > > > } > > > } > > > if( bShrink ){ > > > - pCSV->zRow = realloc( pCSV->zRow, n+1 ); > > > + pCSV->zRow = sqlite3_realloc( pCSV->zRow, n+1 ); > > > pCSV->maxRow = n+1; > > > } > > > return bEol ? pCSV->zRow : 0; > > > > > > Regards. > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users at sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users