CODEWITHSUNDEEP
postgresqlcsv
Username
Username

Reputation: 3663

Can I import CSV data into a table without knowing the columns of the CSV?

I have a CSV file file.csv.

In Postgres, I have made a table named grants:

CREATE TABLE grants
(

)
WITH (
  OIDS=FALSE
);
ALTER TABLE grants
  OWNER TO postgres;

I want to import file.csv data without having to specify columns in Postgres.

But if I run COPY grants FROM '/PATH/TO/grants.csv' CSV HEADER;, I get this error: ERROR: extra data after last expected column.

How do I import the CSV data without having to specify columns and types?

Upvotes: 1

Views: 6427

Answers (3)

ACV
ACV

Reputation: 10560

No, it is not possible using the COPY command

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.

COPY does not create columns for you.

Upvotes: 0

Rony Armon
Rony Armon

Reputation: 338

The best method for me was to convert the csv to dataframe and then follow

https://github.com/sp-anna-jones/data_science/wiki/Importing-pandas-dataframe-to-postgres

Upvotes: -1

Romain MAZIERE
Romain MAZIERE

Reputation: 46

The error is normal. You created a table with no column. The COPY command try to import data into the table with the good structure. So you have to create the table corresponding to your csv file before execute the COPY command.

I discovered pgfutter :

"Import CSV and JSON into PostgreSQL the easy way. This small tool abstract all the hassles and swearing you normally have to deal with when you just want to dump some data into the database"

Perhaps a solution ...

Upvotes: 1

Related Questions