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
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: %0D%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: