From: | Scott Macri <Scott(at)BITSnBYTES(dot)io> |
---|---|
To: | Steve Midgley <science(at)misuse(dot)org> |
Cc: | Rob Sargent <robjsargent(at)gmail(dot)com>, pgsql-sql <pgsql-sql(at)lists(dot)postgresql(dot)org> |
Subject: | Re: ERROR: extra data after last expected column |
Date: | 2022-03-08 01:40:54 |
Message-ID: | 40a8c68234081f39c07c7ec300e96797ca6fa55b.camel@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general 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(at)misuse(dot)org>
> wrote:
> >
> > On Mon, Mar 7, 2022 at 3:34 PM scott macri <hacktorious(at)gmail(dot)com>
> > wrote:
> > > No luck
> > >
> > > On Mon, Mar 7, 2022, 4:58 AM Rob Sargent <robjsargent(at)gmail(dot)com>
> > > wrote:
> > > > On 3/7/22 02:08, Sándor Daku wrote:
> > > >
> > > > >
> > > > >
> > > > > On Mon, 7 Mar 2022 at 09:34, Scott Macri
> > > > > <Scott(at)bitsnbytes(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2022-03-08 01:42:43 | Re: ERROR: extra data after last expected column |
Previous Message | Rob Sargent | 2022-03-07 23:57:40 | Re: ERROR: extra data after last expected column |
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2022-03-08 01:42:43 | Re: ERROR: extra data after last expected column |
Previous Message | Rob Sargent | 2022-03-07 23:57:40 | Re: ERROR: extra data after last expected column |