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

On Mon, 2022-03-07 at 15:56 -0800, Steve Midgley wrote:
> 
> 
> On Mon, Mar 7, 2022 at 3:54 PM Steve Midgley <science@misuse.org>
> wrote:
> > 
> > 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..
> > 
> > 
> 
> 
> Also, have you tried ASCII encoding or something very permissive like
> that? If that works but UTF-8 doesn't, it might be a clue that
> there's an errant char buried in your CSV file. Also, maybe try
> looking at your CSV file with a hex editor.. The weirdest stuff can
> turn up in "wild caught" CSVs.. 

So I guess when I ran: 
awk -F "|" '{if (NF != 25) print}'
the first time it was against the wrong file.  I just tried again and
it produced a bunch of output.  So this is telling me there are extra
'|' in those lines?

-- 
Hacktorious