SEO

#1 (**permalink**) 04-10-2008, 08:11 AM

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

#2 (**permalink**) 04-10-2008, 08:11 AM

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
>

#3 (**permalink**) 04-10-2008, 08:11 AM

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
> >
>
>

#4 (**permalink**) 04-10-2008, 08:11 AM

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/i.../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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

#5 (**permalink**) 04-10-2008, 08:11 AM

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

How to import CSV file?