3 weeks ago, I’ve published part 1 of the CSV blog series in which a static CSV approach was handled. This week, I’ll be showing you how to handle a more dynamic approach for CSV files that don’t have a predefined set of columns.

Introduction

For this blog post, I’ll be using the same CSV file as we used in part 1. Instead of manually defining the columns, we will be automatically map it. For this, we need to do the following:

1. Determine the delimiter automatically
2. Determine all columns automatically
3. Matching columns with their values

We will use the steps from part 1 up until the Compose | CSV to array (split on uriComponent ENTER character) so that we already have an array with the CSV components.

Determine the delimiter character

The first thing we need to do in order to process our CSV successful is to find out what the delimiter of the CSV file is. For this, we need to pick the decoded CSV file (see part 1 – Compose | CSV contents (decodeBase64) action) and find the delimiter.

As mentioned in part 1, the delimiter will be a URI component. So it will start with ‘%’, followed by the URI component code (e.g. %3B for a semicolon).

In order for us to determine the delimiter automatically, we need to find the first ‘%’ symbol in the decoded CSV file. If we split on that character, pick the second attribute (using the [1] index) and append it with the ‘%’ prefix, we get the URI component for our delimiter.

After that, we can decode the URI component to get the final delimiter. The expression for this is:

decodeUriComponent(
	concat(
		'%',
		split(
			uriComponent(
				outputs('Compose_|_CSV_contents_(decodeBase64)')
			),
			'%'
		)[1]
	)
)

This will give us the delimiter we need (in this case: a semicolon):

Determine the columns

Now that we know the delimiter, we can proceed by identifying the columns in our CSV file. For this, we need to look at the header row of the array with CSV components, which we excluded in part 1.

We can do this by picking the first element of the array (by using the [0] index).

After that, we can identify the columns in that row by splitting on the delimiter we found. Since we showed the delimiter as decoded URI component, we need to encode it again in this step.

The full expression for this is:

split(
	outputs('Compose_|_CSV_to_array_(split_on_uriComponent_ENTER_character)')[0],
	encodeUriComponent(
		outputs('Compose_|_Delimiter')
	)
)

This will give us a new array with all columns. You can see that the ‘Additional column’ column – that we (deliberately) didn’t use in part 1 – is also shown:

Matching the columns and its values

Now that we have all column identified, it’s time to match the columns with the values from the CSV. We need to follow a few steps for this:

1. Initialize variables
2. Identify the data rows and make sure each row is filled with data
3. Matching column and value based on their order number

Initialize variables

We will use a double Apply to each set-up for matching columns to its values. For this, we need to use the following variables:

• intHeaderIndex
  This is an integer variable with an initial value of 0 and will be used to identify the current header and its value
• objRow
  This is an object variable (with an initial value of {}, which is an empty object) and will be used to store each row column and its value within a single Do Until loop
• arrCSV
  This is an array variable (without an initial value) and will be used to store all row columns and their values

Identify the data rows and make sure each row is filled with data

Just like in part 1, we need to identify all data rows by using the skip() expression:

skip(
	outputs('Compose_|_CSV_to_array_(split_on_uriComponent_ENTER_character)'),
	1
)

This will give us all data rows, but in part 1 we had an empty data row at the end of our array. In order for us to filter any empty data rows out, we can use the Filter action and check if the row length (which is still a string since we haven’t mapped anything this) is greater than 0:

As you can see, the empty data row is now gone:

Matching column and value based on their order number

Now it’s time to put it all together and do some complex matching based on the steps we did above. The matching will follow the following set-up:

1. We will loop through each filled data row (using an Apply to each based on the filled data rows)
2. Within that Apply to each, we will loop through through each column (using another Apply to each based on the columns array)
3. Within that second Apply to each, we will pick the column and its corresponding value (using the intHeaderIndex variable) and store it into the objRow variable
4. When the entire data row is processed, the columns and its corresponding values will be stored in the arrCSV variable

Loop through each filled data row

In order for us to loop through each filled data row, we can add a Apply to each and pick the outcome of the Filter action:

Loop though each column

While we are looping through a filled data row, we need to process the columns of that data row. That’s why we need another Apply to each and pick the outcome of the action that filtered the columns out out the CSV array:

Match the column with its value

We are now processing each column within each data row. Eventually, this needs to be translated into an array like so:

[
  {
    "column1": "value1",
    "column2": "value2"
  },
  {
    "column1": "value1",
    "column2": "value2"
  }
]

Basically, each column is a new object property of the objRow variable. We need to add that column as a property to the objRow variable. This can be done by using the addProperty() expression. Unfortunately, you cannot reference a variable itself when setting a variable, so this needs to be configured in two steps.

First we need to add the property to the definition of the objRow object variable without storing it into the variable. This can be done using a Compose action. Within this action, we use the addProperty() expression as mentioned above. This expression needs the following input:

1. The object to add the property to, which is our objRow variable.
2. The property name, which is the column name from the current Apply to each loop. Since this still might have some URI components (like a space character), we need to make sure to decode it first:

   decodeUriComponent(
   	items('Apply_to_each_|_Header')
   )

3. The property value, which is the corresponding column value from the filtered data rows. The logic of stripping the value from the data row the same as in part 1. The only difference that we don’t know the array index in which the value exists. This is where the intHeaderIndex variable comes in place:

   decodeUriComponent(
   	first(
   		skip(
   			split(
   				items('Apply_to_each_|_Data_row'),
   				encodeUriComponent(
   					outputs('Compose_|_Delimiter')
   				)
   			),
   			variables('intHeaderIndex')
   		)
   	)
   )

   The intHeaderIndex variable has been initialized with a value of 0, meaning it will skip the first 0 rows. With the first() expression in place, it means it will pick the first value of the split data row (by the delimited we defined earlier). When the intHeaderIndex variable increases (see below), it will pick the next column value.

When all the input has been provided to the addProperty() expression, our Compose action should look like this:

Now we can write this into our objObject variable by using a Set variable action and putting the output of the Compose action above as value.

To make sure the next column value will be matched correctly as well, we also need to increment our intHeaderIndex variable by 1.

Store the columns and its corresponding values

Now that we have a complete set of columns and values, we need to make sure we can fill the arrCSV array variable with the results.

To do this, we need to make sure that we add the objRow object variable to our arrCSV array variable after the column Apply to each loop. We can use the Append to array variable action for this.

After that, we need to make sure the column matching starts over for the new row. That’s why we need to clear out the objRow object variable by setting it back to an empty object: {}. And also the intHeaderIndex variable needs to be reset to 0.

Result

With all of the above in place, we can now run the flow and see that the arrCSV variable has the complete CSV definition:

The flow should look like this. Make sure that each action is put into the correct Apply to each loop! Keep in mind that some of the actions from part 1 are not in this overview.

Final thoughts

Since you probably need to process the CSV into another system (Dataverse, SharePoint, etc.), you still need to know which columns are available in your flow editor. This solution does not provide the editor with the actual column names; only the flow run will show the results. Besides that, this solution is significantly slower than the static approach from part 1 (<1 second vs > 20 seconds):

That’s why you will probably use the solution described in part 1 of this series, but I found it interesting on how I can process a single CSV file without any need for human input.

It also contains a lot of logic that you might want to use in other flows (together or separately) and that’s why I still wanted to share this with you.