Extract CSV data in Power Automate

This post describes how to read a CSV file from Azure storage and parse it into an array using Power Automate.

Azure File Share

Consider a CSV file that has been FTP’d to an Azure file share

The contents of the CSV file is as follows

Power Automate

The Power Automate flow which processes the CSV file is as follows:

Referring to the second action ‘Initialize variable – Each row’. This defines the array that the CSV data will be placed into

The third action ‘List files – in Azure storage FTP folder’. This specifies the Azure File Share to retrieve the file from

The fourth reads the contents of the CSV file and places the contents into an array.

Note: %OD%0A’ represents carriage return (\r) and new line (line feed) (\n). These exist at the end of each line in the samplefile.csv. The ‘split’ function splits the input string when it finds ‘%0D%0A’ and also removes ‘%0D%0A’ from the resulting output strings

The following is an example of the results of the flow being run:

The following action places each CSV row into an array

Note: The delimiters have been removed. If only the line feed was removed (i.e. by using decodeUriComponent(‘%0A’)), it would look like this

The next logical step would be to convert this array into a JSON string and then use the ‘Parse JSON’ action. This would then allow the flow to be able to reference each value in the CSV file. For an explanation of how to do this, I would recommend the following blog posts:

Power Automate: How to parse a CSV file – Manuel T. Gomes

Power Automate: Parse CSV files