\copy command error

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

Hi Andy,

Your CSV lines have 10 fields (10 comma separated values). Probably, in the
data you are exporting, the City/State is one field, but the comma is being
exported, thus giving you 10 fields. If this is the case, exporting the
City/state field wrapped in quotation marks should do the truck, or use a
different delimiter, like Tab

Mark

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/