-----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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
