Hi,
here comes a bug report with fix proposal.
If a CSV file contains a row with missing column, the previous cell will not be
imported.
Tested version: sqlite-shell-win32-x86-3080802.zip,
sqlite-autoconf-3080802.tar.gz
Steps to reproduce:
- A file named data.csv with following content:
a,b,c
d,THIS_ENTRY_WILL_BE_MISSING
f,g,h
- Open shell and execute following commands:
.open db.sqlite
CREATE TABLE data (col1, col2, col3);
.mode csv
.import data.csv data
SELECT * FROM data;
Bug source:
After a missing column was detected (in shell.c line 2890) the bound text
paramter (line 2889) will be overwritten with NULL in line 2895.
Bug fix:
In file shell.c line 2894 change
i++;
to
i+=2;
Corrected source will lock like:
...
do{
int startLine = sCtx.nLine;
for(i=0; i<nCol; i++){
char *z = xRead(&sCtx);
/*
** Did we reach end-of-file before finding any columns?
** If so, stop instead of NULL filling the remaining columns.
*/
if( z==0 && i==0 ) break;
/*
** Did we reach end-of-file OR end-of-line before finding any
** columns in ASCII mode? If so, stop instead of NULL filling
** the remaining columns.
*/
if( p->mode==MODE_Ascii && (z==0 || z[0]==0) && i==0 ) break;
sqlite3_bind_text(pStmt, i+1, z, -1, SQLITE_TRANSIENT);
if( i<nCol-1 && sCtx.cTerm!=sCtx.cColSep ){
fprintf(stderr, "%s:%d: expected %d columns but found %d - "
"filling the rest with NULL\n",
sCtx.zFile, startLine, nCol, i+1);
i+=2; // BUG FIX HERE <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
while( i<=nCol ){ sqlite3_bind_null(pStmt, i); i++; }
}
}
if( sCtx.cTerm==sCtx.cColSep ){
do{
xRead(&sCtx);
i++;
}while( sCtx.cTerm==sCtx.cColSep );
fprintf(stderr, "%s:%d: expected %d columns but found %d - "
"extras ignored\n",
sCtx.zFile, startLine, nCol, i);
}
if( i>=nCol ){
sqlite3_step(pStmt);
rc = sqlite3_reset(pStmt);
if( rc!=SQLITE_OK ){
fprintf(stderr, "%s:%d: INSERT failed: %s\n", sCtx.zFile, startLine,
sqlite3_errmsg(db));
}
}
}while( sCtx.cTerm!=EOF );
...
Udo
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
