cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ServiceTechs-ME
New Member

Parse JSON file from OneDrive for Business

Hello,

 

I have a workflow where I upload a json file to OneDrive, then I want to extract the information from that file to write an Excel Row on a spreadsheet on OneDrive as well. The flow starts with the creation of the file then I tried to parse the json file using the Data Operations - Parse JSON, by pasing the file content as the input to this step. This approach did not work, it turns out that the output of the trigger which is the file creation, is an application/octet-stream type where the file contents are encoded as a Base64 string. So my output of the first step, the file content, is the following:

 

{ "$content-type": "application/octet-stream", "$content": "ewoJIm1ldGFkYXRhIjogewoJCSJkZXZpY2VfaWQiOiAiQW5kcm9pZF8zNTUwMjUwNjM1ODgzMzciLAoJCSJ1c2VyX2lkIjogbnVsbCwKCQkidXNlcm5hbWUiOiAiQWhpcm8gR3V6bWFuIiwKCQkic3VibWl0dGVkX2F0IjogIjIwMTctMDctMjAgMTU6NDg6MTcgLTA0OjAwIiwKCQkicmVjZWl2ZWRfYXQiOiAiMjAxNy0wNy0yMCAxOTo0ODoyMSArMDA6MDAiLAoJCSJzdWJtaXNzaW9uX2lkIjogIjIwODY4OTEwIiwKCQkiZGV2aWNlX3N1Ym1pc3Npb25faWRlbnRpZmllciI6ICI0M2M1ZmM0Yy1hNDZhLTQzNzctOWY2Zi03MzY2OWUwMjEzNzUiLAoJCSJzdWJtaXNzaW9uX2NvdW50ZXIiOiAiMTMiLAoJCSJmb3JtX25hbWUiOiAiTm9uIENoYXJnZWFibGUgVGltZSBMb2ciLAoJCSJmb3JtX25hbWVzcGFjZSI6ICJodHRwOi8vd3d3LmRldmljZW1hZ2ljLmNvbS94Zm9ybXMvZmZmZTNkMDAtNDRjMC0wMTM1LTNlYTAtMjIwMDBhOTg0Y2ZkIiwKCQkiZm9ybV92ZXJzaW9uIjogIjEuMDciCgl9LAoJImFuc3dlcnMiOiB7CgkJIldvcmtfT3JkZXIiOiB7CgkJCSJ2YWx1ZSI6ICI2NU0xNzA3IgoJCX0sCgkJIlNlZ21lbnQiOiB7CgkJCSJ2YWx1ZSI6ICJBVCIKCQl9LAoJCSJDaGFyZ2VfQ29kZSI6IHsKCQkJInZhbHVlIjogIk1IRSIKCQl9LAoJCSJFbXBsb3llZV9OYW1lIjogewoJCQkidmFsdWUiOiAiSzE2NiIKCQl9LAoJCSJTaGlmdCI6IHsKCQkJInZhbHVlIjogIjEiCgkJfSwKCQkiRGF0ZSI6IHsKCQkJInZhbHVlIjogIjIwMTctMDctMjAiCgkJfSwKCQkiU3RhcnRfVGltZSI6IHsKCQkJInZhbHVlIjogIjE1OjQ3OjAwIgoJCX0sCgkJIlN0b3BfVGltZSI6IHsKCQkJInZhbHVlIjogIjE2OjQ3OjAwIgoJCX0sCgkJIk92ZXJ0aW1lX2luZGljYXRvciI6IHsKCQkJInZhbHVlIjogIjAiCgkJfSwKCQkiU2lnbmF0dXJlIjogewoJCQkidmFsdWUiOiAiMHgwIgoJCX0KCX0KfQ==" }

I know that I have to use a Compose Step to extract the Base64 encoded string and cast that as json using the @json() function after decoding the Base64 string with the @base64ToString() function. My problem is that I cannot find a function or method to extract the Base64 string by calling its "$content" key to pass it to the conversion and the cast functions. I think that the formula in the Compose step would be something like @json(base64ToString(TheMagicExtractFunction(triggerBody(),"$content")))

@json(base64ToString(TheMagicExtractFunction(triggerBody(),"$content"))) 

Smiley Happy or I might be awfully wrong and the approach has to be totally different.

Any help will be most appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
v-micsh-msft
Community Support
Community Support

Hi @ServiceTechs-ME,

 

Not sure if the following would help, which you may give it a try:

1. Add a compose Action,

2. Under Compose Input, copy and paste the following:

"@base64ToString(triggerBody()?['$content']))"

Then run the flow to check if this Compose would return the Base64 content into readable string.

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-micsh-msft
Community Support
Community Support

Hi @ServiceTechs-ME,

 

Not sure if the following would help, which you may give it a try:

1. Add a compose Action,

2. Under Compose Input, copy and paste the following:

"@base64ToString(triggerBody()?['$content']))"

Then run the flow to check if this Compose would return the Base64 content into readable string.

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

 

Managed to make it work with something very similar to what you posted:

 

"@base64ToString(trigger()['outputs']['body']['$content'])"

I guess it's exactly the same thing but different syntax.

 

Thanks for your reply! Smiley Happy

Looking at what the cat brought in when I ran my flow:

//TWpNN0pUSXpPeVV5TXpRfDIwMTktMTAtMTVUMDk6MDc6MzQuMDAwMDAwMCswMDowMA%3d%3d",
"X-AspNet-Version":"4.0.30319","X-Powered-By":"ASP.NET","Content-Length":"1094793",
"Content-Disposition":"attachment; filename=\"70.json\"","Content-Type":"application/octet-stream","Expires":"-1"},
"body":{"$content-type":"application/octet-stream","$content":"eyJUb3BQYXJlbnQiOnsiVHlwZSI//

 I have no clue how you worked out the

trigger()['outputs']['body']['$content']

Nevertheless I conclude that it just works  🙂

 

My attempts were alternating between errors claiming the input was an octet stream and when I tried to apply the base64ToString function,  claiming the same input is an object.   🙂

 

At least now I can have a go at using flow trying to digest a powerapp json file.

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.

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.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Users online (1,935)