Обсуждение: CSV delim quoting differences PgCOPY, Excel etc...

Hello.

I confess knowing nothing about MS Excel, don't run Windows, never use
it.

Anyway, I am often enough having to load Pg databases using SQL COPY
from CSV output written by Excel, that I've had to write a script to
change the quoting behavior from Excel's putting double quotes around
a field having embedded delim chars, to Pg's backslash quoting.

I've got a hunch that one or both of the aforementioned softwares can
change their quoting behavior.  (IT just seems reasonable... famous
last words.)

Is it true that neither of Pg SQL \copy and/or Excel can adapt as I
wish?  What about psql \copy?  I fail to read in the docs for the Pg
products any support for this.

If Excel can adapt, at least one experienced user I've asked, doesn't
know about it.

Thanks.

--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant 305
                321-1144 (mobile http://www.JerrySievers.com/

On Tue, 2005-05-17 at 15:58 -0400, Jerry Sievers wrote:
> Hello.
>
> I confess knowing nothing about MS Excel, don't run Windows, never use
> it.
>
> Anyway, I am often enough having to load Pg databases using SQL COPY
> from CSV output written by Excel, that I've had to write a script to
> change the quoting behavior from Excel's putting double quotes around
> a field having embedded delim chars, to Pg's backslash quoting.
>
> I've got a hunch that one or both of the aforementioned softwares can
> change their quoting behavior.  (IT just seems reasonable... famous
> last words.)
>
> Is it true that neither of Pg SQL \copy and/or Excel can adapt as I
> wish?  What about psql \copy?  I fail to read in the docs for the Pg
> products any support for this.
>
> If Excel can adapt, at least one experienced user I've asked, doesn't
> know about it.
>
> Thanks.
>
As of version 8.0 PostgreSQL's COPY (or \copy) command includes a CSV
flag (in addition to the normal DELIMITER AS, etc) that will
automagically deal with the double quotes.

COPY tablename [ ( column [, ...] ) ]
    FROM { 'filename' | STDIN }
    [ [ WITH ]
          [ BINARY ]
          [ OIDS ]
          [ DELIMITER [ AS ] 'delimiter' ]
          [ NULL [ AS ] 'null string' ]
          [ CSV [ QUOTE [ AS ] 'quote' ]
                [ ESCAPE [ AS ] 'escape' ]
                [ FORCE NOT NULL column [, ...] ]

Sven

jerry@jerrysievers.com (Jerry Sievers) writes:

> Hello.
>
> Anyway, I am often enough having to load Pg databases using SQL COPY
> from CSV output written by Excel, that I've had to write a script to
> change the quoting behavior from Excel's putting double quotes around
> a field having embedded delim chars, to Pg's backslash quoting.
>
> I've got a hunch that one or both of the aforementioned softwares can
> change their quoting behavior.  (IT just seems reasonable... famous
> last words.)
>
> Is it true that neither of Pg SQL \copy and/or Excel can adapt as I
> wish?  What about psql \copy?  I fail to read in the docs for the Pg
> products any support for this.
>
> If Excel can adapt, at least one experienced user I've asked, doesn't
> know about it.
>
> Thanks.
>
As of version 8.0 Pg has been taught how to "do the right thing" to
copy CSV files:

COPY mytable TO 'myfile' WITH CSV;


--
Remove -42 for email