agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Rob Sargent <robjsargent@gmail.com>
To: pgsql-sql@lists.postgresql.org
Subject: Re: ERROR: extra data after last expected column
Date: Mon, 7 Mar 2022 02:57:57 -0700
Message-ID: <a9c205a0-501c-ac7b-2b5c-7c25742ecef6@gmail.com> (raw)
In-Reply-To: <CAKyoTgZ6gc-5_SocE55vbD50Mmhd4iP-A3j0mu-RHq+aTu94Lg@mail.gmail.com>
References: <775429fc5428ca2640f72f1d29f6e8ec90955e6c.camel@gmail.com>
<CAKyoTgZ6gc-5_SocE55vbD50Mmhd4iP-A3j0mu-RHq+aTu94Lg@mail.gmail.com>
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}'
view thread (12+ messages) latest in thread
Message-ID: <a9c205a0-501c-ac7b-2b5c-7c25742ecef6@gmail.com>
Permalink: ../a9c205a0-501c-ac7b-2b5c-7c25742ecef6@gmail.com/
Also on: postgresql.org/message-id/a9c205a0-501c-ac7b-2b5c-7c25742ecef6@gmail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: robjsargent@gmail.com, pgsql-sql@lists.postgresql.org
Subject: Re: ERROR: extra data after last expected column
In-Reply-To: <a9c205a0-501c-ac7b-2b5c-7c25742ecef6@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox