How to import a.csv file into a postgresql database using \copy?

Question

I am trying to import a .csv file into a postgresql database. I am using pgadmin III for that on a win7 machine.

My query looks like that:

\COPY pop_grid(GRID_ID, POP_TOT, YEAR, METHD_CL, CNTR_CODE, DATA_SRC) from 'C:\...\popgrid.csv' DELIMITERS ',' CSV;

The data inside the .csv looks like that:

1kmN5142E2862 2 2006 D IS AIT
1kmN5141E2862 13 2006 D IS AIT

My table:

CREATE TABLE pop_grid 
(
 GRID_ID text PRIMARY KEY,
 POP_TOT int NOT NULL,
 YEAR date NOT NULL,
 METHD_CL varchar(2) NOT NULL,
 CNTR_CODE varchar(32) NOT NULL,
 DATA_SRC varchar(4) NOT NULL
);

Why do I get this error?

ERROR: Missing data for column "pop_tot"
CONTEXT: COPY pop_grid, Row 1: "1kmN5142E2862;2;2006;D;IS;AIT"

There is nothing missing?

Answer 1

You tell the COPY command to look for commas as delimiters (DELIMITERS ','), but there are no commas in your CSV. Use the 'text' format instead (it's the default, so you don't have to specify it) and do not specify a delimiter:

The default is a tab character in text format.

(source)

• np00: yeah, that was it! I should really at least try to understand what I'm copying :) The delimiters was actually ';' instead of ','. They were missing in my question, because I copied it out of excel.