Quick Guide on processing CSVs with Power Automate
A way to address common problem in PA
Declare trigger ‘when file is created’ pointing to the location of csv (comma separated values).
Then select ‘get file content’. In pace of File Identifier field put Identifier from the trigger (dynamic content can be selected)
You are going to need three variables: Line, return and tab.
As value of Line generate new line character by simply pressing enter.
Return should have:
decodeUriComponent('%0D') For tab you can copy from below (yes appears empty, but it has tab value which you can grab), and paste it into ‘value’ field. I know a bit weird, it is because /n , /r , /t values which normally encode new line, return, tab are not seen by PA variables.
Alternatively, grab tab value from anywhere else.
Once we have variables, declare Compose
with following expression:
split( variables('csv_string'), variables('Line'))It will split your file by the lines like this:
Declare three variables, one of Integer type- set it to 0 and
the second of String type - called SplitValue
and the third of Array Type:
In general terms here is what we will be doing: Traversing through each row, as we go down we will be extracting current line, dividing it into row values. Each row value can be accessed at the time when line is processed by array index corresponding to column number. With index number we can control what is skipped and what is processed. For example, we would want to ignore columns.
Declare ‘Apply to Each’ and use Output of Compose from above
Inside of a loop, as we traverse each line we will be incrementing Index by 1, so use Increment Variable action
Then, use Set Variable on SplitValue with following expression
replace(items('Apply_to_each'), '\t',',')Expression will get you current line:
Still, inside of a loop declare Condition:
As we traverse csv down and knowing, that first row is a column we can skip it by setting appropriate condition:
In my case I have to do something very specific at line 2 (and only line two), but you can use ‘is equal or grater than' to process all rows. Obviously, in both cases headers are skipped.
Set Array value to
split(replace(replace(items('Apply_to_each'),
variables('tab'),','),variables('return'),','),',')This will divide rows further into values which can be accessed with appropriate column index.
For example, value under FamilyName column would be accessed in following fashion
variables('Ar1')[2]Just as an example. I have got another condition:
SplitLength is variable I have not mentioned earlier. It is declared just after creating first Compose (the one outside of the loop):
length(outputs('Compose'))Together with the second part of condition above, the processing will stop just before last line, which in my case was extra empty row not present in passed csv. My way of dealing with this is just skipping this extra line. You might want to remove it before starting to process csv.