Introduction

This post describes an example of how to read a CSV file from an Azure file share and parse the contents of the file into an array in Power Automate

Technical Solution

Azure File Share

Consider a CSV file (Figure 1) which has been FTP’d to an Azure file share (Figure 2)

Power Automate

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

Referring to Figure 3, the second action is ‘Initialize variable – Each row’. The expanded form of this is displayed in Figure 4. This action defines the array that the CSV data will be placed into

Referring to Figure 3, the third action is ‘List files – in Azure storage FTP folder’. The expanded form of this is displayed in Figure 5. This specifies the Azure File Share to retrieve the file from

Referring to Figure 3, the fourth action is ‘Apply to each – Each spreadsheet in the Azure storage FTP folder’. The expanded form of this is displayed in Figure 6. This 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. Figure 7 displays the results of this

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

Although not described in this post, 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.

Further reading

Import xls data into a canvas app

References

https://learn.microsoft.com/en-us/power-automate/create-variable-store-values?tabs=classic-designer