Unable to import CSV file correctly when using ext/misc/csv.c as the csv module.

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Unable to import CSV file correctly when using ext/misc/csv.c as the csv module.

hi jack
The SQLite source code version I’m using is the latest public release version 3.26.0.

--------------------------------------------------------------------------------------------------------------------------------------

Reproduce steps:

1. Prepare a file data.csv. The content is
a,b,c,d
1,2,3,4
1,2,3,4
1,2,3,4

2. Run this query to import csv data. Only two parameters has been specified: filename and header.
CREATE VIRTUAL TABLE temp.t1 USING csv(filename='data.csv', header);
SELECT * FROM t1;

3.
Expected result in Step 2 is
a,b,c,d
1,2,3,4
1,2,3,4
1,2,3,4

Actual result in Step 2 is
a,b,c,d
There’s only header be imported, table content is lost.

--------------------------------------------------------------------------------------------------------------------------------------

I think this issue is caused by this line of code in the file ext/misc/csv.c
(https://www.sqlite.org/cgi/src/artifact/88333dc9f7dcf6a8),

pNew->iStart = ftell(sRdr.in);

which it might should be

pNew->iStart = ftell(sRdr.in) - sRdr.nIn + sRdr.iIn;

And I may wrong about the fixing, but this bug is exists in real.
--------------------------------------------------------------------------------------------------------------------------------------

Best regards.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Unable to import CSV file correctly when using ext/misc/csv.c as the csv module.

Richard Hipp-3
Fixed at https://www.sqlite.org/src/info/7acaed08f946633f

On 12/25/18, hi jack <[hidden email]> wrote:

> The SQLite source code version I’m using is the latest public release
> version 3.26.0.
>
> --------------------------------------------------------------------------------------------------------------------------------------
>
> Reproduce steps:
>
> 1. Prepare a file data.csv. The content is
> a,b,c,d
> 1,2,3,4
> 1,2,3,4
> 1,2,3,4
>
> 2. Run this query to import csv data. Only two parameters has been
> specified: filename and header.
> CREATE VIRTUAL TABLE temp.t1 USING csv(filename='data.csv', header);
> SELECT * FROM t1;
>
> 3.
> Expected result in Step 2 is
> a,b,c,d
> 1,2,3,4
> 1,2,3,4
> 1,2,3,4
>
> Actual result in Step 2 is
> a,b,c,d
> There’s only header be imported, table content is lost.
>
> --------------------------------------------------------------------------------------------------------------------------------------
>
> I think this issue is caused by this line of code in the file
> ext/misc/csv.c
> (https://www.sqlite.org/cgi/src/artifact/88333dc9f7dcf6a8),
>
> pNew->iStart = ftell(sRdr.in);
>
> which it might should be
>
> pNew->iStart = ftell(sRdr.in) - sRdr.nIn + sRdr.iIn;
>
> And I may wrong about the fixing, but this bug is exists in real.
> --------------------------------------------------------------------------------------------------------------------------------------
>
> Best regards.
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Unable to import CSV file correctly when using ext/misc/csv.c as the csv module.

D Burgess
There is also a problem with the documentation on csv.c

Example from the source comments:

    CREATE VIRTUAL TABLE temp.csv2 USING csv(
       filename = "../http.log",
       schema = "CREATE TABLE x(date,ipaddr,url,referrer,userAgent)"
   );

It should be noted that the schema= parameter allows one to specify names,
NOT types. Everything is set to a datatype of TEXT. Any datatype
declarations are ignored.

so with
"CREATE VIRTUAL TABLE temp.csv2 USING csv(
       filename = "../http.log",
      schema = "CREATE TABLE x(anbr INTEGER ,ipaddr,url,referrer,userAgent)"
    );

typeof (anbr) is "text"
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users