Content-Type: multipart/alternative;
 boundary="------------j8u6UgL3XBvN2mvy07RfT0k7"
Message-ID: <a9c205a0-501c-ac7b-2b5c-7c25742ecef6@gmail.com>
Date: Mon, 7 Mar 2022 02:57:57 -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: pgsql-sql@lists.postgresql.org
References: <775429fc5428ca2640f72f1d29f6e8ec90955e6c.camel@gmail.com>
 <CAKyoTgZ6gc-5_SocE55vbD50Mmhd4iP-A3j0mu-RHq+aTu94Lg@mail.gmail.com>
From: Rob Sargent <robjsargent@gmail.com>
In-Reply-To: 
 <CAKyoTgZ6gc-5_SocE55vbD50Mmhd4iP-A3j0mu-RHq+aTu94Lg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/a9c205a0-501c-ac7b-2b5c-7c25742ecef6%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------j8u6UgL3XBvN2mvy07RfT0k7
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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}'


--------------j8u6UgL3XBvN2mvy07RfT0k7
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 3/7/22 02:08, Sándor Daku wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAKyoTgZ6gc-5_SocE55vbD50Mmhd4iP-A3j0mu-RHq+aTu94Lg@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div dir="ltr"><br>
        </div>
        <br>
        <div class="gmail_quote">
          <div dir="ltr" class="gmail_attr">On Mon, 7 Mar 2022 at 09:34,
            Scott Macri &lt;<a href="mailto:Scott@bitsnbytes.io"
              moz-do-not-send="true" class="moz-txt-link-freetext">Scott@bitsnbytes.io</a>&gt;
            wrote:<br>
          </div>
          <blockquote class="gmail_quote" style="margin:0px 0px 0px
            0.8ex;border-left:1px solid
            rgb(204,204,204);padding-left:1ex">I'm trying to use the
            postgres copy command and getting, "extra data<br>
            after last expected column".<br>
            <br>
            All items in the DB are currently set to varchar(255) to
            make it<br>
            simple.  I've checked for hidden characters in the file and
            don't see<br>
            any.  All the other files I've processed with this exact
            command worked<br>
            perfectly.  I've processed 10 other's so far.  The only
            difference I<br>
            notice is this one has significantly more columns.<br>
            <br>
            The number of columns in the DB (25) exactly match the
            number of<br>
            columns in the csv (25), which exactly match the number of
            columns<br>
            defined in my COPY command (25).  I've read practically
            every post on<br>
            the internet over the last two days containg this error and
            cannot<br>
            resolve it.  I am completely stumped at this point.  <br>
            <br>
            It pukes after the 9th column every time no matter what I
            change.<br>
            <br>
            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)<br>
            FROM '/home/dump/my_csv.csv' WITH (FORMAT CSV, DELIMITER
            '|', ENCODING<br>
            'UTF8');<br>
            <br>
            Row one data in file is below:<br>
            item a | item b | item c | item d | item e | item f | item g
            | item h |<br>
            item i | item j | item k | item l | item m | item n | item o
            | item p |<br>
            item q | item r | item s | item t | item u | item v | item w
            | item x |<br>
            item y<br>
            --- Line two would normally start here but no reason to show
            since it's<br>
            failing above. ---<br>
            <br>
            I get the following error:<br>
            ERROR:  extra data after last expected column<br>
            CONTEXT:  COPY option_details, line 1: "item a|item b|item
            c|item<br>
            d|item e|item f|item g|item h|item i|..."<br>
            <br>
            <br>
            Any help or advice would be greatly appreciated.  Thank you
            very much.<br>
            <br>
            -- <br>
            Hacktorious<br>
            <br>
          </blockquote>
          <div><br>
          </div>
          <div>Hi,</div>
          <div><br>
          </div>
          <div>I pretty sure it doesn't fail after the  9th column, just
            the context hint of the error message is cropped after that.</div>
          <div>My guess is a sneaky '|' somewhere inside one of your
            field.</div>
          <div><br>
          </div>
          <div>Regards,</div>
          <div>Sándor  <br>
          </div>
        </div>
      </div>
    </blockquote>
    if Sándor  is correct this will show the offenders<br>
    awk -F "|" '{if (NF != 25) print}'<br>
    <br>
    <br>
  </body>
</html>

--------------j8u6UgL3XBvN2mvy07RfT0k7--