Re: CSV delim quoting differences PgCOPY, Excel etc...

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