68

I try to copy the content of a CSV file into my postgresql db and I get this error "extra data after last expected column".

The content of my CSV is 

    agency_id,agency_name,agency_url,agency_timezone,agency_lang,agency_phone
100,RATP (100),http://www.ratp.fr/,CET,,

and my postgresql command is 

COPY agency (agency_name, agency_url, agency_timezone) FROM 'myFile.txt' CSV HEADER DELIMITER ',';

Here is my table 

CREATE TABLE agency (
    agency_id character varying,
    agency_name character varying NOT NULL,
    agency_url character varying NOT NULL,
    agency_timezone character varying NOT NULL,
    agency_lang character varying,
    agency_phone character varying,
    agency_fare_url character varying
);

     Column      |       Type        | Modifiers 
-----------------+-------------------+-----------
 agency_id       | character varying | 
 agency_name     | character varying | not null
 agency_url      | character varying | not null
 agency_timezone | character varying | not null
 agency_lang     | character varying | 
 agency_phone    | character varying | 
 agency_fare_url | character varying |
Frederic Le Feurmou
  • 1,676
  • 1
  • 17
  • 22
  • 1
    This means that using the `,` delimiter, the `copy` command parsed more fields than the destination table! Can you please bring your destination table data structure and samples of your CSV content ? – Houari Nov 02 '14 at 17:55
  • 2
    I tried to specify the columns but it's still the same problem. I edited my question. – Frederic Le Feurmou Nov 02 '14 at 19:22
  • Your table contain 3 columns, but your csv file that you want to import contains `6` – Houari Nov 02 '14 at 19:26
  • 1
    Yes but I specify the columns I want to import as specified in the documentation http://www.postgresql.org/docs/9.2/static/sql-copy.html "If a list of columns is specified, COPY will only copy the data in the specified columns to or from the file. If there are any columns in the table that are not in the column list, COPY FROM will insert the default values for those columns." – Frederic Le Feurmou Nov 02 '14 at 19:35
  • 3
    Yes, but sadely, the postgreSQL `copy` command can't handle column selection from your file. In other words, you can only copy whole file at once! – Houari Nov 02 '14 at 19:45
  • 1
    What this column statement stands for then? – Frederic Le Feurmou Nov 02 '14 at 19:47
  • 1
    You could have a table with 6 columns and a file containing only 3 fields for example, and you can import your 3 fields to a spécific columns of your table... – Houari Nov 02 '14 at 19:50
  • 1
    I created a table with all the gtfs fields and I specify my columns but I still have the same error message. – Frederic Le Feurmou Nov 02 '14 at 19:54
  • 1
    I think that now you can do something like: `COPY agency (agency_id, agency_name, agency_url, agency_timezone,agency_lang,agency_phone) FROM 'myFile.txt' CSV HEADER DELIMITER ',';` – Houari Nov 02 '14 at 20:01

6 Answers6

55

Now you have 7 fields.

You need to map those 6 fields from the CSV into 6 fields into the table.

You cannot map only 3 fields from csv when you have it 6 like you do in:

\COPY agency (agency_name, agency_url, agency_timezone) FROM 'myFile.txt' CSV HEADER DELIMITER ',';

All fields from the csv file need to to be mapped in the copy from command.

And since you defined csv , delimiter is default, you don't need to put it.

Houari
  • 5,326
  • 3
  • 31
  • 54
Mladen Uzelac
  • 1,173
  • 1
  • 10
  • 14
6

Not sure this counts as an answer, but I just hit this with a bunch of CSV files, and found that simply opening them in Excel and re-saving them with no changes made the error go away. IOTW there is possibly some incorrect formatting in the source file that Excel is able to clean up automatically.

shacker
  • 14,712
  • 8
  • 89
  • 89
  • 2
    Just a word of warning. Opening things in Excel can change anything and everything. Yes it may fix this error but it will also make anything that looks like a timestamp a date, round all your long decimals and anything else Micro$oft thinks may be useful. – DataMacGyver Jun 15 '20 at 07:18
  • Are we still putting dollar signs in 'Microsoft' in case anyone though they were a charity? – Alan B Mar 29 '21 at 14:39
0

This error also occurs if you have same number of columns in both postgres table and csv file, even if you have specified delimiter ',' in \copy command. You also need to specify CSV.

In my case, one of my columns contained comma separated data and I execute:

db=# \copy table1 FROM '/root/db_scripts/input_csv.csv' delimiter ','
ERROR:  invalid input syntax for integer: "id"
CONTEXT:  COPY quiz_quiz, line 1, column id: "id"

It worked after adding CSV:

db=# \copy table1 FROM '/root/db_scripts/input_csv.csv' delimiter ',' CSV
COPY 47871
Mahesha999
  • 22,693
  • 29
  • 116
  • 189
0

For future visitors, when I had this problem it was because I was using a loop that wrote to the same io.StringsIO() variable before committing the query to the database (context).

If you're encountering this problem, make sure your code is like this:

for tableName in tableNames:
    output = io.StringsIO()
    ...
    output.seek(0)
    cur.copy_expert(f"COPY {tableName} FROM STDIN", output)
    conn.commit()

And not like this:

output = io.StringsIO()

for tableName in tableNames:
    ...
    output.seek(0)
    cur.copy_expert(f"COPY {tableName} FROM STDIN", output)
    conn.commit()
Aaron Meese
  • 1,670
  • 3
  • 22
  • 32
0

I just experienced this error and it took me 3 days to get a solution that's not even on YouTube. Here is the best approach to it 1 insert(copy) the csv file into a new folder, then grant access to "Everyone" by right clicking and selecting properties and then security. 2 after you have granted access to Everyone , the next step is to copy that folder that has your csv file into [ program file > postgresql > 14 > data] then go back to use the copy table command and not the insert table option. Your program file is located in your PC you just have to find where it is, and the look for the postgresql folder. Take care .

ChuksOFlavian
  • 1
-1

I tried your example and it works fine but ....

your command from the psql command line is missing \

database=#  \COPY agency FROM 'myFile.txt' CSV HEADER DELIMITER ',';

And next time please include DDL

I created DDL from the csv headers

Houari
  • 5,326
  • 3
  • 31
  • 54
Mladen Uzelac
  • 1,173
  • 1
  • 10
  • 14
  • 1
    What are these DDL and how do I include it? I tried with the '\COPY' but it didn't change anything. – Frederic Le Feurmou Nov 02 '14 at 19:19
  • @yowza you can get the DDL using the `\d` like : `\d my_table;` – Houari Nov 02 '14 at 19:31
  • DDL is Data Definition language i.e. Statements for creating table in this case. You are trying to map 3 columns in table from 6 columns in the csv file. Use this commands in psql with postgres user with custom database: drop table if exists agency; create table agency(agency_id int, agency_name text, agency_url text, agency_timezone text, agency_lang text, agency_phone text); \COPY agency FROM 'myFile.txt' CSV HEADER DELIMITER ','; – Mladen Uzelac Nov 02 '14 at 19:31
  • As I mentioned it in my other comment, I'm specifying the columns that I want to import so I don't see why it wouldn't work – Frederic Le Feurmou Nov 02 '14 at 19:37
  • Then adjust the csv file to your copy import statement. You cannot have 6 columns in the file, and 3 in the table. Or you use Python for importing the data with csv. There is a DictReader class in csv module. – Mladen Uzelac Nov 02 '14 at 19:41
  • -1 because the information about missing backslash is incorrect. `\copy` and `COPY` are different commands. `COPY` is a PostgreSQL statement that is executed server-side and can be called from other tools, like e.g. pgAdmin. `\copy` is the syntax specific to psql command-line tool and it reads files from the _client_. Both are correct in their own way, so please don't assume it to be a syntax error and don't mix them. – Sergey Kudriavtsev Apr 09 '19 at 09:39