Thread: Problem importing a csv file

Problem importing a csv file

From
planas
Date:
Hi,

I am attempting to import a csv file into a predefined, empty table using the following commands:

COPY ContactUpdates
FROM '/media/Fred/Work/SQL_Server/AP/ContactUpdates.csv'
WITH DELIMITERS ',' CSV

When testing the query I get the following error message

ERROR:  syntax error at or near "COPY"
LINE 1: ...(ANALYZE off, VERBOSE off, COSTS on, BUFFERS off )COPY Conta...
                                                             ^


********** Error **********

ERROR: syntax error at or near "COPY"
SQL state: 42601
Character: 59

I have been able to determine what the error is. Researching the question does not give me any clue what is wrong or what the syntax error is.

Using pgsql 9.1 rc1 on Ubuntu 11.04 (Gnome) 64 bit using pgadminIII
--
Jay Lozier
jslozier@gmail.com

Re: Problem importing a csv file

From
pasman pasmański
Date:
What if you run this query using psql?

--
------------
pasman

Re: Problem importing a csv file

From
Adrian Klaver
Date:
On Saturday, August 27, 2011 10:03:53 pm planas wrote:
> Hi,
>
> I am attempting to import a csv file into a predefined, empty table
> using the following commands:
>
> COPY ContactUpdates
> FROM '/media/Fred/Work/SQL_Server/AP/ContactUpdates.csv'
> WITH DELIMITERS ',' CSV
>
> When testing the query I get the following error message
>
> ERROR:  syntax error at or near "COPY"
> LINE 1: ...(ANALYZE off, VERBOSE off, COSTS on, BUFFERS off )COPY
> Conta...
>                                                              ^
>
>
> ********** Error **********
>
> ERROR: syntax error at or near "COPY"
> SQL state: 42601
> Character: 59
>
> I have been able to determine what the error is. Researching the
> question does not give me any clue what is wrong or what the syntax
> error is.

Is the above the only command being run? Where is this coming from?:

LINE 1: ...(ANALYZE off, VERBOSE off, COSTS on, BUFFERS off )...

>
> Using pgsql 9.1 rc1 on Ubuntu 11.04 (Gnome) 64 bit using pgadminIII

--
Adrian Klaver
adrian.klaver@gmail.com

Re: Problem importing a csv file

From
planas
Date:
On Sun, 2011-08-28 at 03:53 -0200, pasman pasmański wrote:
What if you run this query using psql?

-- 
------------
pasman


It did import the data and it gave no error messages

--
Jay Lozier
jslozier@gmail.com

Re: Problem importing a csv file

From
planas
Date:
On Sun, 2011-08-28 at 03:53 -0200, pasman pasmański wrote:
What if you run this query using psql?

-- 
------------
pasman


When I run the following:

DROP Table "ContactUpdates"

CREATE TABLE "ContactUpdates"
(
  "VendorID" integer NOT NULL,
  "LastName" character varying(45),
  "FirstName" character varying(45),
  CONSTRAINT "ContactUpdates_pkey" PRIMARY KEY ("VendorID" )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "ContactUpdates"
  OWNER TO postgres;

The table is created with proper columns

Then running

COPY CountUpdates
FROM '/media/Fred/Work/SQL_Server/AP/ContactUpdates.csv'
DELIMITER ',' CSV

gives this error message

ERROR:  relation "countupdates" does not exist


********** Error **********

ERROR: relation "countupdates" does not exist
SQL state: 42P01
Adding ""
COPY "CountUpdates"
FROM '/media/Fred/Work/SQL_Server/AP/ContactUpdates.csv'
DELIMITER ',' CSV

ERROR:  relation "CountUpdates" does not exist


********** Error **********

ERROR: relation "CountUpdates" does not exist
SQL state: 42P01

But the file exists, it was previously created. What is the problem?
--
Jay Lozier
jslozier@gmail.com

Re: Problem importing a csv file

From
Shoaib Mir
Date:
On Mon, Aug 29, 2011 at 11:21 AM, planas <jslozier@gmail.com> wrote:

CREATE TABLE "ContactUpdates"
(
  "VendorID" integer NOT NULL,
  "LastName" character varying(45),
  "FirstName" character varying(45),
  CONSTRAINT "ContactUpdates_pkey" PRIMARY KEY ("VendorID" ) 
ERROR: relation "countupdates" does not exist
SQL state: 42P01
Adding ""
COPY "CountUpdates"
FROM '/media/Fred/Work/SQL_Server/AP/ContactUpdates.csv'
DELIMITER ',' CSV

ERROR:  relation "CountUpdates" does not exist


Table you created is "ContactUpdates" and you are trying to COPY data into "CountUpdates"? why do you think the error is not what you expected??

cheers,
Shoaib 

Re: Problem importing a csv file

From
Adrian Klaver
Date:
On Sunday, August 28, 2011 5:48:50 pm you wrote:

> > > error is.
> >
> > Is the above the only command being run? Where is this coming from?:
> >
> > LINE 1: ...(ANALYZE off, VERBOSE off, COSTS on, BUFFERS off )...
>
> Explain mode, in pgadminIII
>
> > > Using pgsql 9.1 rc1 on Ubuntu 11.04 (Gnome) 64 bit using pgadminIII

As far as I know EXPLAIN does not work with COPY.

From the docs:
http://www.postgresql.org/docs/9.0/interactive/sql-explain.html
"
statement

    Any SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE, DECLARE, or CREATE
TABLE AS statement, whose execution plan you wish to see.

"
--
Adrian Klaver
adrian.klaver@gmail.com