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 😉

9 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.

  2. commented on April 7, 2020 by Furkan

    Hello, very helpful. Can you share the full flow with us because I couldn’t reapply it. Thanks !

  3. commented on April 17, 2020 by Jarek

    I am getting such error on filter array using your code: Unable to process template language expressions in action ‘Filter_array’ inputs at line ‘1’ and column ‘3308’: ‘The template language function ‘uriComponent’ was invoked with invalid parameters. The parameter at index ‘0’ cannot be converted to URI component.’.

    • That sounds like you are getting the first element of an empty array. You might want to check the data that you are pushing into the Array Filter.

  4. commented on April 17, 2020 by Jarek

    Hello,
    Data is there, I checked. Issue lies in split, it just doesn’t catch it. I have even converted data so \r\n is replaced by |. So i do
    json(
    uriComponentToString(
    replace(
    replace(
    uriComponent(
    split(
    outputs(‘ConvertEOL’),
    ‘|’)
    ),
    ‘%5Cr’,
    ”),
    ‘%EF%BB%BF’,
    ”)
    )
    )

    Where outputs looks like: “column1;column2|data1;data2|data3;data4|..etc”

  5. commented on April 20, 2020 by Sherwin

    I need help after the get file content. I add the filter array but when I execute the flow it gives error “The value must be of type array.”?

    • Hi Sherwin,

      I would take the input that you are using for the Filter Array action and put it into a compose action. Then run the flow and see what the data is that you get? If this isn’t an array or if there isn’t any data then slowly take away bits of the expression and check where the unexpected data processing is happening.

Leave a Reply

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

%d bloggers like this: