Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.

Dismiss

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

113 views

Skip to first unread message

Jonathan Leffler

unread,

Mar 22, 2005, 7:36:13 PM3/22/05

This is a multipart message in MIME format.
--=_alternative 0003511C88256FCD_=
Content-Type: text/plain; charset="US-ASCII"

Jonathan Leffler <jlef...@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_a...@web.de (try_and_err) wrote in message news:<fbcbd702.
> 050318121...@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 (jlef...@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 <jlef...@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_a...@web.de (try_and_err) wrote in message news:<fbcbd702. 
> 050318121...@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 (jlef...@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!" 
<tt> 
</tt>
--=_alternative 0003511C88256FCD_=--
sending to informix-list

0 new messages