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

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.

  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 😉

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

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

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

  3. 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.’.

  4. 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. 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.”?

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

  6. Hi, great method, and thanks for sharing, I have a couple of questions though. in the section Convert the CSV file to json, it has an item expression in the value field (where the value is not equal to null), can you confirm what that need to be, I have tried to enter item() but my flow keeps turning it into triggerbody(item) instead of this item? Also I am getting the error “Unable to process template language expressions in action ‘Filter_array’ inputs” and have tried to implement the fix mentioned in the comments but getting nowhere, my data is fine, I dont have any blanks or blank rows or anything the I can confirm that the outputs show exactly what I was expecting. Help?

    1. Hi Rob,

      The advanced expression should be

      @not(equals(item(), ''))

      I think that when you get the template language expression error that there is something wrong with the input of the filter action. this could for example happen when the input contains characters important to json itself. e.g. ” or @ or anything else that json might see as a special character. I would need to look at the actual data to get a better feel for that problem.

  7. Hi Pieter, thanks for that, I have updated the item expression and I think that is OK, although it has the wrong icon on it!

    This is from the exchange report export and after I have edited to ; delimited and | instead of returns Im left with something that looks like this:

    “Report Refresh Date;User Principal Name;Display Name;Is Deleted;Deleted Date;Created Date;Last Activity Date;Item Count;Storage Used (Byte);Issue Warning Quota (Byte);Prohibit Send Quota (Byte);Prohibit Send/Receive Quota (Byte);Deleted Item Count;Deleted Item Size (Byte);Report Period\r|2020-06-06;arcnsm@hants.gov.uk;Martin, Shaun;False;;2018-04-27;2020-06-06;170885;101752206840;105226698752;106300440576;107374182400;52846;12081470886;30\r

    from what you said, I’m wondering if either the \r or @ symbols in the email are causing issues, I dont really know how to remove the \r, I have to use

    replace(replace(body(‘Get_file_content’),decodeUriComponent(‘%0A’),’|’), ‘,’, ‘;’)

    to get rid of the \n in the first instance. Then the next question would be how to translate the @ symbol so it can be used? Is there something in the header row thats causing a problem? Maybe I need to delete the headers?

    1. You could replace the @ with an @@ so that it is escaped. These problems can be a real pain.

      If you need any detailed help we can have a chat about making this work. please feel free to hit the chat button on this site.

  8. Hi Pieter, I got it to work in the end, as my csv was not being generated by Excel I removed the uricomponent / uricomponenttostring sections. In order to diagnose the issue more effectively I also broke each step down into separate steps

    Just to let you know as well you can remove those special characters with encoding the whole string to URIsafe, you can do another

    replace(body(‘Get_file_content’)?[‘$content’], decodeuricomponent(%EF%BB%BF), ”)

    There are a couple of things you could add here that might be helpful, how to convert that array back into an object so we can use parseJSON, also I find with these that once done, in order to do more with it, you need to push it through an apply to each command, which in most cases will be locked down to a maximum of 5000 rows.

    Just to finish, this post has been exceptionally useful and your help on chat was very valuable, thank you Pieter for your help with this and if you need anything, let me know!

    1. Hi Rob,

      Thank you for your comments and feedback.

      It is great to hear that you got it to work at the end.

      In general I skip the parse json other than when I use parse json to validate data. But I agree that it can help make flow development just that little bit easier.

  9. Correct to include a valid reference to ‘Newline’ for the input parameter(s) of action ‘Filter_array’. I’m getting this error with filter array step. can you advise ? I have only 2 columns with my excel name and group.

  10. I’m also getting same error “Correct to include a valid reference to ‘Newline’ for the input parameter(s) of action ‘Filter_array’.” could you help me

    1. There isn’t really a great way around that, but what you could do it replace ‘”,”‘ with ‘”###”‘ before processing your csv. Now your csv separator is ### and the commas in the csv data will be processes as you want.

  11. Hi Pieter,

    Your solution looks fantastic! Life Saver indeed.

    I followed into my solution, but I am getting error on Select action as-

    failed: ‘The template language expression ‘split(item(),’;’)[1]’ cannot be evaluated because array index ‘1’ is outside bounds (0, 0) of array

  12. I am having issues w/ commas as well and saw your resolution. The csv file I’m using isn’t clean data and there are commas and line breaks within the data. Is your resolution for the comma issue with replacing ‘,’ commas w/ something else ‘###’, being done outside of power automate? I would like to automate everything but it looks like w/o regular expressions, getting the commas out of the csv data isn’t possible w/ flow.

Leave a Reply

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

%d bloggers like this: