Thread: \copy command error

hi,

I can't seem to get the \copy command to work.

Here's my syntax:

GISCI# \copy gisp from 'd:/projects/gisp/gisp.csv' delimiter ','

ERROR extra data after last expected column Line 1


It is taking all of the fields and grouping them into one field, the .csv has about 4700 records, here is what the first two look like:

Jon,JF,Doe,GIS Coordinator,Miami University,Ohio,Oxford,OH,United States,10/25/02
Janet,,Doe,PhD Student and Professional Geologist,York University,Toronto,ON,Canada,9/25/07

The table has 9 fields mostly varchar and one date field.

I'm running 8.3 on Windows Vista Ultimate

thanks.

Andy

--
Andrew Maracini, GISP/AICP
Superior GIS Solutions LLC

3309 N.Casaloma Dr. #114
Appleton, WI 54913

http://www.superiorgissolutions.com
920-574-2090
906-361-4132 (cell)

Mark Watson wrote:
>
> *>De :* pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] *De la part de* Andrew
> Maracini
> >*Envoyé :* 3 août 2009 11:46
> >*À :* pgsql-general@postgresql.org
> >*Objet :* [GENERAL] \copy command error
>
> >
>
> >hi,
> >
> >I can't seem to get the \copy command to work.
> >
> >Here's my syntax:
> >
> >GISCI# \copy gisp from 'd:/projects/gisp/gisp.csv' delimiter ','
> >
> >ERROR extra data after last expected column Line 1
> >
> >
> >It is taking all of the fields and grouping them into one field, the
> .csv has about 4700 records, here is what
>
Maybe the error is \c   because '\c'   is used to connect  to another
database.
try   the same line without '\'  only  :
copy gisp from d:/projects/gisp/gisp.csv'   delimiters  ',';

On Mon, Aug 03, 2009 at 01:25:56PM -0400, Mark Watson wrote:
> Andrew Maracini wrote:
> >GISCI# \copy gisp from 'd:/projects/gisp/gisp.csv' delimiter ','
>
> If this is the case, exporting the
> City/state field wrapped in quotation marks should do the trick

You'll want to use the real CSV parser then, the code in psql would look
like this:

  \copy gisp FROM 'd:/projects/gisp/gisp.csv' WITH CSV

PG and MS Excel have an almost identical definition of what a CSV file
should look like, opening the file in Excel is always a good quick check
as to why PG isn't liking the file.

--
  Sam  http://samason.me.uk/

On Mon, Aug 03, 2009 at 01:18:06PM -0500, erobles wrote:
> Maybe the error is \c   because '\c'   is used to connect  to another
> database.
> try   the same line without '\'  only  :
> copy gisp from d:/projects/gisp/gisp.csv'   delimiters  ',';

\copy is a special command in psql that does a copy from the system that
psql is running in, rather than a normal COPY command that runs on the
server.

I believe \copy is implemented as a COPY FROM STDIN... with psql
automatically piping the data over the connection for you.

--
  Sam  http://samason.me.uk/