In this post I will give a pattern for reading a csv file form SharePoint.
An example csv file
For this post I created a csv file with 3 columns and two rows of data.
I uploaded this csv file to SharePoint, ready to be read by my Flow.
The flow to read csv files
After I built my flow I ended up with a json object in my flow that contained all the information from my csv file.
Only 3 steps needed to complete this task.
How to read a csv file from SharePoint.
This is the easy part. The get file content action will do this for you.
See there, we have $content! Ready to be converted to json.
Convert the CSV file to json
Ok, this is the tricky part.
The Filter Array action does two things. The obvious bit is that I want to filter out the empty lines as there is no need to process these.
The the From setting is where I’m turning the $content into an array of data that can be filtered.
json( uriComponentToString( replace( replace( uriComponent( split( replace( base64ToString( body('Get_file_content')?['$content'] ), ',',';'), outputs('Newline') ) ), '%5Cr', ''), '%EF%BB%BF', '') ) )
That expression doesn’t need any explanation, as it is as clear as mud!
The base64ToString function turns the weird looking characters in $content into something that already looks like the data in my csv.
base64ToString( body('Get_file_content')?['$content'] )
Then the replace around that replaces my commas with semi colons. The reason why I’m doing this is because the commas may cause some problems within my csv data.
replace( base64ToString( body('Get_file_content')?['$content'] ), ',',';'),
Then in the rest of the large expression I’m removing some of the junk that excel adds to csv files. (‘%EF%BB%BF’ and ‘%5Cr’)
You will also have noticed the outputs(‘Newline’). This is referring to a compose action in which I just pressed enter.
The final step
The final step of the flow has again 2 reasons.
- The first line of csv files contains headers rather than data and should be ignored.
- turn the column names into json labels.
In one of my favourite actions in Flow, the select action, I will use to skip the headers, the following expression:
Ok, that was easy!
split(item(),';') split(item(),';') split(item(),';')
If you are interested in a single step then you could also up vote the idea on the forum. Although when only 3 steps are required to read rows form a CSV file, there will hardly be a benefit 😉