Power Automate: How to parse a CSV File to create a JSON array

There are no built in actions in Power Automate to Parse a CSV File. There are external connectors which can do this for you, but this blog post will cover how to Parse a CSV in Power Automate without the use of any external connectors. The aim is to end up with a JSON array that we can use in other actions. I’ve exported this flow and you can download it here.

Here is a video explanation of the process:

For the purposes of this blog post, the sample CSV will have the following contents:

Session Data by SSID,,,,,
SSID,Session Count (%),Client Count (%),Duration (%),Total Usage (%),Usage (In/Out)
test1,90 (54.22%),26 (48.15%),1d:11h:35m (62.06%),939.09MB (50.69%),814.94MB/124.15MB
-,36 (21.69%),13 (24.07%),0d:2h:55m (5.09%),0.00B (0.0%),0.00B/0.00B
test2,21 (12.65%),13 (24.07%),0d:8h:35m (14.97%),538.12MB (29.05%),500.54MB/37.58MB

We are going to transform it into this:

[
  {
    "SSID": "test1",
    "sessionCount": "90 (54.22%)",
    "clientCount": "26 (48.15%)",
    "duration": "1d:11h:35m (62.06%)",
    "totalUsage": "939.09MB (50.69%)",
    "usage": "814.94MB/124.15MB"
  },
  {
    "SSID": "-",
    "sessionCount": "36 (21.69%)",
    "clientCount": "13 (24.07%)",
    "duration": "0d:2h:55m (5.09%)",
    "totalUsage": "0.00B (0.0%)",
    "usage": "0.00B/0.00B"
  },
  {
    "SSID": "test2",
    "sessionCount": "21 (12.65%)",
    "clientCount": "13 (24.07%)",
    "duration": "0d:8h:35m (14.97%)",
    "totalUsage": "538.12MB (29.05%)",
    "usage": "500.54MB/37.58MB"
  }
]

The first thing to note is that the first two lines of this CSV need to be excluded, because they do not contain any data. So lets get started!

Step 1 – Get the CSV Data and Split it into lines

The first thing is to get the CSV data and split it into lines:

This compose action will use the split function to convert the original CSV into an array of lines, it will now look like this:

[
  "Session Data by SSID,,,,,",
  "SSID,Session Count (%),Client Count (%),Duration (%),Total Usage (%),Usage (In/Out)",
  "test1,90 (54.22%),26 (48.15%),1d:11h:35m (62.06%),939.09MB (50.69%),814.94MB/124.15MB",
  "-,36 (21.69%),13 (24.07%),0d:2h:55m (5.09%),0.00B (0.0%),0.00B/0.00B",
  "test2,21 (12.65%),13 (24.07%),0d:8h:35m (14.97%),538.12MB (29.05%),500.54MB/37.58MB",
]

The expression I used was:

split(outputs('Get_file_content_using_path')?['body'], decodeUriComponent('%0A'))

If your file is stored in SharePoint you will use the action “Get file content”, so the expression will be:

split(outputs('Get_file_content')?['body'], decodeUriComponent('%0A'))

This flow uses only compose actions. There is a very good reason for this which I will come to later. There are no variables whatsoever.

Important note regarding line endings

I used the decodeUriComponent function, to split the CSV.

decodeUriComponent('%0A')

This represents a new line feed character (LF), often displayed as \n. This is the Unix standard.

CSV Files generated in Windows, may use this format but often use a carriage return and line feed (CR+LF). This is represented as \r\n.

The split expression above will still work with CR+LF, but you will be left with \r characters in your data. The correct expression to split on a CR+LF is:

decodeUriComponent('%0D%0A')

If you load your CSV file into Notepad you can easily see which format your file is in, in the bottom right hand corner it will show either “Unix (LF)” or “Windows (CR LF)”.

Step 2 – Process each line of the CSV and create JSON record for each line

Now that we have our CSV formatted as an array, we can loop through each line. Take a look at this loop:

In the select an output from previous steps I used an expression, which requires a bit of explanation. The expression is:

skip(outputs('splitNewLine'),2)

The skip function returns an array, but removes items from the beginning of the collection. My sample CSV had two lines at the beginning which I did not want to include. So by using the Skip function, they will not be sent into the apply-each loop.

This loop will process each line individually, so every iteration will be working with a single line.

The first compose action splits the incoming line on a comma, here is the expression:

split(item(), ',')

This will produce yet another array, within the loop, which contains each of the column values, sample from example above:

[
  "test1",
  "90 (54.22%)",
  "26 (48.15%)",
  "1d:11h:35m (62.06%)",
  "939.09MB (50.69%)",
  "814.94MB/124.15MB"
]

The next compose action, called JSON is more interesting. First, manually build a JSON string which represents an empty record, like this:

{
  "SSID": "",
  "sessionCount": "",
  "clientCount": "",
  "duration": "",
  "totalUsage": "",
  "usage": ""
}

Then, within the quotes for each column header you can use an expression to access each element of the splitByComma action:

outputs('splitByComma')?[0]

This expression represents the first element of the array produced by the previous compose step. Arrays in Power Automate are numbered from zero. My complete compose action has this code:

{
  "SSID": "@{outputs('splitByComma')?[0]}",
  "sessionCount": "@{outputs('splitByComma')?[1]}",
  "clientCount": "@{outputs('splitByComma')?[2]}",
  "duration": "@{outputs('splitByComma')?[3]}",
  "totalUsage": "@{outputs('splitByComma')?[4]}",
  "usage": "@{outputs('splitByComma')?[5]}"
}

This is everything you need to include in the loop. You might be wondering why this is of any use as the JSON data has not been added to a variable, so you have no method of accessing it! A secret of apply-each loops is that they output an array of output, so our final compose step has the simple expression:

outputs('JSON')

This will compile all of the results from the compose action within the loop, into a nice array containing JSON objects.

Performance

The flow above will work fine, but you may find it takes a long time to execute with a large CSV file. Because no variables have been used, only compose actions, the concurrency setting of the loop can be adjusted so that it will work on many records simultaneously.

With the three sample rows, the flow took just 1 second to run without concurrency enabled.

I increased the number of rows to 500, and without concurrency it took 1 minutes and 46 seconds to execute.

Enabling concurrency with 30 degrees of parallelism reduced the execution time to 7 seconds! Not a bad improvement.

The Complete Flow

Here is an image of the entire flow, to make your life easier I have exported the flow. You can download it here, import it into your environment and modify to your requirements:

This is a very simple flow, which you can recreate easily. You could add a Parse JSON step after the final compose and then the contents of your CSV will be available as dynamic content in the remainder of your flow.

I hope this helps you to Parse your CSV files, reach out if you need any help!