Recently, a new project started within our company that had something to do with an Exchange migration. One of the deliverables was to send automated mail notifications based on a CSV export of the migration run.

Although the project wasn’t assigned to me, it did trigger me to play around a bit with the CSV file format in Power Automate, since I’ve never really done anything with it. It turned out that it is not quite that straight forward to with with a CSV file in Power Automate, so in this blog series I will explain how to work with CSV files without having to use a third party connector.

This is part 1 of the series which will cover a CSV that will almost never change in the course of your flow lifespan. In part 2, a more dynamic approach will be explained.

CSV basics

Before we start, let’s have a quick look into how a CSV is put together. Basically, it’s a plain text file that separates column with the use of delimiters (usually a comma or semicolon).

Office will automatically open a CSV file in Excel and try to translate it to a table to make it readable to users:

But opening it in a plain text editor (such as Notepad++) reveals the actual structure of the file:

As you can see in Notepad++, this CSV has a textual column setup with a semicolon as separator. Be aware that the separators can differ in various CSV files!

CSV processing in Power Automate

Now that we know the basics, it’s time to let Power Automate process a CSV file. We will use a CSV that is almost identical to the one that is used as an example in the previous chapter: username.csv?media=1751618448.

This CSV has one extra column that we will not use in this example (Additional column), but in part 2 of this blog series.

Loading the CSV into your flow and getting the data

First, we need to make sure that the flow knows which CSV to process.

You can put the CSV somewhere on SharePoint (and maybe let it trigger automatically on file upload) or any other data source that has a Power Automate connector. After that, you need to use a Get File Content action to get the actual contents of the file.

In this example, I will be using the upload function of the Manual trigger:

When triggering the flow, the user gets prompted for a CSV to upload:

But this does not give us the actual textual contents of the CSV we want:

As you can see, the contents have been encoded and cannot be processed (yet).

For this, we need to decode the contents of the file to make it ‘readable’ for our flow. We can use the decodeBase64() expression for this, combined with the File Content of our CSV file:

I’ve put this expression in a Compose action, but you can use a string variable as well:

After this, we are presented with a textual CSV in our flow:

Parsing the data so it can be actually used

Now that we have a textual CSV, we can parse the contents and use the data.

For this, we first need to convert the string to an array, making sure each new line from the string is a new row in the array.

This can be done by splitting the contents on a newline character. Unfortunately, in a plain text configuration like this, this is not possible.

We can however convert the string to a URI component, which is used for URLs. The string itself will remain the same, but special characters (like ENTER, comma, slash, etc.) will be converted to its corresponding URI component. To do this, we can leverage the uriComponent() expression.

With this, we have the URI encoded contents which we need to split. We are looking for the newline character, which is ‘%0A’. Splitting can be done using the split() expression.

If we put all this together, we will get the following expression:

split(
	uriComponent(
		outputs('Compose_|_CSV_contents_(decodeBase64)')
	),
	'%0A'
)

The uriComponent() expression will convert the special characters in URI components. The split() expression will transform the string into an array, making sure each newline (‘%0A’) will be put on a new row:

Again; I’ve put this into a Compose action, but you can use an array variable for this as well.

Making sure the columns are identified and filled

As you can see in the screenshot above, we’re not quite there yet. We do have an array of items, but we still need to identify the columns and fill the data into those columns.

Excluding the headers

First, we need to make sure to exclude the header row of the CSV, since we will not be using it (we will identify the columns manually). To do this, we can use the skip() expression.

With this expression, you can configure how many rows of your array you want to be excluded. Since the header row is the first row of our array, we will be skipping this first row:

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

If we look into our flow run, we will see that the first row is now excluded from our array:

Mapping the columns

The next step is to break down these rows into actual columns. We will use the Select action for this, which allows us to pick elements from the header less array action (see above) and assign them a property.

But before we jump into that, some further explanation is required. As you can see in the screenshot above, the rows are still in URI format. This hasn’t been encoded before, because we still need the URI for splitting the rows into column.

As mentioned above, the CSV parameter of our file is a semicolon . The URI component for a semicolon is ‘%3B’. We can use the split() expression again to split on this URI component for each column we want to select. This can be configured as follows:

split(
	item(),
	'%3B'
)

If we use the split() expression on the row, an array will be returned which contains all columns for that row. That’s where array indexing and the skip() and first() expressions come in handy. With array indexing, you can select a specific element from an array:

The skip() expression has already been addressed above.

The first() expression does the same as [0] array indexing but for some reason, it seems that Power Automate doesn’t let you use array indexing in combination with the skip() expression. That’s why we will use the first() expression as well

Putting it to the test

There are five columns in our CSV:

• Username, which is de first column.
  Since this is the first column, we can extract this column using array indexing (this is the first element, so we can use [0]):

  split(
  	item(),
  	'%3B'
  )[0]

• Identifier, which is the second column.
  We need to make sure to exclude the Username column by using the skip() expression and skipping 1 element. This is where the array indexing doesn’t get accepted anymore, so we need the first() expression:

  first(
  	skip(
  		split(
  			item(),
  			'%3B'
  		),
  		1
  	)
  )

• First name, which is de third column.
  The same logic as the Identifier column applies here. The only difference lies into the amount of elements we need to skip. For this column, we need to skip 2 elements:

  first(
  	skip(
  		split(
  			item(),
  			'%3B'
  		),
  		2
  	)
  )

• Last name, which is the fourth column.
  The same logic as the Identifier column applies here. The only difference lies into the amount of elements we need to skip. For this column, we need to skip 3 elements:

  first(
  	skip(
  		split(
  			item(),
  			'%3B'
  		),
  		3
  	)
  )

• Additional column, which is the fifth and last column.
  In this example, we will not be using this column.

This will result in the following configured Select action:

When we run the flow, we can see that it has mapped every column and value from our CSV, without the Additonal column property: