topic Re: ERROR: invalid byte sequence for encoding "UTF8": 0x00 in Talend Studio

ERROR: invalid byte sequence for encoding "UTF8": 0x00

dwhdeveloper — Wed, 05 Apr 2017 16:52:15 GMT

I received the following error when insert string column from mysql to Postgres.
Some of the rows were fixed when I did trim(both CHAR(0x00) from text) in my sql.
But I am still getting this error for some other rows.
Maybe it has invalid characters in the middle.
How do I get rid of them?

Re: ERROR: invalid byte sequence for encoding "UTF8": 0x00

Anonymous — Fri, 07 Apr 2017 03:43:43 GMT

Hi,
Postgesql does not supports zero in characters.
Could you please take a look at this post:http://stackoverflow.com/questions/1347646/postgres-error-on-insert-error-invalid-byte-sequence-for-encoding-utf8-0x0?
You can use tMap to replace " \\x00" in expression.
Best regards
Sabrina

Re: ERROR: invalid byte sequence for encoding "UTF8": 0x00

vaibs_parab — Wed, 25 Apr 2018 17:11:59 GMT

I had also faced the same issue recently. As I'm working with PostgreSQL database while inserting the data from Excel (.xlsx) file.

The PostgreSQL does not support zero in characters or 0x00 (unlike Oracle). You have to replace such chars with space or anything else.

In order to do so, you have to implement an expression in tMap to handle such situations.

Use below expression:

row1.your_column_name.replace("\\x00", " ")

For reference, follow below link:

http://stackoverflow.com/questions/1347646/postgres-error-on-insert-error-invalid-byte-sequence-for-encoding-utf8-0x0

This work for me. Give it a try.

Regards

Re: ERROR: invalid byte sequence for encoding "UTF8": 0x00

raghu780 — Tue, 10 Sep 2019 17:21:28 GMT

How can we handle the same issue in Talend data Mapper.

I used below function while loading the data into PostgreSQL database.

i've uploaded the regex and replace string.

Thanks,

Raghu.

Screen Shot 2019-09-10 at 11.19.39 AM.png