Re: loadfile with quotation marks -> csv-file
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 ? Regards, try_and_err
Hmm, i'am really the first one with this problem ? 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 for Informix. 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 ? > > Regards, > try_and_err
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? If that failed, I'd look at DBD::CSV and related Perl stuff. > 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 for Informix. 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 ? >> >>Regards, >>try_and_err