agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedERROR: extra data after last expected column
12+ messages / 6 participants
[nested] [flat]
* ERROR: extra data after last expected column
@ 2022-03-07 01:59 Scott Macri <Scott@BITSnBYTES.io>
2022-03-07 09:08 ` Re: ERROR: extra data after last expected column Sándor Daku <daku.sandor@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Scott Macri @ 2022-03-07 01:59 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: extra data after last expected column
2022-03-07 01:59 ERROR: extra data after last expected column Scott Macri <Scott@BITSnBYTES.io>
@ 2022-03-07 09:08 ` Sándor Daku <daku.sandor@gmail.com>
2022-03-07 09:57 ` Re: ERROR: extra data after last expected column Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Sándor Daku @ 2022-03-07 09:08 UTC (permalink / raw)
To: Scott@bitsnbytes.io; +Cc: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: extra data after last expected column
2022-03-07 01:59 ERROR: extra data after last expected column Scott Macri <Scott@BITSnBYTES.io>
2022-03-07 09:08 ` Re: ERROR: extra data after last expected column Sándor Daku <daku.sandor@gmail.com>
@ 2022-03-07 09:57 ` Rob Sargent <robjsargent@gmail.com>
2022-03-07 23:33 ` Re: ERROR: extra data after last expected column scott macri <hacktorious@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Rob Sargent @ 2022-03-07 09:57 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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}'
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: extra data after last expected column
2022-03-07 01:59 ERROR: extra data after last expected column Scott Macri <Scott@BITSnBYTES.io>
2022-03-07 09:08 ` Re: ERROR: extra data after last expected column Sándor Daku <daku.sandor@gmail.com>
2022-03-07 09:57 ` Re: ERROR: extra data after last expected column Rob Sargent <robjsargent@gmail.com>
@ 2022-03-07 23:33 ` scott macri <hacktorious@gmail.com>
2022-03-07 23:54 ` Re: ERROR: extra data after last expected column Steve Midgley <science@misuse.org>
2022-03-07 23:57 ` Re: ERROR: extra data after last expected column Rob Sargent <robjsargent@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: scott macri @ 2022-03-07 23:33 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
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}'
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: extra data after last expected column
2022-03-07 01:59 ERROR: extra data after last expected column Scott Macri <Scott@BITSnBYTES.io>
2022-03-07 09:08 ` Re: ERROR: extra data after last expected column Sándor Daku <daku.sandor@gmail.com>
2022-03-07 09:57 ` Re: ERROR: extra data after last expected column Rob Sargent <robjsargent@gmail.com>
2022-03-07 23:33 ` Re: ERROR: extra data after last expected column scott macri <hacktorious@gmail.com>
@ 2022-03-07 23:54 ` Steve Midgley <science@misuse.org>
2022-03-07 23:56 ` Re: ERROR: extra data after last expected column Steve Midgley <science@misuse.org>
1 sibling, 1 reply; 12+ messages in thread
From: Steve Midgley @ 2022-03-07 23:54 UTC (permalink / raw)
To: scott macri <hacktorious@gmail.com>; +Cc: Rob Sargent <robjsargent@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
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..
Steve
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: extra data after last expected column
2022-03-07 01:59 ERROR: extra data after last expected column Scott Macri <Scott@BITSnBYTES.io>
2022-03-07 09:08 ` Re: ERROR: extra data after last expected column Sándor Daku <daku.sandor@gmail.com>
2022-03-07 09:57 ` Re: ERROR: extra data after last expected column Rob Sargent <robjsargent@gmail.com>
2022-03-07 23:33 ` Re: ERROR: extra data after last expected column scott macri <hacktorious@gmail.com>
2022-03-07 23:54 ` Re: ERROR: extra data after last expected column Steve Midgley <science@misuse.org>
@ 2022-03-07 23:56 ` Steve Midgley <science@misuse.org>
2022-03-08 01:40 ` Re: ERROR: extra data after last expected column Scott Macri <Scott@BITSnBYTES.io>
2022-03-08 01:51 ` Re: ERROR: extra data after last expected column Scott Macri <Scott@BITSnBYTES.io>
0 siblings, 2 replies; 12+ messages in thread
From: Steve Midgley @ 2022-03-07 23:56 UTC (permalink / raw)
To: scott macri <hacktorious@gmail.com>; +Cc: Rob Sargent <robjsargent@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
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..
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: extra data after last expected column
2022-03-07 01:59 ERROR: extra data after last expected column Scott Macri <Scott@BITSnBYTES.io>
2022-03-07 09:08 ` Re: ERROR: extra data after last expected column Sándor Daku <daku.sandor@gmail.com>
2022-03-07 09:57 ` Re: ERROR: extra data after last expected column Rob Sargent <robjsargent@gmail.com>
2022-03-07 23:33 ` Re: ERROR: extra data after last expected column scott macri <hacktorious@gmail.com>
2022-03-07 23:54 ` Re: ERROR: extra data after last expected column Steve Midgley <science@misuse.org>
2022-03-07 23:56 ` Re: ERROR: extra data after last expected column Steve Midgley <science@misuse.org>
@ 2022-03-08 01:40 ` Scott Macri <Scott@BITSnBYTES.io>
1 sibling, 0 replies; 12+ messages in thread
From: Scott Macri @ 2022-03-08 01:40 UTC (permalink / raw)
To: Steve Midgley <science@misuse.org>; +Cc: Rob Sargent <robjsargent@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: extra data after last expected column
2022-03-07 01:59 ERROR: extra data after last expected column Scott Macri <Scott@BITSnBYTES.io>
2022-03-07 09:08 ` Re: ERROR: extra data after last expected column Sándor Daku <daku.sandor@gmail.com>
2022-03-07 09:57 ` Re: ERROR: extra data after last expected column Rob Sargent <robjsargent@gmail.com>
2022-03-07 23:33 ` Re: ERROR: extra data after last expected column scott macri <hacktorious@gmail.com>
2022-03-07 23:54 ` Re: ERROR: extra data after last expected column Steve Midgley <science@misuse.org>
2022-03-07 23:56 ` Re: ERROR: extra data after last expected column Steve Midgley <science@misuse.org>
@ 2022-03-08 01:51 ` Scott Macri <Scott@BITSnBYTES.io>
2022-03-08 02:40 ` Re: ERROR: extra data after last expected column Rob Sargent <robjsargent@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Scott Macri @ 2022-03-08 01:51 UTC (permalink / raw)
To: Steve Midgley <science@misuse.org>; +Cc: Rob Sargent <robjsargent@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: extra data after last expected column
2022-03-07 01:59 ERROR: extra data after last expected column Scott Macri <Scott@BITSnBYTES.io>
2022-03-07 09:08 ` Re: ERROR: extra data after last expected column Sándor Daku <daku.sandor@gmail.com>
2022-03-07 09:57 ` Re: ERROR: extra data after last expected column Rob Sargent <robjsargent@gmail.com>
2022-03-07 23:33 ` Re: ERROR: extra data after last expected column scott macri <hacktorious@gmail.com>
2022-03-07 23:54 ` Re: ERROR: extra data after last expected column Steve Midgley <science@misuse.org>
2022-03-07 23:56 ` Re: ERROR: extra data after last expected column Steve Midgley <science@misuse.org>
2022-03-08 01:51 ` Re: ERROR: extra data after last expected column Scott Macri <Scott@BITSnBYTES.io>
@ 2022-03-08 02:40 ` Rob Sargent <robjsargent@gmail.com>
2022-03-08 03:47 ` Re: ERROR: extra data after last expected column scott macri <hacktorious@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Rob Sargent @ 2022-03-08 02:40 UTC (permalink / raw)
To: Scott@BITSnBYTES.io, Steve Midgley <science@misuse.org>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On 3/7/22 18:51, Scott Macri wrote:
> 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.
>
>
> 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
Can't tell for sure but could be mac/dos/unix EOLN mixup. Some tools are
better than others in dealing with a mixture of those.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: extra data after last expected column
2022-03-07 01:59 ERROR: extra data after last expected column Scott Macri <Scott@BITSnBYTES.io>
2022-03-07 09:08 ` Re: ERROR: extra data after last expected column Sándor Daku <daku.sandor@gmail.com>
2022-03-07 09:57 ` Re: ERROR: extra data after last expected column Rob Sargent <robjsargent@gmail.com>
2022-03-07 23:33 ` Re: ERROR: extra data after last expected column scott macri <hacktorious@gmail.com>
2022-03-07 23:54 ` Re: ERROR: extra data after last expected column Steve Midgley <science@misuse.org>
2022-03-07 23:56 ` Re: ERROR: extra data after last expected column Steve Midgley <science@misuse.org>
2022-03-08 01:51 ` Re: ERROR: extra data after last expected column Scott Macri <Scott@BITSnBYTES.io>
2022-03-08 02:40 ` Re: ERROR: extra data after last expected column Rob Sargent <robjsargent@gmail.com>
@ 2022-03-08 03:47 ` scott macri <hacktorious@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: scott macri @ 2022-03-08 03:47 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: Steve Midgley <science@misuse.org>; pgsql-sql <pgsql-sql@lists.postgresql.org>
On Mon, Mar 7, 2022, 9:40 PM Rob Sargent <robjsargent@gmail.com> wrote:
> On 3/7/22 18:51, Scott Macri wrote:
> > 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.
> >
> >
> > 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
>
> Can't tell for sure but could be mac/dos/unix EOLN mixup. Some tools are
> better than others in dealing with a mixture of those.
>
> I found an extra pipe character at the end of each line. It seems like
> the data coming from the sender is messed up. Gotta take a closer look
> tomorrow and determine the best plan of action.
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: extra data after last expected column
2022-03-07 01:59 ERROR: extra data after last expected column Scott Macri <Scott@BITSnBYTES.io>
2022-03-07 09:08 ` Re: ERROR: extra data after last expected column Sándor Daku <daku.sandor@gmail.com>
2022-03-07 09:57 ` Re: ERROR: extra data after last expected column Rob Sargent <robjsargent@gmail.com>
2022-03-07 23:33 ` Re: ERROR: extra data after last expected column scott macri <hacktorious@gmail.com>
@ 2022-03-07 23:57 ` Rob Sargent <robjsargent@gmail.com>
2022-03-08 01:42 ` Re: ERROR: extra data after last expected column Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Rob Sargent @ 2022-03-07 23:57 UTC (permalink / raw)
To: scott macri <hacktorious@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On 3/7/22 16:48, scott macri wrote:
>
>
> On Mon, Mar 7, 2022, 6:42 PM Rob Sargent <robjsargent@gmail.com> wrote:
>
> On 3/7/22 16:33, scott macri wrote:
>> No luck
> Bummer. Best to bottom post or in-line comment on this forum.
>
>
>>
>> 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
>>>
> Simpler yet is to make the columns "text"
>
>>> 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');
>>>
> You've verified the encoding is 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|..."
>>>
>>>
> Does line two generate the same error?
> Is there perhaps a funky line ending?
>
>
> Yes it does.
>
>
Can we see the live DDL of option_details table (i.e. from psql \d
option_details)?
Best to reply to the list so we're all on the same page
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: ERROR: extra data after last expected column
2022-03-07 01:59 ERROR: extra data after last expected column Scott Macri <Scott@BITSnBYTES.io>
2022-03-07 09:08 ` Re: ERROR: extra data after last expected column Sándor Daku <daku.sandor@gmail.com>
2022-03-07 09:57 ` Re: ERROR: extra data after last expected column Rob Sargent <robjsargent@gmail.com>
2022-03-07 23:33 ` Re: ERROR: extra data after last expected column scott macri <hacktorious@gmail.com>
2022-03-07 23:57 ` Re: ERROR: extra data after last expected column Rob Sargent <robjsargent@gmail.com>
@ 2022-03-08 01:42 ` Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Ron @ 2022-03-08 01:42 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 3/7/22 17:57, Rob Sargent wrote:
> On 3/7/22 16:48, scott macri wrote:
[snip]
>>
>>>> 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');
>>>>
>> You've verified the encoding is 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|..."
>>>>
Might there be pipe characters in one of the columns?
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2022-03-08 03:47 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-03-07 01:59 ERROR: extra data after last expected column Scott Macri <Scott@BITSnBYTES.io>
2022-03-07 09:08 ` Re: ERROR: extra data after last expected column Sándor Daku <daku.sandor@gmail.com>
2022-03-07 09:57 ` Re: ERROR: extra data after last expected column Rob Sargent <robjsargent@gmail.com>
2022-03-07 23:33 ` Re: ERROR: extra data after last expected column scott macri <hacktorious@gmail.com>
2022-03-07 23:54 ` Re: ERROR: extra data after last expected column Steve Midgley <science@misuse.org>
2022-03-07 23:56 ` Re: ERROR: extra data after last expected column Steve Midgley <science@misuse.org>
2022-03-08 01:40 ` Re: ERROR: extra data after last expected column Scott Macri <Scott@BITSnBYTES.io>
2022-03-08 01:51 ` Re: ERROR: extra data after last expected column Scott Macri <Scott@BITSnBYTES.io>
2022-03-08 02:40 ` Re: ERROR: extra data after last expected column Rob Sargent <robjsargent@gmail.com>
2022-03-08 03:47 ` Re: ERROR: extra data after last expected column scott macri <hacktorious@gmail.com>
2022-03-07 23:57 ` Re: ERROR: extra data after last expected column Rob Sargent <robjsargent@gmail.com>
2022-03-08 01:42 ` Re: ERROR: extra data after last expected column Ron <ronljohnsonjr@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox