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