Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nRNEZ-0000ZP-K0 for pgsql-general@arkaria.postgresql.org; Mon, 07 Mar 2022 23:57:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nRNEY-0007XM-H3 for pgsql-general@arkaria.postgresql.org; Mon, 07 Mar 2022 23:57:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nRNEX-0007X6-Sx for pgsql-general@lists.postgresql.org; Mon, 07 Mar 2022 23:57:46 +0000 Received: from mail-pj1-x102d.google.com ([2607:f8b0:4864:20::102d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nRNEV-0001vE-Ef for pgsql-general@lists.postgresql.org; Mon, 07 Mar 2022 23:57:44 +0000 Received: by mail-pj1-x102d.google.com with SMTP id v4so15559621pjh.2 for ; Mon, 07 Mar 2022 15:57:43 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=message-id:date:mime-version:user-agent:subject:content-language:to :references:cc:from:in-reply-to; bh=9wWc1adXd4CY3W1/zZqpapTVV9MZnVI2r3JrPxF33wM=; b=TT+gZNRPGlt54tzXWpeVD5rOOYU/QGxSP75150g4ICFC5cIiirbXoD80O+aVfWLs+V 4zMSa3P/1EPwNdrt9qrEwEGi8RZov7ng8aMDmlBufifmtdIsXcMItnhYgctHS1+chwEn a0fU7cgRxygR2Xw9JISj0dXw9yZ/jHVU1CbLKAreQ59U2DGRfNXnSbhaXzbVTxQpjMkW Ol/oLl+8tEMvFLOx2y3KLP2k3u2+Kl2GaqpgUfzphWmt5lc6Q/qAO0SbvRbDabnGgYer UVSNhITNf4Wqx6vg++msU9t6Vn8odb/sF4iBaGMNdM9mdFdb1HE72gHPJN6o5oL/WGat xoOg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:message-id:date:mime-version:user-agent:subject :content-language:to:references:cc:from:in-reply-to; bh=9wWc1adXd4CY3W1/zZqpapTVV9MZnVI2r3JrPxF33wM=; b=ujRZ6WcsK4Xrk6iS7GATAfLibf7EYS/YDcZBLmsdrhxW4+mSXkDeuqnV1aZ4OHsj1M c1xY6afU/wYugR0gVBh+c2XBnjm2kAhcGi5RuN+YX9kf+luxhzZBhwbGkIX9Rp7Z5zOU UJhgjCC/0cGG4iyqZ8MaoTjC0o9NVypLlEX+d7KWo7cyV4W03u2nzUZkJFVx2J80ob6a m85eXLceeigmPnuKongNYelmjXa1yNy/+UwsAnweMriQGJEtYc0Yy+jBAZeqJp1oc6X1 uD9An5DW0mcdb2/DYRT6n04+OMKOfESl7xw30DOVHjAsXHpG3z7cIAm+kbbE9Y2G1CWZ rybQ== X-Gm-Message-State: AOAM533XoXzp1MIhS1JUOKgw5Z/TJz5X3NgIjOLP7p7HMu9qIttf/jWv vo8RUz0mI5ncASsGSuFj/L0= X-Google-Smtp-Source: ABdhPJyACUOc80GX4sjM754JwHy7Hw3Lb09yuhZo39KJ4tqmZ3SKXsiAikM8n7E9nQLbcMYoWcOLiQ== X-Received: by 2002:a17:902:d2ce:b0:151:6781:affa with SMTP id n14-20020a170902d2ce00b001516781affamr14750447plc.168.1646697462272; Mon, 07 Mar 2022 15:57:42 -0800 (PST) Received: from [10.128.71.194] ([155.98.131.2]) by smtp.gmail.com with ESMTPSA id t7-20020a17090a024700b001bf12386db4sm451359pje.47.2022.03.07.15.57.41 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 07 Mar 2022 15:57:41 -0800 (PST) Content-Type: multipart/alternative; boundary="------------Hwz39oImKifktEHso35Od8UT" Message-ID: <095fbaeb-9c9e-1c8f-42bd-d1e75eb81eb5@gmail.com> Date: Mon, 7 Mar 2022 16:57:40 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.6.1 Subject: Re: ERROR: extra data after last expected column Content-Language: en-CA To: scott macri References: <775429fc5428ca2640f72f1d29f6e8ec90955e6c.camel@gmail.com> Cc: "pgsql-generallists.postgresql.org" From: Rob Sargent In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------Hwz39oImKifktEHso35Od8UT Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 3/7/22 16:48, scott macri wrote: > > > On Mon, Mar 7, 2022, 6:42 PM Rob Sargent 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 >> wrote: >> >> On 3/7/22 02:08, Sándor Daku wrote: >>> >>> >>> On Mon, 7 Mar 2022 at 09:34, Scott Macri >>> 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 --------------Hwz39oImKifktEHso35Od8UT Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
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



--------------Hwz39oImKifktEHso35Od8UT--