Re: ERROR: extra data after last expected column

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}'