Microsoft Flow (advanced) tutorial : creating a CSV to JSON converter from scratch

a couple of days ago, one of my friend challenged a group of “flowers” to come up with a  Flow that converts a CSV file to whatever you want, in this specific case to JSON objects that can easily be treated by Microsoft flow.

Even tough flow can do it, we should rely on more performant tools like SQL Server Integration services or PowerQuery to achieve this kind of data manipulation. Currently the PowerQuery action in flow can only be used to transform data coming from SQL Sever, but I have no doubt that it will extended to other data sources (like a csv file among many others).

Just fr the fun, I jumped on my keyboard and came up with the following solution that we will build from scratch.

Basically we must transform a list of record that look like this :

company,country
Microsoft,usa
BMW,Germany
Samsung,Korea
Mazda,Japan

into a list of JSOn objects that look like this:

[
  {
    “company”: “Microsoft”,
    “country”: “usa”
  },
  {
    “company”: “BMW”,
    “country”: “Germany”
  },
  {
    “company”: “Samsung”,
    “country”: “Korea”
  },
  {
    “company”: “Mazda”,
    “country”: “Japan”
  }
]

 

The key point is that in logic apps/flow we have the following  logic apps function that can add a property to a JSON object, so we can dynamically create JSON objects:

addProperty(…)

More details here :

 

You can easily test this by creating a small test flow with an empty object variable and a compose action that adds a property ‘Company’ with the value ‘Microsoft’:

When you run this flow, you will get the following result :

 

Easy so far. Now let’s apply this concept to a set of existing data that should be stored a a CSV (Comma separated value).

I’ve create the following Excel data that I’ve saved as a csv :

Open this file with notepad and copy & paste the value in a Compose action in your flow. Name the Compose action ‘CSV Data‘ (and remove the previous Compose action) :

The first row (the header) of this set of data plays an important role because it will provide you the property definition of your JSON objects.

The algorithm is quite simple :

1. Split the data in different lines by using the linefeed delimiter
2. Save the first row somewhere
3. Take all other rows and for each row create a new JSON object with the corresponding property grabbed from the header: so here we need to take each header column and use it as a property (in our case, the properties will be ‘company’ and ‘country’. But it must be retrieved dynamically as well.
4. Add each row into an array
5. Display the array as an HTML table

Step 1.Split the data in different lines by using the line feed delimiter

there is currently an issue in the flow editor when we use the linefeed character, so we have to explicitly type the logic app code. Add a Compose action, name it Table and add the following code in the input (not in the expression, and keep the double quotes):

“@split(outputs(‘CSV_Data’), ‘\n’)”

 

Save an run the flow; the output should display an array of strings :

Step 2.Save the first row (the header) somewhere

• Add a variable action,
• name it Header.
• Its type must be array.
• Its expression should be:

split(outputs(‘Table’)[0],’,’)

In a nutshell we grab the first row of the array of data and we store each column in an array:

Test the flow and make sure the column are stored in the array :

Step 3. Create a JSON object for each row and add it into an array of objects

• Let’s create an array of object by adding a variable of type Array and name it JSONArray:

• Add an apply to each that will go through every row (except the first one) and that will create a JSON object for each row. There is a basic function that allows to skip items in a list : the skip(…) function :

skip() is very useful; in a previous version of my flow I did define an index variable that I incremented in the loop and I tested if the variable was 0 (the header) or not (the data).

• Test you flow, you should only have 4 iterations in this loop.
• Now let’s create our JSON object. We will have to dynamically generate the JSON properties, this means a loop in the loop.

But before doing that, let’s make sure we are able to create 1 single JSON object:

• Let’s add a (test) Apply to each before the last one; we will loop through the value of the Header table (we will move this loop into the previous loop afterwards, we just need to make sure it works ok, because debugging loops in loops is a bit tricky):

• In this new Apply to each add a new Compose action where we will invoke the addProperty function :

this creates an object that we will store in our variable JSONObject :

• add a set variable action to store the new generated property into our JSONObject :

After testing our flow we see that both properties are well defined in our JSON object:

• Move this loop into the other loop:

In the outer loop, we need to store the current Table row (like ‘Microsoft,usa’):

• Create a Compose object and name it CurrentTableRow. Its expression should be: item()
• In the same outer loop, add a Set variable action that will clean up every JSON object before adding the set of properties :

• And in the Inner loop we need to replace our ‘testvalue’ with real values fetched from CurrentTableRow :

the expression we did create before was

addProperty(variables(‘JSONObject’), item(), ‘testvalue’)

–> so now we need to replace ‘testvalue’.

In our example, the value could be ‘Microsoft’ or ‘usa’, based on the index of the Inner Apply to Each.

There are different way to solve this. One way is to define an index variable that we will increment in the inner loop and we will split the CurrentTableRow.

• In the outer loop let’s define a variable IndexProperty of type Integer and  set its value to 0 in the inner loop:

• In inner loop, let’s add a new Compose action that will grab the property value like ‘Microsoft’, or ‘usa’ ; its expression should be:

 

split(outputs(‘CurrentTableRow’), ‘,’)[int(variables(‘indexProperty’))]

 

• Update the AddJSONProperties expression :

The expression should now become :

addProperty(variables(‘JSONObject’), item(), outputs(‘PropertyValue’))

• Also don’t forget to increment the indexProperty variable as illustrated in the previous picture.

• In the outer loop, we need to add the JSONObject into the JSONArray and to create an HTML table that will display the flow output:

 

• Run the flow and check the output of the flow : we have pure JSON and pure HTML :

 

You can extend the flow by fetching data stored in a file store for instance in OneDrive for Business

I will also update this post in the future because it takes 40 minutes to parse a 950 rows/3 column csv file.

 

If you are interested, I can train your team in PowerApps and Flow anywhere in the world. My workshops are fully exercises oriented, 0 slide.

Check my web site : https://www.shareql.com