cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ServiceTechs-ME
Level: Power Up

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
Community Support Team
Community Support Team

Re: Parse JSON file from OneDrive for Business

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
Community Support Team
Community Support Team

Re: Parse JSON file from OneDrive for Business

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

ServiceTechs-ME
Level: Power Up

Re: Parse JSON file from OneDrive for Business

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

MichelH
Level 8

Re: Parse JSON file from OneDrive for Business

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
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!

Top Kudoed Authors
Users Online
Currently online: 277 members 5,241 guests
Please welcome our newest community members: