ERROR: extra data after last expected column - COPY

Question

When I try to import the data with delimiter | I receive the error:

ERROR: extra data after last expected column

I am able to load the data if I remove double quote or single quote from the filed which have issue in the below sample data but my requirement is I need all data without removing any.

This is my copy command:

COPY public.dimingredient FROM '/Users//Downloads/archive1/test.txt' 
DELIMITER '|' NULL AS ''  CSV HEADER ESCAPE AS '"'  ;

My table:

  public.dimingredient
(
    dr_id integer NOT NULL,
    dr_loadtime timestamp(6) without time zone NOT NULL,
    dr_start timestamp(6) without time zone NOT NULL,
    dr_end timestamp(6) without time zone NOT NULL,
    dr_current boolean NOT NULL,
    casnumber character varying(100) COLLATE pg_catalog."default" NOT NULL,
    ingredientname character varying(300) COLLATE pg_catalog."default" NOT NULL,
    matchingstrategy character varying(21) COLLATE pg_catalog."default",
    percentofconfidence double precision,
    disclosurestatus character varying(42) COLLATE pg_catalog."default",
    issand character varying(1) COLLATE pg_catalog."default",
    sandmeshsize character varying(20) COLLATE pg_catalog."default",
    sandquality character varying(20) COLLATE pg_catalog."default",
    isresincoated character varying(1) COLLATE pg_catalog."default",
    isartificial character varying(1) COLLATE pg_catalog."default",
    CONSTRAINT dimingredient_pkey PRIMARY KEY (dr_id)
)

my data:

5144|2016-07-01 13:34:25.1001891|1900-01-01 00:00:00.0000000|9999-12-31 23:59:59.9999999|True|93834|"9-octadecenamide,n,n-bis(2-hydroxyethyl)-, (9z)"|"NO CAS MATCH FOUND"||Disclosed|||||

5145|2016-07-01 13:34:25.1001891|1900-01-01 00:00:00.0000000|9999-12-31 23:59:59.9999999|True|93834|"9-octadecenamide,n,n-bis-2(hydroxy-ethyl)-,(z)""|"NO CAS MATCH FOUND"||Disclosed|||||

The last delimiter has nothing following it? should there be data there as some would be expected ? Can you show us the table you are loading into ? — VynlJunkie
@rajkumars - You should edit your post instead of pasting all that in a comment. I added your table DDL along with some formatting, but did not copy over your data as it is truncated and I don't want to misrepresent it. You should, though, update your question with a line or two from your file if it is not sensitive information. — Nick
How is public.table defined? How many columns does it have? — Laurenz Albe
@LaurenzAlbe earlier just gave sample copy command.. now I have changed correctly and I have mentioned my test data and table columns how it looks in the below — rajkumar s
@rajkumars - Right now I am looking at 9 questions to the right under "Related" that are all asking the same thing. Have you read any of them to see if your solution is mentioned? — Nick

Laurenz Albe Laurenz Albe · Accepted Answer · 2017-07-28T07:14:35

Omitting the empty line in your dample data, I get a different error message with 9.6, to wit:

ERROR:  unterminated CSV quoted field
CONTEXT:  COPY dimingredient, line 3: "5145|2016-07-01 13:34:25.1001891|1900-01-01 00:00:00.0000000|9999-12-31 23:59:59.9999999|True|93834|..."

Strangely enough, that error message has been there since CSV COPY was introduced in version 8.0, so I wonder how your data are different from the data you show above.

The error message is easily explained: There is an odd number of quotation characters (") in the second line.

Since two doubled quotes in a quoted string are interpreted as a single double quote (" is escaped as ""), the fields in the second line are:

5145
2016-07-01 13:34:25.1001891
1900-01-01 00:00:00.0000000
9999-12-31 23:59:59.9999999
True
93834
9-octadecenamide,n,n-bis-2(hydroxy-ethyl)-,(z)"|NO CAS MATCH FOUND||Disclosed|||||

... and then COPY hits the end of file while parsing a quoted string. Hence the error.

The solution is to use an even number of " characters per field.

If you need a " character in a field, either choose a different QUOTE or quote the field and double the ".

ERROR: extra data after last expected column - COPY

1 Answers