Answered

Accent errors - CSV files - UTF - 8

(0) Share

Report

Posted on 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

Categories:

General topics

I have the same question (0)

All responses (13)

Answers (1)

Expiscornovus 33,194 Most Valuable Professional on at

Like (0)

Report

Hi @pmota,

Just tested it with the setup below. That seems to give me the correct output.

Can you try that and see if this approach makes any difference for you?

Was this reply helpful? Yes No
pmota 55 on at

Like (0)

Report

Hi @Expiscornovus ,

Thank you so much for looking at this. Unfortunately, the solution didn't work for me.

I created a mockup file with two rows:

Québec Inc.
Alvéole Montréal inc.

And I got the following results:

Flow:

Do you have any clues why I am not getting the expected result even after running the UTF-8 command?

Much appreciated!

Paulo

Was this reply helpful? Yes No
Expiscornovus 33,194 Most Valuable Professional on at

Like (1)

Report
Hi @pmota,

Maybe it helps if I share more details about my test. This way we can figure out what is different in your setup/scenario.

1. First of all I used this csv as an example. Created in Windows Notepad.

City,Country Québec,Canada Seattle,United States Paris,France Berlin,Germany

2. I e-mailed it via the browser, https://outlook.office.com/mail as an attachment (to myself 😁)

3. I used these settings for the trigger action

Was this reply helpful? Yes No
pmota 55 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Expiscornovus 33,194 Most Valuable Professional on at

Like (1)

Report

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.

Was this reply helpful? Yes No
pmota 55 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Expiscornovus 33,194 Most Valuable Professional on at

Like (0)

Report

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?

Was this reply helpful? Yes No
pmota 55 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
pmota 55 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Expiscornovus 33,194 Most Valuable Professional on at

Like (1)

Report

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 😉

Was this reply helpful? Yes No