Re: [sqlite] v3.8 .import misbehaves

[Simon Slavin]

On 28 Aug 2013, at 2:32pm, Simon Davies  wrote:

> On 28 August 2013 14:16,   wrote:
>> OK, now copy the data line several times, and you'll see there are errors
>> for several lines, unrelated to the final CRLF (which I removed this next
>> sample). [snip]
> 
> Agreed.
> The CR character seems not to be handled properly. Change CRLF to just
> LF, and the import succeeds. Change CRLF to CR and same failure as
> above.

Nice diagnosis.  Well done.

Technically, all CSV lines except the last should be delimited by CRLF, and the 
last line does not need (but may have) the CRLF.  RFC4180 2.1 and 2.2:

1.  Each record is located on a separate line, delimited by a line break (CRLF).
2.  The last record in the file may or may not have an ending line break.

In practise, most CSV importing software does something like

1. Convert all LF to CR.
2. Convert all CRCR to CR
3. Read data as lines delimited by CR
4. Ignore all lines which are entirely blank

so it doesn't matter.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] v3.8 .import misbehaves

[Richard Hipp]

http://www.sqlite.org/src/info/b5617e4fda


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] v3.8 .import misbehaves

[Simon Davies]

On 28 August 2013 14:16,   wrote:
> OK, now copy the data line several times, and you'll see there are errors
> for several lines, unrelated to the final CRLF (which I removed this next
> sample).
>
> -- data --
> "Year","Debt","GDP1","GDP2","RGDP","dRGDP","Infl","debtgdp"
> "1833","","49.3275923134","","118.3483703666","","",""
> "1833","","49.3275923134","","118.3483703666","","",""
> "1833","","49.3275923134","","118.3483703666","","",""
> "1833","","49.3275923134","","118.3483703666","","",""
> "1833","","49.3275923134","","118.3483703666","","",""
> "1833","","49.3275923134","","118.3483703666","","",""
> ---
>
> C:\temp>sqlite3.exe
> SQLite version 3.8.0 2013-08-26 04:50:08
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .sep ,
> sqlite> .import data tab
> data:3: unescaped " character
> data:4: unescaped " character
> data:5: unescaped " character
> data:6: unescaped " character
> data:7: unescaped " character
> data:2: expected 8 columns but found 43 - extras ignored
> sqlite>
>

Agreed.
The CR character seems not to be handled properly. Change CRLF to just
LF, and the import succeeds. Change CRLF to CR and same failure as
above.

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] v3.8 .import misbehaves

[tonyp]

It turns out that CRLF may have something to do with it.

I can get rid of the errors, either:

1. if I replace "" with nothing,
OR 
2. if I save the file as Linux style (LF only).


-Original Message- 
From: to...@acm.org 
Sent: Wednesday, August 28, 2013 4:16 PM 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] v3.8 .import misbehaves 

OK, now copy the data line several times, and you'll see there are errors 
for several lines, unrelated to the final CRLF (which I removed this next 
sample).


-- data --
"Year","Debt","GDP1","GDP2","RGDP","dRGDP","Infl","debtgdp"
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
---

C:\temp>sqlite3.exe
SQLite version 3.8.0 2013-08-26 04:50:08
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .sep ,
sqlite> .import data tab
data:3: unescaped " character
data:4: unescaped " character
data:5: unescaped " character
data:6: unescaped " character
data:7: unescaped " character
data:2: expected 8 columns but found 43 - extras ignored
sqlite>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] v3.8 .import misbehaves

[tonyp]

OK, now copy the data line several times, and you'll see there are errors 
for several lines, unrelated to the final CRLF (which I removed this next 
sample).


-- data --
"Year","Debt","GDP1","GDP2","RGDP","dRGDP","Infl","debtgdp"
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
"1833","","49.3275923134","","118.3483703666","","",""
-------

C:\temp>sqlite3.exe
SQLite version 3.8.0 2013-08-26 04:50:08
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .sep ,
sqlite> .import data tab
data:3: unescaped " character
data:4: unescaped " character
data:5: unescaped " character
data:6: unescaped " character
data:7: unescaped " character
data:2: expected 8 columns but found 43 - extras ignored
sqlite>

-Original Message- 
From: Simon Davies

Sent: Wednesday, August 28, 2013 4:06 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] v3.8 .import misbehaves

On 28 August 2013 13:51,  <to...@acm.org> wrote:

I did.  I just download the precompiled binaries for Windows, and this is
what I see (for that sample data file):

C:\temp>sqlite3.exe
SQLite version 3.8.0 2013-08-26 04:50:08
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .sep ,
sqlite> .import data tab
data:2: unterminated "-quoted field
sqlite>





I can confirm the behaviour with
C:\>xd -c data.txt
0: 22 59 65 61 72 22 2C 22  44 65 62 74 22 2C 22 47 | "Year","Debt","G
   10: 44 50 31 22 2C 22 47 44  50 32 22 2C 22 52 47 44 | DP1","GDP2","RGD
   20: 50 22 2C 22 64 52 47 44  50 22 2C 22 49 6E 66 6C | P","dRGDP","Infl
   30: 22 2C 22 64 65 62 74 67  64 70 22 0D 0A 22 31 38 | ","debtgdp".."18
   40: 33 33 22 2C 22 22 2C 22  34 39 2E 33 32 37 35 39 | 33","","49.32759
   50: 32 33 31 33 34 22 2C 22  22 2C 22 31 31 38 2E 33 | 23134","","118.3
   60: 34 38 33 37 30 33 36 36  36 22 2C 22 22 2C 22 22 | 483703666","",""
   70: 2C 22 22 0D 0A   | ,""..
C:> sqlite3
SQLite version 3.8.0 2013-08-26 04:50:08
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .sep ,
sqlite>
sqlite> .import data.txt tab
data.txt:2: unterminated "-quoted field

But change file to:

C:\Joinerysoft\Software\sqlite\3_8_0>xd -c data.txt
0: 22 59 65 61 72 22 2C 22  44 65 62 74 22 2C 22 47 | "Year","Debt","G
   10: 44 50 31 22 2C 22 47 44  50 32 22 2C 22 52 47 44 | DP1","GDP2","RGD
   20: 50 22 2C 22 64 52 47 44  50 22 2C 22 49 6E 66 6C | P","dRGDP","Infl
   30: 22 2C 22 64 65 62 74 67  64 70 22 0D 0A 22 31 38 | ","debtgdp".."18
   40: 33 33 22 2C 22 22 2C 22  34 39 2E 33 32 37 35 39 | 33","","49.32759
   50: 32 33 31 33 34 22 2C 22  22 2C 22 31 31 38 2E 33 | 23134","","118.3
   60: 34 38 33 37 30 33 36 36  36 22 2C 22 22 2C 22 22 | 483703666","",""
   70: 2C 22 22 | ,""

(remove trailing newline) and the import works with no errors.

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@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

Re: [sqlite] v3.8 .import misbehaves

[Simon Davies]

On 28 August 2013 13:51,   wrote:
> I did.  I just download the precompiled binaries for Windows, and this is
> what I see (for that sample data file):
>
> C:\temp>sqlite3.exe
> SQLite version 3.8.0 2013-08-26 04:50:08
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .sep ,
> sqlite> .import data tab
> data:2: unterminated "-quoted field
> sqlite>
>
>


I can confirm the behaviour with
C:\>xd -c data.txt
 0: 22 59 65 61 72 22 2C 22  44 65 62 74 22 2C 22 47 | "Year","Debt","G
10: 44 50 31 22 2C 22 47 44  50 32 22 2C 22 52 47 44 | DP1","GDP2","RGD
20: 50 22 2C 22 64 52 47 44  50 22 2C 22 49 6E 66 6C | P","dRGDP","Infl
30: 22 2C 22 64 65 62 74 67  64 70 22 0D 0A 22 31 38 | ","debtgdp".."18
40: 33 33 22 2C 22 22 2C 22  34 39 2E 33 32 37 35 39 | 33","","49.32759
50: 32 33 31 33 34 22 2C 22  22 2C 22 31 31 38 2E 33 | 23134","","118.3
60: 34 38 33 37 30 33 36 36  36 22 2C 22 22 2C 22 22 | 483703666","",""
70: 2C 22 22 0D 0A   | ,""..
C:> sqlite3
SQLite version 3.8.0 2013-08-26 04:50:08
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .sep ,
sqlite>
sqlite> .import data.txt tab
data.txt:2: unterminated "-quoted field

But change file to:

C:\Joinerysoft\Software\sqlite\3_8_0>xd -c data.txt
 0: 22 59 65 61 72 22 2C 22  44 65 62 74 22 2C 22 47 | "Year","Debt","G
10: 44 50 31 22 2C 22 47 44  50 32 22 2C 22 52 47 44 | DP1","GDP2","RGD
20: 50 22 2C 22 64 52 47 44  50 22 2C 22 49 6E 66 6C | P","dRGDP","Infl
30: 22 2C 22 64 65 62 74 67  64 70 22 0D 0A 22 31 38 | ","debtgdp".."18
40: 33 33 22 2C 22 22 2C 22  34 39 2E 33 32 37 35 39 | 33","","49.32759
50: 32 33 31 33 34 22 2C 22  22 2C 22 31 31 38 2E 33 | 23134","","118.3
60: 34 38 33 37 30 33 36 36  36 22 2C 22 22 2C 22 22 | 483703666","",""
70: 2C 22 22 | ,""

(remove trailing newline) and the import works with no errors.

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] v3.8 .import misbehaves

[tonyp]

I did.  I just download the precompiled binaries for Windows, and this is 
what I see (for that sample data file):


C:\temp>sqlite3.exe
SQLite version 3.8.0 2013-08-26 04:50:08
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .sep ,
sqlite> .import data tab
data:2: unterminated "-quoted field
sqlite>

-Original Message- 
From: Richard Hipp

Sent: Wednesday, August 28, 2013 3:47 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] v3.8 .import misbehaves

On Wed, Aug 28, 2013 at 8:25 AM, <to...@acm.org> wrote:


For example, here's a sample (header + one line of data) that fails -- a
lot more lines fail but I cut it down just to show the problem:

"Year","Debt","GDP1","GDP2","**RGDP","dRGDP","Infl","debtgdp"
"1833","","49.3275923134","","**118.3483703666","","",""

Then, doing
.sep ,
.import data tab

gives error(s).



Unable to reproduce the problem.  The example above works correctly for me
using SQLite 3.8.0 on Linux and on Windows8.

Please verify that you are using 3.8.0 (where this problem has been fixed)
and not SQLite version 3.7.17 or earlier.

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@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

Re: [sqlite] v3.8 .import misbehaves

[Simon Slavin]

On 28 Aug 2013, at 1:25pm, to...@acm.org wrote:

> Then, doing
> .sep ,
> .import data tab
> 
> gives error(s).

Can't reproduce your fault though I'm on a different version and platform.  
Please make sure you are using those specific quote characters and not 
directional quotes, and such things.  You might want to use hexdump to dump 
your file and make sure it doesn't have control characters in it.

> Another less important issue, I think it should put NULLs instead of blanks 
> for blank columns.

It would be correct to insert NULLs for this line

> "1833",,"49.3275923134",,"118.3483703666",,,

But this line explicitly states that there are zero-length strings:

> "1833","","49.3275923134","","118.3483703666","","",""

And the example I gave for NULLs arguably does not conform to CSV format.  
Handling it properly might do no harm, though.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] v3.8 .import misbehaves

[Richard Hipp]

On Wed, Aug 28, 2013 at 8:25 AM,  wrote:

> For example, here's a sample (header + one line of data) that fails -- a
> lot more lines fail but I cut it down just to show the problem:
>
> "Year","Debt","GDP1","GDP2","**RGDP","dRGDP","Infl","debtgdp"
> "1833","","49.3275923134","","**118.3483703666","","",""
>
> Then, doing
> .sep ,
> .import data tab
>
> gives error(s).
>

Unable to reproduce the problem.  The example above works correctly for me
using SQLite 3.8.0 on Linux and on Windows8.

Please verify that you are using 3.8.0 (where this problem has been fixed)
and not SQLite version 3.7.17 or earlier.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] v3.8 .import misbehaves

[tonyp]

For example, here's a sample (header + one line of data) that fails -- a lot 
more lines fail but I cut it down just to show the problem:


"Year","Debt","GDP1","GDP2","RGDP","dRGDP","Infl","debtgdp"
"1833","","49.3275923134","","118.3483703666","","",""

Then, doing
.sep ,
.import data tab

gives error(s).

Replacing double quotes with blanks lets it work.  (Running on Win7, if it 
matters.)


Another less important issue, I think it should put NULLs instead of blanks 
for blank columns.


-Original Message- 
From: Richard Hipp

Sent: Wednesday, August 28, 2013 2:58 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] v3.8 .import misbehaves

On Wed, Aug 28, 2013 at 7:24 AM, <to...@acm.org> wrote:


When trying to load a data file with ,"", sequences (for empty field),
there are quote escape related errors.
Manually converting ,"", to ,, allows the file to be loaded.  According to
RFC4180, the double quote is an escaped quote if found inside a string.
 The leading quote should not be considered an escaped quote.  The ,"",
pattern is very common for denoting empty field.



Unable to reproduce the problem.  CSV import works according to RFC4180
when I try it.

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@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

Re: [sqlite] v3.8 .import misbehaves

[Richard Hipp]

On Wed, Aug 28, 2013 at 7:24 AM,  wrote:

> When trying to load a data file with ,"", sequences (for empty field),
> there are quote escape related errors.
> Manually converting ,"", to ,, allows the file to be loaded.  According to
> RFC4180, the double quote is an escaped quote if found inside a string.
>  The leading quote should not be considered an escaped quote.  The ,"",
> pattern is very common for denoting empty field.
>

Unable to reproduce the problem.  CSV import works according to RFC4180
when I try it.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users