SQLite Forum

csv extension bug ?
Login

csv extension bug ?

(1) By david.marcombes on 2022-01-13 10:35:04 [source]

Consider the below snippet, pushed via the C api

const char * sql =
"CREATE VIRTUAL TABLE csv_tab USING "
"csv(header = True, filename = 'c:/tab.csv', "
"schema = 'CREATE TABLE csv_schema ("
"some_date DATE NOT NULL,"
"some_str1 TEXT COLLATE BINARY DEFAULT '',"
"some_str2 TEXT COLLATE BINARY DEFAULT '',"
"some_flt DOUBLE'"
"); ');";

as csv does unquoting of its parameters (csv_unquote)

the schema used ends up as (line reformatted to illustrate problem)

CREATE VIRTUAL TABLE csv_tab USING 
csv(header = True, filename = 'c:/tab.csv', 
schema = 'CREATE TABLE csv_schema (
some_date DATE NOT NULL,
some_str1 TEXT COLLATE BINARY DEFAULT ',some_str2 TEXT COLLATE BINARY DEFAULT ',
some_flt DOUBLE
); 

And we end up with one less column in the schema and on with incorrect default.

Can be worked around once problem understood but I think that in general only the outer quotes should be processed, not the inner ones.

Many thanks in advance for your feedback

(2.1) By Ryan Smith (cuz) on 2022-01-13 11:27:32 edited from 2.0 in reply to 1 [link] [source]

I'm not 100% sure what is being done here, but a cursory glance makes it clear your escaping is not correct or at least, not consistent.

Try this and see if it works:

const char * sql =
"CREATE VIRTUAL TABLE csv_tab USING "
"csv(header = True, filename = 'c:/tab.csv', "
"schema = 'CREATE TABLE csv_schema ("
"some_date DATE NOT NULL,"
"some_str1 TEXT COLLATE BINARY DEFAULT '''',"
"some_str2 TEXT COLLATE BINARY DEFAULT '''',"
"some_flt DOUBLE"
"); ');";

(3) By david.marcombes on 2022-01-13 12:09:04 in reply to 2.1 [link] [source]

My work around is indeed to "re quote" the inner schema passed to the csv module to compensate for the unquoting of the parameters.

Thanks