cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resonance
Level: Power Up

Garbage Characters from JSON E-mail Attachment

Hi All,

 

I recently overcame an annoying challenge and thought I'd share to save anyone else the time of they encounter the same pain.

 

Source data description:

Using Powershell on-premises we generate a PSCustomObject which lists disabled computers , I borrow this report which gets sent out via e-mail , I save the report as JSON using powershell's ConvertTo-JSON cmdlet and action the trigger using the Flag e-mail trigger.

When trying to access this e-mail attachment I encountered some challenges :


1. Images in the e-mail are considered attachments , so I created an IF statement to look for the custom extension [*.JSON] , I used an expression similar to item()?['Name'] to help isolate the name property for the condition.

step1.JPGExample filter for specific attachment

2. Challenge # 2 was that attachment data is encoded , thus I employed an expression to decode the base64 data.

decodeBase64(item()?['ContentBytes'])
 
3. This is where the most annoying challenge arose and the real reason I am sharing this post :
The parsed data included unwanted / unexpected characters , the reason is that default encoding for the file was UNICODE , to solve this , I opted to change the encoding of the source file to UTF-8
**Update** I found that instead of using powershell's OUT-FILE using UTF-8 , I had more success using SET-CONTENT , which by the looks of it uses UTF8NoBOM as per this article:
https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.management/set-content?view=...
[ Figuring that out took me much longer than it should have]
 
Warning & Disclaimer : Be aware of your character constraints in your data , mine has no special characters and is standardized as such.
 
Some could solve this by adding expressions using regex to strip out unicode characters. I'm allergic to regex and tend to avoid when I can.
 
Below is a shot of what the incorrect output would look like.

Error.pngGarbage characters parsing JSON

Pictured below is a successful parse using UTF-8.
Result.JPGSuccessfully Parsed JSON

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Hopefully by sharing this , I save others from the pain.

 

**UPDATE** I found out that UTF8-BOM seemingly adds a header character that MS Flow really doesn't like.

Capture.JPG

 

I used an online JSON Editor which helped me understand why I was getting this error all of a sudden.

cap2.JPGCopied and pasted text from the error to identify the root cause as a header byte which occurs as a result of transporting JSON as a UTF8-BOM file.

1 REPLY 1
Highlighted
Administrator
Administrator

Re: Garbage Characters from JSON E-mail Attachment

This is a brilliant Flow recipe! 👌🏼😁

Happy Flowing! 

 

 

-Gabriel
Microsoft Power Automate Community Manager

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Users Online
Currently online: 340 members 6,563 guests
Please welcome our newest community members: