Hi Everyone,
I have patched my SQLite3.exe source (shell.c) to have an ".import"
function witch can read ".mod csv" exports :
- can read QUOTE + TEXT + CRLF + TEXT + QUOTE
- can read QUOTE + SEPARATOR + QUOTE
- can read QUOTE + TEXT + SEPARATOR + TEXT + QUOTE
- can read QUOTE + TEXT + 2QUOTES + TEXT + QUOTE
So, I can export my table like this :
.mod csv
.output table.csv
select * from table;
.output stdout
And import like this :
.mod csv
.import table.csv table
example of csv file :
1,"this is ok",
2,"this is
a new line",""
3,"look ! , the coma ..."," and the ""quotes"" !"
Patch source code of shell.c :
==============================
/* patch for shell.c version 3_6_23_1 */
// I modified the local_getline() function
// from line 41:
#if defined(HAVE_READLINE) && HAVE_READLINE==1
# include <readline/readline.h>
# include <readline/history.h>
#else
# define readline(p) local_getline(p,stdin,0)
# define add_history(X)
# define read_history(X)
# define write_history(X)
# define stifle_history(X)
#endif
// from line 368:
static char *one_input_line(const char *zPrior, FILE *in){
char *zPrompt;
char *zResult;
if( in!=0 ){
return local_getline(0, in, 0); /* not in csv mode */
}
// function local_getline(), from line 320 :
/* NB: bCsvMode = 1, if the function is used by the .import command */
static char *local_getline(char *zPrompt, FILE *in, const int bCsvMode){
char *zLine;
int nLine;
int n;
int eol;
int bEscaped; /* Escape CRLF when quoted */
if( zPrompt && *zPrompt ){
printf("%s",zPrompt);
fflush(stdout);
}
nLine = 100;
zLine = malloc( nLine );
if( zLine==0 ) return 0;
n = 0;
eol = 0;
bEscaped = 0;
while( !eol ){
if( n+100>nLine ){
nLine = nLine*2 + 100;
zLine = realloc(zLine, nLine);
if( zLine==0 ) return 0;
}
if( fgets(&zLine[n], nLine - n, in)==0 ){
if( n==0 ){
free(zLine);
return 0;
}
zLine[n] = 0;
eol = 1;
break;
}
while( zLine[n] ){
if( zLine[n]=='"' && bCsvMode ) bEscaped = 1 - bEscaped;
n++;
}
if( !bEscaped && n>0 && zLine[n-1]=='\n' ){
n--;
if( n>0 && zLine[n-1]=='\r' ) n--;
zLine[n] = 0;
eol = 1;
}
}
zLine = realloc( zLine, n+1 );
return zLine;
}
// and from line 1628 :
sqlite3_exec(p->db, "BEGIN", 0, 0, 0);
zCommit = "COMMIT";
while( (zLine = local_getline(0, in, 1))!=0 ){ /* csv mode */
char *z;
char *q;
int bEscaped; /* to escape quote + comma + quote */
int bFieldQuoted;
bEscaped = 0;
i = 0;
lineno++;
if( *zLine=='"' ){
azCol[0] = zLine + 1; /* ignore the first quote */
bFieldQuoted = 1;
}else{
azCol[0] = zLine;
bFieldQuoted = 0;
}
for(i=0, z=zLine, q=zLine; *z ; z++){
if( *z=='"' ) bEscaped = 1 - bEscaped;
if( bEscaped==0 && *z==p->separator[0] && strncmp(z, p->separator,
nSep)==0 ){
if( bFieldQuoted==1 && *q=='"') *q = 0; /* ignore the last quote */
*z = 0;
i++;
if( i<nCol ){
azCol[i] = &z[nSep];
if( *azCol[i]=='"' ){
azCol[i]++; /* ignore the first quote */
bFieldQuoted = 1;
}else{
bFieldQuoted = 0;
}
z += nSep-1;
}
}
q = z;
} /* end for */
if( bEscaped==0 && bFieldQuoted==1 && *q=='"' ) *q = 0; /* ignore the
last quote */
*z = 0;
if( i+1!=nCol ){
fprintf(stderr,
"Error: %s line %d: expected %d columns of data but found %d\n",
zFile, lineno, nCol, i+1);
zCommit = "ROLLBACK";
free(zLine);
rc = 1;
break; /* from while */
}
for(i=0; i<nCol; i++){
/* find and reduce double quotes */
for( bEscaped=0, z=azCol[i], q=azCol[i]; *z ; z++, q++){
if( *z=='"' ) z++;
*q = *z;
}
*q = 0;
sqlite3_bind_text(pStmt, i+1, azCol[i], -1, SQLITE_STATIC);
}
sqlite3_step(pStmt);
rc = sqlite3_reset(pStmt);
free(zLine);
// end
It works, I can import and export csv file with this litle patch (few
lines of code).
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
