Fw: loadfile with quotation marks -> csv-file (SQLCMD and DB-Access and

Posted in 2005

Jonathan Leffler — Tue, 22 Mar 2005 16:36:13 -0800 — source: Usenet: comp.databases.informix

This is a multipart message in MIME format. --=_alternative 0003511C88256FCD_= Content-Type: text/plain; charset="US-ASCII" Jonathan Leffler <jleffler@earthlink.net> wrote on 03/21/2005 09:38:43 PM: > try_and_err wrote: > > Hmm, i'am really the first one with this problem ? > > Maybe, but probably not. I would have tried SQLCMD first (mainly > because I wrote it), and I'd worry about whether it handled the > "ABC,DEF" field correctly (and the other unquoted non-numeric strings). > I need to investigate, and fix if necessary. How would you embed a > double quote inside a field enclosed in double quotes? It failed - the comma inside the quote is mishandled. I'll meditate on whether it is worth fixin > If that failed, I'd look at DBD::CSV and related Perl stuff. Also look (in Google, IIUG) for csv2unl - a Perl script that Andrew Hamm and I assembled around October 2002. If that is not readily discoverable, I'll submit it to the IIUG. It converts CSV data to Informix UNLOAD format, ready for use by - well, DB-Access, SQLCMD, DBLOAD, ... It uses the Text::CSV module for Perl - you'd need to obtain that (and any of its dependencies - I didn't notice any, but I have so many modules installed it could be that I wouldn't notice). > > ok, maybe this is useful for others: > > When Informix has not a solution have a look on the competition: > > I found a nice website with a solution for postgresql: > > http://www.onlamp.com/onlamp/2004/12/09/examples/create_input_sql.pl > > You have only to adapt your table schema, works also perfectly forInformix. > > Looks as though it might be useful. > > > Regards, > > try_and_err > > > > try_and_err@web.de (try_and_err) wrote in message news:<fbcbd702. > 0503181213.50f422e1@posting.google.com>... > > > >>ok, maybe my first explanation was to simple (or false): > >>We have a standard CSV file (comma separated) from an extern firma to > >>insert in an Informix table, like this: > >>1,ABC,"ABC,DEF",DEF,2,,, > >>2,BCD,EFG,"BCD,EFG",2,,, > >> > >>There were three problems (for Informix/dbaccess): > >>- double quotes > >>- comma in double quotes identified as delimiter > >>- last column without finally delimiter (table has 8 columns) > >> > >>ok, i can write an (awk) script to fix these problems, but i am > >>surprised that is it really not possible to load an normal and > >>standard CSV file in Informix ? > >>sqlreload doesn't work: > >>env | grep DB > >>DBQUOTE=" > >>DBDELIMITER=, > >>sqlreload -d db1 -i test1.csv -t test1 -v -x > >>SQL -846: Number of values in load file is not equal to number of > >>columns. > >>ISAM -746: Too many values in record > >>It seems, that the comma in double quotes is already identified as > >>delimiter... > >>I have tried with and without an additionally delimiter (comma). > >>Further suggestions ? -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Information Management Division 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" --=_alternative 0003511C88256FCD_= Content-Type: text/html; charset="US-ASCII" <tt>Jonathan Leffler <jleffler@earthlink.net> wrote on 03/21/2005 09:38:43 PM: > try_and_err wrote: > > Hmm, i'am really the first one with this problem ? > > Maybe, but probably not.  I would have tried SQLCMD first (mainly > because I wrote it), and I'd worry about whether it handled the > "ABC,DEF" field correctly (and the other unquoted non-numeric strings). >   I need to investigate, and fix if necessary.  How would you embed a > double quote inside a field enclosed in double quotes? </tt> <tt>It failed - the comma inside the quote is mishandled.  I'll meditate on whether it is worth fixin</tt> <tt> > If that failed, I'd look at DBD::CSV and related Perl stuff. </tt> <tt>Also look (in Google, IIUG) for csv2unl - a Perl script that Andrew Hamm and I assembled around October 2002.  If that is not readily discoverable, I'll submit it to the IIUG.  It converts CSV data to Informix UNLOAD format, ready for use by - well, DB-Access, SQLCMD, DBLOAD, ...  It uses the Text::CSV module for Perl - you'd need to obtain that (and any of its dependencies - I didn't notice any, but I have so many modules installed it could be that I wouldn't notice).</tt> <tt> > > ok, maybe this is useful for others: > > When Informix has not a solution have a look on the competition: > > I found a nice website with a solution for postgresql: > > http://www.onlamp.com/onlamp/2004/12/09/examples/create_input_sql.pl ; > > You have only to adapt your table schema, works also perfectly forInformix. > > Looks as though it might be useful. > > > Regards, > > try_and_err > > > > try_and_err@web.de (try_and_err) wrote in message news:<fbcbd702. > 0503181213.50f422e1@posting.google.com>... > > > >>ok, maybe my first explanation was to simple (or false): > >>We have a standard CSV file (comma separated) from an extern firma to > >>insert in an Informix table, like this: > >>1,ABC,"ABC,DEF",DEF,2,,, > >>2,BCD,EFG,"BCD,EFG",2,,, > >> > >>There were three problems (for Informix/dbaccess): > >>- double quotes > >>- comma in double quotes identified as delimiter > >>- last column without finally delimiter (table has 8 columns) > >> > >>ok, i can write an (awk) script to fix these problems, but i am > >>surprised that is it really not possible to load an normal and > >>standard CSV file in Informix ? > >>sqlreload doesn't work: > >>env | grep DB > >>DBQUOTE=" > >>DBDELIMITER=, > >>sqlreload -d db1 -i test1.csv -t test1 -v -x > >>SQL -846: Number of values in load file is not equal to number of > >>columns. > >>ISAM -746: Too many values in record > >>It seems, that the comma in double quotes is already identified as > >>delimiter... > >>I have tried with and without an additionally delimiter (comma). > >>Further suggestions ? </tt> -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Information Management Division 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921   Tie-Line: 630-6921      "I don't suffer from insanity; I enjoy every minute of it!" </font

Related threads

Need your help on this strange loading problem ... Thanks