cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pmota
Frequent Visitor

Accent errors - CSV files - UTF - 8

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
1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Expiscornovus ,

 

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

 

pmota_0-1648756778345.png

 

Again, thanks and have a great day! 

 

View solution in original post

13 REPLIES 13
Expiscornovus
Super User
Super User

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?

 

byteordermark.png

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: 

 

pmota_1-1647959438963.png

 

Flow:

 

pmota_0-1647958882251.png

 

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

 

Much appreciated! 

 

Paulo 

Expiscornovus
Super User
Super User

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

 

 

example_csv.png

 

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

 

cities_outlookofficecom_mail.png

 

3. I used these settings for the trigger action

 

triggeractionsettings.png

pmota
Frequent Visitor

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:

 

pmota_0-1647972810419.png

 

Thanks again,

 

Paulo

 

 

 

Expiscornovus
Super User
Super User

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-res...

 

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.

 

which_csv_type.png

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.  

 

pmota_0-1647985564307.png

 

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):

 

pmota_1-1647985983840.png

 

pmota_2-1647986048721.png

 

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
Super User
Super User

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
Frequent Visitor

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. 

pmota
Frequent Visitor

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_0-1648057871140.png

 

Expiscornovus
Super User
Super User

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 😉

Hi @Expiscornovus,

 

You are right. lol.

 

Thanks for the heads up. I removed that as the solution for the post. 😞

 

 

 

 

Expiscornovus
Super User
Super User

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

Hi @Expiscornovus ,

 

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

 

pmota_0-1648756778345.png

 

Again, thanks and have a great day! 

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (1,907)