Donald, I have a question about #9 of your test cases. According to RFC 4180, #9 is an invalid record. The RFC states "If fields are not enclosed with double quotes, then
double quotes may not appear inside the fields." However, I imported your test cases into Open Office, Excel, and Numbers and the resulting spreadsheets all left the quotes in place in that record. To confuse matters even more, if I then exported those spreadsheets as csv files, they all enlosed the string in quotes and escaped the original quotes. So I guess there's precedent for either case depending on how closely you want to stick to the RFC. Pete lcSQL Software <http://www.lcsql.com> On Mon, May 7, 2012 at 9:00 AM, <sqlite-users-requ...@sqlite.org> wrote: > Message: 17 > Date: Sun, 6 May 2012 20:00:51 -0400 > From: Donald Griggs <dfgri...@gmail.com> > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Subject: Re: [sqlite] Details on New Features > Message-ID: > <cadlx_mn0cgpukdbvvtiexdwkzt2ajb3hhrpui2s5enw2kgq...@mail.gmail.com > > > Content-Type: text/plain; charset=ISO-8859-1 > > 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
