3 steps to read csv files from SharePoint

3 steps to read csv files from SharePoint

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.

Example CSV file

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.

3 steps to read csv files from SharePoint

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.

$content as reeturned by read content action

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.

Filter Array to clean the CSV content

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’)

The final step

The final step of the flow has again 2 reasons.

  1. The first line of csv files contains headers rather than data and should be ignored.
  2. 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:

skip(body('Filter_array'),1)

Ok, that was easy!

The final step

Now in each of the mapping we can use the following expressions:
split(item(),';')[0]
split(item(),';')[1]
split(item(),';')[2]

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 😉

2 thoughts on “3 steps to read csv files from SharePoint

  1. commented on March 10, 2020 by Dave McKenzie

    Thanks Pieter, this is awesome – just what I’ve been looking for! Thought I was going to need to use a third-party service for this.

    One thing – it looks like there’s a reference to an action called “Newline” in the main expression, which currently doesn’t appear in your article above. I found that I if I added a Compose action renamed to “Newline” just above the “Filter array” action, and typed a single carriage return into the action’s “Inputs” field, it made everything work a treat.

    • Hi Dave,

      That is exactly the way to do it and the way I did it. For some reason replacing \n or \\n doesn’t work.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: