Regarding: What precisely are the
"improvements" in handling of CSV inputs?
Gabor, I don't know about "precisely" -- I'll let others on the list tell
me where I'm off, but here's my take:
A lot of strange things call themselves csv, but the change attempts to
make the sqlite3 utility's CSV inputs perform a bit more closely to RFC4180.
http://tools.ietf.org/html/rfc4180
http://en.wikipedia.org/wiki/Comma-separated_values#Toward_standardization
In particular, during CSV mode import:
-- Allow any field to be surrounded by double quote characters without
those characters being considered part of the field data.
-- Allow fields to contain embedded commas (or other separators) when the
field is surrounded by double quote characters.
-- Allow fields to span multiple lines if they are surrounded by double
quote characters.
-- Allow the double quote character to be escaped by having two adjacent
double quote characters. (But note that a field consisting solely of two
double quote characters still represents an empty string field.)
-- On output in CSV mode, surround text fields with double quotes when
needed.
See check-in [93aa17d866] http://www.sqlite.org/src/info/93aa17d866
(By the way, I believe the sqlite3 command line utility (CLI) was intended
to be more of a debug tool than a production component -- but it surely is
useful!)
For an example of CSV import, if I have file MyStuff.csv whose data is
shown below between the barred lines below (words in square brackets [] are
just my comments and were not present in the import file):
==============================
1,cat
2,"rat" [quotes are optional unless separator(s)
embedded]
3 ,"grey fox" [extra whitespace will be handled differently
when affinity is numeric]
4, spacedog [There's a space before and after spacedog --
trust me]
5,o'possum
6,"big, bad, wolf"
7,"two-lined [Fields can span lines]
zebra"
8, [Second field empty. (Maybe I forgot to type
"Missing lynx")]
9,imperial ("laughing") loon
==============================
Now I create a test database.
C:\util>sqlite3 test.db
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> /* Define a simple table t, comprised of an integer column and a
text column */
sqlite> Create table t ( id integer, animal);
sqlite> /* import the data above using csv mode */
sqlite> .mode csv
sqlite> .import MyStuff.csv t
sqlite> /* Show the table in CSV mode
sqlite> select * from t;
1,cat
2,rat
3,"grey fox"
4," spacedog "
5,"o'possum"
6,"big, bad, wolf"
7,"two-lined
zebra"
8,""
9,"imperial (""laughing"") loon"
sqlite>
sqlite>
sqlite>
sqlite> /* Try changing the separator and show it again in LIST mode */
sqlite> .separator |
sqlite> .mode list
sqlite> select * from t;
1|cat
2|rat
3|grey fox
4| spacedog
5|o'possum
6|big, bad, wolf
7|two-lined
zebra
8|
9|imperial ("laughing") loon
sqlite>
Does this answer your questions?
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
