Accent errors - CSV files - UTF - 8

Like (0) Share

Report

Posted on 21 Mar 2022 19:42:14 by pmota

Hello,

I am having a hard time copying a CSV file from email to one drive. The file contains words with accents. For example: "Québec". If I just use a 'base64tostring' function I get the result "Quï¿½bec".

I tried to convert to UTF -8 format by using the expression

"concat(uriComponentToString('%EF%BB%BF'),base64ToString(items('Apply_to_each')?['contentBytes']))". After that I got the word "Qu�bec".

Can anybody help me with that?

Thanks

Paulo

All responses (13)

Answers (1)

Verified answer

pmota 55 on 31 Mar 2022 at 20:01:51

Like (1)

Report

Re: Accent errors - CSV files - UTF - 8

Hi @Expiscornovus ,

I think I finally got the solution. I used the "base64tobinary" function without the 'BOM' expression.

Again, thanks and have a great day!
Expiscornovus 31,641 Most Valuable Professional on 24 Mar 2022 at 08:53:03

Like (0)

Report

Re: Accent errors - CSV files - UTF - 8

Hi @pmota,

It might be possible to convert the csv via a custom connector or an azure function, both are code solutions.

Alternatively, you could use a desktop flow an run for example a script with the Set-Content cmdlet. That cmdlet has an -Encoding parameter
pmota 55 on 23 Mar 2022 at 18:26:33

Like (0)

Report

Re: Accent errors - CSV files - UTF - 8

Hi @Expiscornovus,

You are right. lol.

Thanks for the heads up. I removed that as the solution for the post. 😞
Expiscornovus 31,641 Most Valuable Professional on 23 Mar 2022 at 18:06:26

Like (1)

Report

Re: Accent errors - CSV files - UTF - 8

Hi @pmota,

Just to double check. The diamond character is a generic replacement character.

By using that replace function you are also using the acute accent for other characters it is unable to encode. Just wanted to make you aware of that 😉
pmota 55 on 23 Mar 2022 at 17:53:42

Like (0)

Report

Re: Accent errors - CSV files - UTF - 8

Hi @Expiscornovus ,

Apparently, I could find a workaround by using the function "replace".

Thank you so much for your help. Much appreciated.

Have a great day!

Paulo
pmota 55 on 22 Mar 2022 at 23:03:49

Like (0)

Report

Re: Accent errors - CSV files - UTF - 8

Hi @Expiscornovus ,

Woooow. It worked. Awesome. We're getting there.

However, I receive the file from an internal system. How can I convert this file (CSV) in a CSV UTF-8 using power automate?

I already tried to use "concat(Uri..." and decoding after using base64tostring. However, it didn't work. The flow returned error.

Thanks again.
Expiscornovus 31,641 Most Valuable Professional on 22 Mar 2022 at 22:38:20

Like (0)

Report

Re: Accent errors - CSV files - UTF - 8

Hi @pmota,

Can you create a test file in Excel, save it as CSV UTF-8 (comma delimited) and test it with that type of file?
pmota 55 on 22 Mar 2022 at 22:16:40

Like (0)

Report

Re: Accent errors - CSV files - UTF - 8

Hi @Expiscornovus ,

Thanks for sharing the link. That's exactly what's happening. However, I don't know how to solve it (or even if there is a solution).

The original file (Test.csv) is the one I created using excel and it's the one I sent to my email (I saved it as CSV (comma delimited)(*.csv)). If I open it using notepad, it is in the ANSI format. As you can see below, I have no issues with that.

After running the flow, I am getting two different outcomes in notepad and excel (test2.csv). Note that is the same file. I just opened it in two different tools (excel and notepad):

Lastly, if I add the "concat(Uri..." expression in the code, the only difference in the excel file outcome will be the replacement of "ï¿½" with the �.

The issue happens during the file decoding in Power Automate (base64toString function). 😞

If you have any additional suggestions, please let me know.

Thank you,

Paulo
Expiscornovus 31,641 Most Valuable Professional on 22 Mar 2022 at 20:29:24

Like (1)

Report

Re: Accent errors - CSV files - UTF - 8

Hi @pmota,

Even if you have created it with Excel you should still be able to use 'open with' and select Notepad. Worth double checking if it shows UTF-8 or something else (ANSI) in the right bottom corner of Notepad.

Btw, I have looked into ¿½ (diamond character). This article might be a good read-up on the cause of showing that replacement character:

https://stackoverflow.com/questions/6366912/reading-file-from-windows-and-linux-yields-different-results-character-encoding/6367675#6367675

What have you used when you created the file saved it as csv in Excel? There are several csv options 😁

Based on the fact it is still showing a replacement character my guess would be that you save the as a CSV (comma delimited) without UTF-8.
pmota 55 on 22 Mar 2022 at 18:17:40

Like (0)

Report

Re: Accent errors - CSV files - UTF - 8

Hey Dennis @Expiscornovus ,

Thanks for sharing this. The only difference I see between our scenarios is the fact you created your csv file using the notepad.

If I am not asking too much, could you please test it out using a file created in excel? I receive a csv report on a daily basis from the system, so I have no power to create that in the notepad.

I ran a test using your data (in an excel file), and I got the same issue:

Thanks again,

Paulo