Magic Efficient – CSV to JSON

Are you looking to parse any CSV to JSON without having to worry about the file encoding, the header names, or the length and shape of the CSV? I have a new one solution fits all method here (May 2021) which I encourage you to try. The method I propose below is off the back of my demo of the Union, Except, Intersect actions in Power Automate where I learned something new. How to use the Select Action. How about using this action to create an efficient method for converting CSV to JSON?

I then began to look at where else I could use this useful action to improve upon efficiency of other processes and stumbled across a fantastic video by Paul Murana where he parses a CSV into a JSON Array. There are many other examples online where the Apply to Each action was used to build the final JSON, to which I questioned myself, can we use a select action here and the answer is YES!

So how do I go about achieving this?

Let me set the scene, I am at the stage of the Cloud Flow where the CSV has been loaded into a Compose Action as an Array. Prior to this stage I have loaded a CSV with get file content, decoded the file from base64 to a string, encoded as URI in order to replace the newline (%0D%0A) with a semi colon (;) and then decoded back to a string. I then split the string by the semi colon and skip the header line to be left with the following Array of CSV Rows (details of which are all at the bottom of the post).

The next step is the magic. Super efficient No Apply to Each Json. I use the select statement to take the output from the above NEW CSV Compose action and split each item and call upon the array elements by index, all within the same action.

The output of the select statement looks like follows:

The alternative method is to use an apply to each as follows, with a final compose action outside of the apply to each action to compose the results of the JSON Array.

The sample CSV that I loaded contained 23 records. The select action example took 0 seconds to complete where the apply to each took 6 seconds.

How was the rest of the Cloud Flow Built?

Do you have 6 seconds to spare? Will you try this on your next flow? Let me know in the comments below and get in touch if you have any questions?

Want to know another nice trick using select? Read my article on Union, Except and Intersect (click here) without the need to use an apply to each.

Tags: Apply to EachCSV to JSONEfficiencyParse a CSV FilePower Automate