pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How 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>
  2007-08-28 17:03 ` Re: How to import CSV file? Medi Montaseri <montaseri@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 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   ` Re: How to import CSV file? 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 15:38 How to import CSV file? Chris Hoover <revoohc@gmail.com>
  2007-08-28 17:03 ` Re: How to import CSV file? Medi Montaseri <montaseri@gmail.com>
@ 2007-08-28 18:02   ` Chris Hoover <revoohc@gmail.com>
  2007-08-28 18:11     ` Re: How to import CSV file? Jeff Frost <jeff@frostconsultingllc.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 15:38 How to import CSV file? Chris Hoover <revoohc@gmail.com>
  2007-08-28 17:03 ` Re: How to import CSV file? Medi Montaseri <montaseri@gmail.com>
  2007-08-28 18:02   ` Re: How to import CSV file? Chris Hoover <revoohc@gmail.com>
@ 2007-08-28 18:11     ` Jeff Frost <jeff@frostconsultingllc.com>
  2007-08-28 19:34       ` Re: How to import CSV file? Tom Lane <tgl@sss.pgh.pa.us>
  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 15:38 How to import CSV file? Chris Hoover <revoohc@gmail.com>
  2007-08-28 17:03 ` Re: How to import CSV file? Medi Montaseri <montaseri@gmail.com>
  2007-08-28 18:02   ` Re: How to import CSV file? Chris Hoover <revoohc@gmail.com>
  2007-08-28 18:11     ` Re: How to import CSV file? Jeff Frost <jeff@frostconsultingllc.com>
@ 2007-08-28 19:34       ` Tom Lane <tgl@sss.pgh.pa.us>
  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