CODEWITHSUNDEEP
pythonpostgresqlcsv
Mr. Concolato
Mr. Concolato

Reputation: 2230

Import Error on CSV Import with Postgress COPY

CSV file is simple and looks like this:

Name,Channel0,Channel1,Channel2,Channel3,Channel4
CSBA10,125833,147883,184269,162270,151366
CSBA20,125533,145883,154269,152270,155365

Table looks like this:

CREATE TABLE public."csTest"
(
    idname character varying(200) COLLATE pg_catalog."default" NOT NULL DEFAULT nextval('seqtest'::regclass),
    channel0 double precision,
    channel1 double precision,
    channel2 double precision,
    channel3 double precision,
    channel4 double precision,
    CONSTRAINT "csTest_pkey" PRIMARY KEY (idname)
);

Query looks like this:

COPY public."csTest" FROM  '/file/on/server.csv' DELIMITER ',' CSV;

All of this is being done with a simple python script and query is what is failing and I have no idea why. But, just in case, here is the python code for your convenience. 

        conn = psycopg2.connect(
            "host="+host+" port="+port+" dbname="+dbname+
            " user="+user+" password="+password+""
        )

        cur = conn.cursor()
        cur.execute(query)
        conn.commit()
        conn.close()

Error Message: psycopg2.DataError: invalid input syntax for type numeric: "Channel0"

What am I missing?

Thanks in advance.

Upvotes: 0

Views: 34

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51406

COPY public."csTest" FROM  '/file/on/server.csv' DELIMITER ',' CSV HEADER;

should do the trick, as it reads first line as data, not as column names if you don't specify it

update also I predict problems with numeric(10,10) and your data - maybe you meant numeric (20,10) or alike?.. numeric(10,10) would accept 0-0.9999999999 range

Upvotes: 1

Related Questions