Re: ERROR: extra data after last expected column - Mailing list pgsql-sql

On Mon, Mar 7, 2022 at 3:34 PM scott macri <hacktorious@gmail.com> wrote:

No luck

On Mon, Mar 7, 2022, 4:58 AM Rob Sargent <robjsargent@gmail.com> wrote:

On 3/7/22 02:08, Sándor Daku wrote:

On Mon, 7 Mar 2022 at 09:34, Scott Macri <Scott@bitsnbytes.io> wrote:

I'm trying to use the postgres copy command and getting, "extra data
after last expected column".

All items in the DB are currently set to varchar(255) to make it
simple.  I've checked for hidden characters in the file and don't see
any.  All the other files I've processed with this exact command worked
perfectly.  I've processed 10 other's so far.  The only difference I
notice is this one has significantly more columns.

The number of columns in the DB (25) exactly match the number of
columns in the csv (25), which exactly match the number of columns
defined in my COPY command (25).  I've read practically every post on
the internet over the last two days containg this error and cannot
resolve it.  I am completely stumped at this point. 

It pukes after the 9th column every time no matter what I change.

COPY option_details(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y)
FROM '/home/dump/my_csv.csv' WITH (FORMAT CSV, DELIMITER '|', ENCODING
'UTF8');

Row one data in file is below:
item a | item b | item c | item d | item e | item f | item g | item h |
item i | item j | item k | item l | item m | item n | item o | item p |
item q | item r | item s | item t | item u | item v | item w | item x |
item y
--- Line two would normally start here but no reason to show since it's
failing above. ---

I get the following error:
ERROR:  extra data after last expected column
CONTEXT:  COPY option_details, line 1: "item a|item b|item c|item
d|item e|item f|item g|item h|item i|..."


Any help or advice would be greatly appreciated.  Thank you very much.

--
Hacktorious

Hi,

I pretty sure it doesn't fail after the  9th column, just the context hint of the error message is cropped after that.

My guess is a sneaky '|' somewhere inside one of your field.

Regards,

Sándor 

if Sándor  is correct this will show the offenders
awk -F "|" '{if (NF != 25) print}'

Can you send the CSV file that is causing the problem as a CSV file attachment so some of us can try this as a full reproduction? I don't want to copy/paste the sample line from the text of the email as it seems like that wouldn't be a good replication path for such a weird bug..