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..