pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to import CSV file?
5+ messages / 4 participants
[nested] [flat]
* How to import CSV file?
@ 2007-08-28 15:38 Chris Hoover <revoohc@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Chris Hoover @ 2007-08-28 15:38 UTC (permalink / raw)
To: pgsql-admin
I need some help importing a csv file. I have been given a csv file full of
data that is delimited by a comma, and the strings are delimited by a single
quote. How do you write the copy statement to use a single quote
delimiter? I have tried several things, but so far, no joy.
Thanks for any help,
Chris
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to import CSV file?
@ 2007-08-28 17:03 Medi Montaseri <montaseri@gmail.com>
parent: Chris Hoover <revoohc@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Medi Montaseri @ 2007-08-28 17:03 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
This might not be the quickest way, but it is a skill you'll be happy to
have gained.
Perl has a class (or module) called CSV.pm, you feed it a file, and it does
the rest (parsing it) and gives an array for each row. You can then alter
them (however you wish) and write them to a file suitable for pg copy, or
just use DBI to insert it into the PG. Which is basically an ETL (Extract,
Transform, Load). I know insert is slower, but the point was to show a
general way that will always for.
Cheers
Medi Montaseri
On 8/28/07, Chris Hoover <revoohc@gmail.com> wrote:
>
> I need some help importing a csv file. I have been given a csv file full
> of data that is delimited by a comma, and the strings are delimited by a
> single quote. How do you write the copy statement to use a single quote
> delimiter? I have tried several things, but so far, no joy.
>
> Thanks for any help,
>
> Chris
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to import CSV file?
@ 2007-08-28 18:02 Chris Hoover <revoohc@gmail.com>
parent: Medi Montaseri <montaseri@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Chris Hoover @ 2007-08-28 18:02 UTC (permalink / raw)
To: Medi Montaseri <montaseri@gmail.com>; +Cc: pgsql-admin
Why go through all of this when COPY can handle this for you? All I need to
know is the syntax to tell copy that the strings are quoted by a single
quote.
Thanks anyway.
On 8/28/07, Medi Montaseri <montaseri@gmail.com> wrote:
>
> This might not be the quickest way, but it is a skill you'll be happy to
> have gained.
>
> Perl has a class (or module) called CSV.pm, you feed it a file, and it
> does the rest (parsing it) and gives an array for each row. You can then
> alter them (however you wish) and write them to a file suitable for pg copy,
> or just use DBI to insert it into the PG. Which is basically an ETL
> (Extract, Transform, Load). I know insert is slower, but the point was to
> show a general way that will always for.
>
> Cheers
> Medi Montaseri
>
> On 8/28/07, Chris Hoover <revoohc@gmail.com> wrote:
> >
> > I need some help importing a csv file. I have been given a csv file
> > full of data that is delimited by a comma, and the strings are delimited by
> > a single quote. How do you write the copy statement to use a single quote
> > delimiter? I have tried several things, but so far, no joy.
> >
> > Thanks for any help,
> >
> > Chris
> >
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to import CSV file?
@ 2007-08-28 18:11 Jeff Frost <jeff@frostconsultingllc.com>
parent: Chris Hoover <revoohc@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Jeff Frost @ 2007-08-28 18:11 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: Medi Montaseri <montaseri@gmail.com>; pgsql-admin
On Tue, 28 Aug 2007, Chris Hoover wrote:
> Why go through all of this when COPY can handle this for you? All I need to
> know is the syntax to tell copy that the strings are quoted by a single
> quote.
Check out the docs here:
http://www.postgresql.org/docs/8.1/interactive/sql-copy.html
Probably you want to try something like:
COPY foo FROM '/tmp/foo' WITH CSV QUOTE AS '\'';
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to import CSV file?
@ 2007-08-28 19:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2007-08-28 19:34 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Chris Hoover <revoohc@gmail.com>; Medi Montaseri <montaseri@gmail.com>; pgsql-admin
Jeff Frost <jeff@frostconsultingllc.com> writes:
> On Tue, 28 Aug 2007, Chris Hoover wrote:
>> Why go through all of this when COPY can handle this for you? All I need to
>> know is the syntax to tell copy that the strings are quoted by a single
>> quote.
> Probably you want to try something like:
> COPY foo FROM '/tmp/foo' WITH CSV QUOTE AS '\'';
Actually I'd recommend WITH CSV QUOTE AS '''' ... the backslash syntax
is nonstandard and will bite you eventually. Doubled quote works in
every version of PG.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-08-28 19:34 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-28 15:38 How to import CSV file? Chris Hoover <revoohc@gmail.com>
2007-08-28 17:03 ` Medi Montaseri <montaseri@gmail.com>
2007-08-28 18:02 ` Chris Hoover <revoohc@gmail.com>
2007-08-28 18:11 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-08-28 19:34 ` Tom Lane <tgl@sss.pgh.pa.us>
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