cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
russellhq
Regular Visitor

Help Cleaning Up Outlook Attachment To Remove Special Characters

I get an xml report emailed to me daily and my flow saves the attachment to a folder in Teams which Excel then uses as a data source. Sometimes the XML contains invalid characters (Hexadecimal 0x01 is an example) and it stops excel from accessing the data.

 

I've tried searching for a way to handle this in Excel but couldn't get a solution, so I figured there might be a way with Power Automate.

 

Is there a way this could be done where the XML file is cleaned up using Power Automate before it's saved to the Teams folder?

1 ACCEPTED SOLUTION

Accepted Solutions

This was fun, and taught me something new (I feel a blog post coming on!). It can be done and it is pretty easy.

 

Check out this image https://ibb.co/PcFvbq5 :

Power-Automate-Remove-Unprintable-Chars.png

 

The code for the "Badchars" array is:

["\u0001", "\u0002", "\u0003", "\u0004", "\u0005", "\u0006", "\u0007", "\u0008", "\u0011", "\u0012", "\u0016", "\u0017" ]

See how you get on.

 

Please...

If I answered your question Accept it as a solution ✔️ 

If I helped you out, please give me some Kudos 👍

Thanks 😙

View solution in original post

9 REPLIES 9
russellhq
Regular Visitor

The characters I need to remove are covered by this regular expression: [\x00-\x08\x0B-\x0C\x0E-\x1F]

This sort of thing is tricky in PA as there is no regular expression support (which is a real pain and I wish it would be added). I was going to ask you to post a sample of the file, but that isn't going to help as the characters will likely be lost.

 

Can you put a sample file on the web somewhere we can download and examine? It does sound like a good challenge.

 

Here's a link to a snipped of the data that has these characters in it

 

Snippet of data 

This was fun, and taught me something new (I feel a blog post coming on!). It can be done and it is pretty easy.

 

Check out this image https://ibb.co/PcFvbq5 :

Power-Automate-Remove-Unprintable-Chars.png

 

The code for the "Badchars" array is:

["\u0001", "\u0002", "\u0003", "\u0004", "\u0005", "\u0006", "\u0007", "\u0008", "\u0011", "\u0012", "\u0016", "\u0017" ]

See how you get on.

 

Please...

If I answered your question Accept it as a solution ✔️ 

If I helped you out, please give me some Kudos 👍

Thanks 😙

View solution in original post

Sorry, the first image I posted isn't completely easy to follow, check out this one:

https://ibb.co/fYpQ2yx

Thanks, that looks really good!

 

Would it be easy to do this for an email attachment? I'd like to be able to clean up the attachment before it's saved to the folder location.

No reason that you couldn’t do it on an email attachment. You’re going to need a condition or two to make sure you’re not just doing it to any attachment. But sure, go for it.

Worked a treat with the email attachment 👌

Well done 👍 Pretty pleased with that solution!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,781)