Re: [sqlite] Import feature requests

[Walter Dnes]

On Sun, Dec 13, 2009 at 10:31:20PM -0800, Roger Binns wrote

> Hint:  If you feel the need to get aggressive and abusive when
> posting then you probably missed something!  If SQLite was useless,
> someone else would have noticed by now.

  I apologize for coming across that way; I din't intend to.  Let's just
say I'm rather frustrated with some defaults.  If I felt SQLite was
useless I wouldn't be using it.  I read about the "loose typing" during
import, in the docs.  It was only via "the hard way" that I found out
just *HOW* loose.

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

Re: [sqlite] Import feature requests

[Dan Bishop]

Simon Slavin wrote:
> On 14 Dec 2009, at 5:13am, Walter Dnes wrote:
>
>   
>>  The following might be options (compile time, config file, set manually;
>> I don't care), but they should be available...
>> 
>
> It might be worth writing a separate sqlite3 import facility which just reads 
> a .csv into a table.  It could have some command-line switches (options ?) or 
> the first time it comes across an ambiguous value it could stop and ask the 
> user what the user wants.
>   
http://www.mail-archive.com/sqlite-users@sqlite.org/msg46507.html

Feel free to modify the code as needed.

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

Re: [sqlite] Import feature requests

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Simon Slavin wrote:
> It might be worth writing a separate sqlite3 import facility which just reads 
> a .csv into a table.

I betcha did not know there are at least 4 different implementations of CSV
as a virtual table (2 in the wiki, one in the SQLite source, and one by our
prolific extension writer friend in Russia!)

They all have problems.  They also all work correctly on the data set the
authors wanted them to.  There are a huge number of issues with trying to do
this that will work in all cases for everyone.  (Encodings, locales,
quoting, delimiter merging, column affinity, value affinity etc).

Beyond simple needs the importer really needs to write some code.  Sometimes
they can do it with SQL (eg converting strings to null) but in other cases
it requires real code.  Every popular scripting language out there has
SQLite bindings so it is not that hard and it will at least ensure the
resulting data is as expected by the author.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAksl4qIACgkQmOOfHg372QSlLQCgppsDLOx2Zv2bVufHvycLLrci
NL8AmwSrtn13Iokg3G22sNyiBIMyrgiw
=TtuA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Import feature requests

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Walter Dnes wrote:
> 1) import with strict typing.  If I create a table with 3 numeric (real.
> integer, whatever) fields, then a CSV file containing...
> 
>2.345,  42,  27.7
> 
> should import as 3 numbers, not as 3 character strings

What makes you think it doesn't?

My test file is one line:  2.345,42,27.7
(Note no spaces etc)

sqlite> .mode csv
sqlite> create table foo(a real, b int, c double);
sqlite> .import t.csv foo
sqlite> select typeof(a), typeof(b), typeof(c) from foo;
real,integer,real

> 2) import adjacent commas in a CSV file as NULL, not as a zero-length
> string.  

null and zero length strings have *very* different semantics.

>a) if it can't read my mind, I need to be able to tell it what I want
> 
>b) why would I want a "zero-length string" to behave any differently
>   from NULL?

If you have to ask the question then you really don't understand the issue!

>c) why on earth would I want a "zero-length string" in an *INTEGER*
>   or *REAL* field???  That is a totally insane default.

It isn't a default and SQLite uses manifest typing.  The column types are
hints, *not* requirements.  You may not like this but IMHO it is by far one
of the best features.

Going back to point (a), this is what you do.

- - Import into a temporary table

- - Copy the data into your permanent table modifying it as needed:

INSERT INTO permtable SELECT a,b,c from temptable

If you want to force a to be real then replace it with cast(a as REAL).  If
you want to turn zero length strings into nulls then replace it with:

  CASE a WHEN '' THEN null ELSE a END

A longer example:

INSERT INTO permtable SELECT
   CASE a WHEN trim(a)='' THEN null ELSE cast(a as REAL) END,
   CASE b WHEN trim(b)='' THEN null ELSE cast(b as INTEGER) END,
   CASE c WHEN trim(c)='' THEN null ELSE cast(c as DOUBLE) END
 FROM temptable;

This turns any amount of whitespace into null.  The cast function is also
more lenient - for example it will convert a number surrounded by white
space into a number - cast('   3.4' as real) - whereas column affinity
rules will not as doing so loses information (the space padding).  (Note
that cast won't even error on invalid input - cast('  3.4 hello' as real) -
returns the number 3.4.

Hint:  If you feel the need to get aggressive and abusive when posting then
you probably missed something!  If SQLite was useless, someone else would
have noticed by now.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAksl27gACgkQmOOfHg372QQl/QCfagur/5lU0pLbBjRpKe+jw1Wy
f+UAoKvODl2ki9yzSwjuhYu+4sDHIIjr
=JPnN
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Import feature requests

[Simon Slavin]

On 14 Dec 2009, at 5:13am, Walter Dnes wrote:

>  The following might be options (compile time, config file, set manually;
> I don't care), but they should be available...

It might be worth writing a separate sqlite3 import facility which just reads a 
.csv into a table.  It could have some command-line switches (options ?) or the 
first time it comes across an ambiguous value it could stop and ask the user 
what the user wants.

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

[sqlite] Import feature requests

[Walter Dnes]

  The following might be options (compile time, config file, set manually;
I don't care), but they should be available...

1) import with strict typing.  If I create a table with 3 numeric (real.
integer, whatever) fields, then a CSV file containing...

   2.345,  42,  27.7

should import as 3 numbers, not as 3 character strings

2) import adjacent commas in a CSV file as NULL, not as a zero-length
string.  As for the argument that SQLite can't read my mind...

   a) if it can't read my mind, I need to be able to tell it what I want

   b) why would I want a "zero-length string" to behave any differently
  from NULL?

   c) why on earth would I want a "zero-length string" in an *INTEGER*
  or *REAL* field???  That is a totally insane default.

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