CODEWITHSUNDEEP
postgresqlcsvheader
Kate Brunette
Kate Brunette

Reputation: 1

Syntax Error Copying CSV to Postgres

Very novice PostgreSQL user here trying to import a CSV file into an existing table in a Postgres database. I'm working with PgAdmin III on Windows 8.

Here is the query: 

COPY "Mobility Survey2" FROM 'C:\\Users\\Public\\2011-2.csv'
WITH CSV HEADER DELIMITER ',';

And here is the error message: 

ERROR:  la sintaxis de entrada no es válida para integer: «1    25099   1020624     4   4   8           2   903 1   2   4   4   1                               1   3           0   2   2   16.6»

CONTEXT:  COPY Mobility Survey2, línea 2, columna ORDEN: «1 25099   1020624     4   4   8           2   903 1   2   4   4   1                               1   3           0   2   2   16.6»

(Sorry for the Spanish--translates as "entry syntax is not valid for integer")

I've tried looking at some other questions here including this one (Importing CSV file into PostgreSQL) but I'm not finding a way to fix the problem. Very likely just because I'm so unfamiliar with Postgres and SQL in general, but am I missing something in my query? I think it has to do with the Header but I don't know how to fix it.

Upvotes: 0

Views: 1593

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61506

The delimiter passed to COPY is ',' but looking at the faulty line, there's no comma in it. It looks like the delimiter is a tab character instead.

Try instead:

COPY "Mobility Survey2" FROM 'C:\\Users\\Public\\2011-2.csv'
WITH CSV HEADER DELIMITER E'\t';

As for the HEADER keyword, it's needed if the first line of the data file consists of the column names, otherwise it should be removed.

Upvotes: 1

Related Questions