Parse JSON file from OneDrive for Business

[ServiceTechs-ME]

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

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

Any help will be most appreciated!

[v-micsh-msft · Accepted solution]

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

[ServiceTechs-ME]

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! 

[MichelH]

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.