0

I am trying to import a csv file to postgres db. I tried first through pgadmin tool and got error - ERROR: unterminated CSV quoted field. Then i imported it through command - \copy table FROM filename DELIMITER ',' CSV ENCODING 'UTF8' QUOTE E'\b' ESCAPE ''''; then got error -

ERROR: extra data after last expected column

I checked even.. there are no extra data after last column. Both files and my table in DB has 6 columns.

CREATE TABLE STATEMENT : 

CREATE TABLE public.test_table ( 
  classification character varying(255) COLLATE pg_catalog."default", 
  code character varying(255) COLLATE pg_catalog."default", 
  desc character varying(255) COLLATE pg_catalog."default", 
  startDate date, jurisdiction character varying(255) 
  COLLATE pg_catalog."default", endDate date )

What can be the issue. Can someone help?

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
Prags
  • 1
  • 1
  • 2
  • 3
    Please [edit] the question and add the `CREATE TABLE` statement and a sample of the CSV file to the question. Try to [format the code](https://stackoverflow.com/editing-help). Headbanging is not the best way to solve such problems. – Laurenz Albe Jan 08 '20 at 15:10
  • so my csv is the client data which has around 1 lac records with chars and date and integer with 6 column. Create table statement : CREATE TABLE public.test_table ( classification character varying(255) COLLATE pg_catalog."default", code character varying(255) COLLATE pg_catalog."default", desc character varying(255) COLLATE pg_catalog."default", startDate date, jurisdiction character varying(255) COLLATE pg_catalog."default", endDate date ) – Prags Jan 08 '20 at 15:30
  • Please [edit] the question and add the `CREATE TABLE` statement and a sample of the CSV file to the question. Try to [format the code](https://stackoverflow.com/editing-help). – Laurenz Albe Jan 08 '20 at 15:33
  • @Prags please add a sample of your CSV file – Jim Jones Jan 09 '20 at 09:23

3 Answers3

0

You can import your csv file with Pgadmin itself Just right click table and press Import part. Then you should select filename, then file type whether it is txt or csv, after that you need to select columns. Make sure your csv file data columns and table columns match. One important thing is that You should point out delimiter also. That means inide your csv file rows maybe seperated with , or ;. I suppose postgres see some extra characters like , and it sees it as new column

Abdusoli
  • 661
  • 1
  • 8
  • 24
0

If you like to use terminal try this 

\copy "table_name" FROM '/path/to/csvfile' DELIMITER ',' CSV

read this for more details

SANDEEP S S
  • 68
  • 1
  • 9
0

The most convenient way I found to resolve such errors is to convert the csv file to a text file. Here is a link to my answer with more details - Link

Pushpak
  • 301
  • 3
  • 6