From: Jeff Eckermann <jeff_eckermann@yahoo.com>
To: azwa@nc.com.my
To: pgsql-sql@postgresql.org
Subject: Re: data loading
Date: Sat, 10 Jan 2004 10:28:38 -0800 (PST)
Message-ID: <20040110182838.44707.qmail@web20804.mail.yahoo.com> (raw)
In-Reply-To: <200401092004.48865.dev@archonet.com>
--- Richard Huxton <dev@archonet.com> wrote:
> On Friday 09 January 2004 02:13, azwa@nc.com.my
> wrote:
> > Hi,
> >
> >
> > i try to load data from flat file (comma
> delimiter format) into
> > temporary table . i use COPY command as below:
> >
> > dwnc=# copy biosadm.custdo_temp
> > dwnc-# from
> '/home/bios/customer_data/CustomerDO_new.CSV'
> > dwnc-# WITH DELIMITER ',' ;
> >
> > ERROR: copy: line 141, Extra data after last
> expected column
>
> Does line 141 (or nearby) have a comma somewhere in
> its data? That would fool
> the COPY into mis-counting the columns. See the COPY
> entry in the SQL Command
> Reference chapter of the manuals - you'll want to
> escape any commas with a
> backslash:
> \,
CSV deals with embedded commas by quoting the data
field that contains it. Applications that are aware
of this will know to ignore commas within quoted
strings. COPY is not smart enough to figure this out
(nor smart enough to strip off any quotes, which will
be imported as data). If you know for a fact that
your data contains no quotes, then you can get away
with the "with delimiters ','" trick. Otherwise, you
will need to preprocess your data through something
that will parse the CSV format, e.g. some Perl module.
Note that hand rolled solutions usually underestimate
the complexity of dealing with CSV data, and why
bother when others have already solved the problem for you.
__________________________________
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jeff_eckermann@yahoo.com, azwa@nc.com.my
Subject: Re: data loading
In-Reply-To: <20040110182838.44707.qmail@web20804.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox