Subject: Re: Invalid byte sequence
To: pgsql-admin@lists.postgresql.org
References: 
 <CALhHtcC1zP2ADshm_Qo=RHHsTyqhR0jErAydPBZf2-ZbUrFLWg@mail.gmail.com>
 <c30c40b2-721f-7fb9-7973-4a65f89d8e4b@jakobs.com>
 <CALhHtcCMKMUuw_QbZ1EyyjbMxnxGZoL8cZf+1BAUx_mvoYtP0g@mail.gmail.com>
From: Holger Jakobs <holger@jakobs.com>
Message-ID: <782159a8-4a60-dc25-2907-67e21f4acf99@jakobs.com>
Date: Thu, 1 Jul 2021 09:49:36 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.11.0
MIME-Version: 1.0
In-Reply-To: 
 <CALhHtcCMKMUuw_QbZ1EyyjbMxnxGZoL8cZf+1BAUx_mvoYtP0g@mail.gmail.com>
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="eomtPfR97nd6k4RdeORt62A0KHQyFBQ56"
Archived-At: 
 <https://www.postgresql.org/message-id/782159a8-4a60-dc25-2907-67e21f4acf99%40jakobs.com>
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--eomtPfR97nd6k4RdeORt62A0KHQyFBQ56
Content-Type: multipart/mixed; boundary="5KyopWFyCJz8NFeeDxqZI2ZVCkyv7OCYn";
 protected-headers="v1"
From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Message-ID: <782159a8-4a60-dc25-2907-67e21f4acf99@jakobs.com>
Subject: Re: Invalid byte sequence
References: <CALhHtcC1zP2ADshm_Qo=RHHsTyqhR0jErAydPBZf2-ZbUrFLWg@mail.gmail.com>
 <c30c40b2-721f-7fb9-7973-4a65f89d8e4b@jakobs.com>
 <CALhHtcCMKMUuw_QbZ1EyyjbMxnxGZoL8cZf+1BAUx_mvoYtP0g@mail.gmail.com>
In-Reply-To: <CALhHtcCMKMUuw_QbZ1EyyjbMxnxGZoL8cZf+1BAUx_mvoYtP0g@mail.gmail.com>

--5KyopWFyCJz8NFeeDxqZI2ZVCkyv7OCYn
Content-Type: multipart/alternative;
 boundary="------------BBC89DE983E7B2DE4176A79D"
Content-Language: de-DE

This is a multi-part message in MIME format.
--------------BBC89DE983E7B2DE4176A79D
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: quoted-printable

Then have a look at the line 377617 and check whether its content is=20
important. The following command shows the line on the terminal. Loading =

it into an interactive editor seems not to be an option due to the size=20
of the file

|sed -n '||377617 p;|||||377617| q' |/outputfile.csv

Then you can decide what to do.

Am 01.07.21 um 09:45 schrieb Yambu:
> Hi=C2=A0Holger
>
> There are 8mil lines, the file is not in /=C2=A0 , i just removed the o=
ther=20
> parts.
>
> On Thu, Jul 1, 2021 at 9:40 AM Holger Jakobs <holger@jakobs.com=20
> <mailto:holger@jakobs.com>> wrote:
>
>     How many lines has the file? Maybe line 377617 is the last one.
>
>     If so, you can just remove the last line containing this 0x00. The
>     following command can do this for you:
>
>     |sed -i '$ d' |/outputfile.csv
>
>     May I remark that having a file in the / directory is a bit
>     awkward? This directory should not be writable by any user (except
>     root, and root doesn't fiddle with files for a database).
>
>
>
>     Am 01.07.21 um 09:29 schrieb Yambu:
>>     Hello
>>
>>     How do i get past the error below.=C2=A0I have created a foreign t=
able
>>     which points to a csv file and when i try to create another table
>>     it gives error below
>>
>>     The csv file has funny characters, how do i bypass those character=
s.
>>
>>     create foreign table table1
>>     (
>>     id int,
>>     name text
>>     )
>>     SERVER local_file
>>     OPTIONS (filename '/outputfile.csv' ,
>>     format 'csv',
>>     header 'TRUE',
>>     delimiter ';'
>>     );
>>
>>
>>     create table2 as select * from=C2=A0table1;
>>
>>     ERROR: =C2=A0invalid byte sequence for encoding "UTF8": 0x00
>>     CONTEXT: =C2=A0COPY table1, line 377617
>
>     --=20
>     Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>
--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012


--------------BBC89DE983E7B2DE4176A79D
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html>
  <head>
    <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DUTF=
-8">
  </head>
  <body>
    <p>Then have a look at the line 377617 and check whether its content
      is important. The following command shows the line on the
      terminal. Loading it into an interactive editor seems not to be an
      option due to the size of the file<br>
    </p>
    <pre><code>sed -n <span>'</span></code><code><span>377617 p;</span></=
code><code><span><code><span></span></code><code><span> 377617</span></co=
de> q'</span> </code>/outputfile.csv</pre>
    <div class=3D"moz-cite-prefix">Then you can decide what to do.<br>
    </div>
    <div class=3D"moz-cite-prefix"><br>
    </div>
    <div class=3D"moz-cite-prefix">Am 01.07.21 um 09:45 schrieb Yambu:<br=
>
    </div>
    <blockquote type=3D"cite"
cite=3D"mid:CALhHtcCMKMUuw_QbZ1EyyjbMxnxGZoL8cZf+1BAUx_mvoYtP0g@mail.gmai=
l.com">
      <meta http-equiv=3D"content-type" content=3D"text/html; charset=3DU=
TF-8">
      <div dir=3D"ltr">Hi=C2=A0Holger
        <div><br>
        </div>
        <div>There are 8mil lines, the file is not in /=C2=A0 , i just
          removed the other parts.</div>
      </div>
      <br>
      <div class=3D"gmail_quote">
        <div dir=3D"ltr" class=3D"gmail_attr">On Thu, Jul 1, 2021 at 9:40=
 AM
          Holger Jakobs &lt;<a href=3D"mailto:holger@jakobs.com"
            moz-do-not-send=3D"true">holger@jakobs.com</a>&gt; wrote:<br>=

        </div>
        <blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px
          0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">=

          <div>
            <p>How many lines has the file? Maybe line 377617 is the
              last one.</p>
            <p>If so, you can just remove the last line containing this
              0x00. The following command can do this for you:<br>
            </p>
            <pre><code>sed -i <span>'$ d'</span> </code>/outputfile.csv</=
pre>
            <p>May I remark that having a file in the / directory is a
              bit awkward? This directory should not be writable by any
              user (except root, and root doesn't fiddle with files for
              a database).</p>
            <p><br>
            </p>
            <p><br>
            </p>
            <div>Am 01.07.21 um 09:29 schrieb Yambu:<br>
            </div>
            <blockquote type=3D"cite">
              <div dir=3D"ltr">Hello<br>
                <div><br>
                </div>
                <div>How do i get past the error below.=C2=A0I have creat=
ed a
                  foreign table which points to a csv file and when i
                  try to create another table it gives error below</div>
                <div><br>
                </div>
                <div>The csv file has funny characters, how do i bypass
                  those characters.</div>
                <div><br>
                </div>
                <div>create foreign table table1</div>
                <div>(</div>
                <div>id int,</div>
                <div>name text</div>
                <div>)<br>
                  SERVER local_file<br>
                  OPTIONS (filename '/outputfile.csv' ,<br>
                  format 'csv',<br>
                  header 'TRUE',<br>
                  delimiter ';' <br>
                  );<br>
                </div>
                <div><br>
                </div>
                <div><br>
                </div>
                <div>create table2 as select * from=C2=A0table1;</div>
                <div><br>
                </div>
                <div>ERROR: =C2=A0invalid byte sequence for encoding "UTF=
8":
                  0x00<br>
                  CONTEXT: =C2=A0COPY table1, line 377617<br>
                </div>
              </div>
            </blockquote>
            <pre cols=3D"72">--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012</pre>
          </div>
        </blockquote>
      </div>
    </blockquote>
    <pre class=3D"moz-signature" cols=3D"72">--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012</pre>
  </body>
</html>

--------------BBC89DE983E7B2DE4176A79D--

--5KyopWFyCJz8NFeeDxqZI2ZVCkyv7OCYn--

--eomtPfR97nd6k4RdeORt62A0KHQyFBQ56
Content-Type: application/pgp-signature; name="OpenPGP_signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="OpenPGP_signature"

-----BEGIN PGP SIGNATURE-----

wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCYN1zkAUDAAAAAAAKCRCKV5yTsxlhumIz
AKCGaWIKZUri4/s4dwqVZcrtGJ1XhQCg2gsLr2bIfEGjdJLAM9C4GlEdek0=
=Iimr
-----END PGP SIGNATURE-----

--eomtPfR97nd6k4RdeORt62A0KHQyFBQ56--