"extra data after last expected column" while trying to import a csv file into postgresql

Question

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 | 

Piddling

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 ? – Averroism

I tried to specify the columns but it's still the same problem. I edited my question. – Piddling

Your table contain 3 columns, but your csv file that you want to import contains 6 – Averroism

Yes but I specify the columns I want to import as specified in the documentation 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." – Piddling

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! – Averroism

What this column statement stands for then? – Piddling

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... – Averroism

I created a table with all the gtfs fields and I specify my columns but I still have the same error message. – Piddling

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 ','; – Averroism

Answer 1

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.

Colombes

Thanks, this command works COPY agency (agency_id, agency_name, agency_url, agency_timezone,agency_lang,agency_phone) FROM 'myFile.txt' CSV HEADER DELIMITER ','; – Piddling

Hi Fred, which changes did you make? – Prepay

Answer 2

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.

Forfeiture

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. – Haricot

Are we still putting dollar signs in 'Microsoft' in case anyone though they were a charity? – Dorsad

Answer 3

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

Priapic

Answer 4

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()

Gladine

Answer 5

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

Colombes

What are these DDL and how do I include it? I tried with the '\COPY' but it didn't change anything. – Piddling

@yowza you can get the DDL using the \d like : \d my_table; – Averroism

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 ','; – Colombes

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 – Piddling

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. – Colombes

-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. – Disaffirm

Answer 6

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 .

Samira