Re: ERROR: extra data after last expected column - Mailing list pgsql-sql
From | Scott Macri |
---|---|
Subject | Re: ERROR: extra data after last expected column |
Date | |
Msg-id | 354e10f466876aad50a77fa6fcb49206c29e678f.camel@gmail.com Whole thread Raw |
In response to | Re: ERROR: extra data after last expected column (Steve Midgley <science@misuse.org>) |
List | pgsql-sql |
It looks like it might have something to do with the line length. All the output from the AWK command are a continuation of the line above it. There is no line break, however. I'm investigating now. 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.. -- Hacktorious