Microsoft Flow – Read large Excel files within seconds without creating tables using Microsoft Graph

In Microsoft Flow it is possible to use the Excel connectors to read data form a spreadsheet. there are however a few major issues with doing this:

  1.  You data needs to exist within Excel Tables.
  2. Large tables take long to load. (4000 rows takes about 2 minutes)
  3. The Excel connector likes hard coded names for sites, libraries and Excel files.
  4. Excel files get locked as you configure flows. And it can take a while for the locks to be removed.

If the above is not a problem please feel free to create a flow like this:

Get Tables and List Rows actions

 

If you want more however you might want to look into the graph API. With the help of Flow Ninja, John Liu‘s post on twitter I got this all working.

I started with setting up my app permissions which gave me a Tenant ID, Client Id and Secret which I then used in 3 variables. If you need some help setting this up you could look at the infoPulse site.

Now I’m having to Collect the site details from MS Graph. This can be done by configuring an HTTP action as shown below. After the v1.0/sites bit simply supply the url of your subsite.

HTTP request calling MS Graph

 

This now gives the ID of the site that we need to get the list of the lists an libraries that we are interested in. Remember you will need to specify the same authentication bits as in the previous HTTP call.

HTTP request calling MS Graph getting lists

 

Now I can use the ID returned for the library (I used a compose in between these actions to hold the library details.)

HTTP request calling MS Graph reading range

This now give me the fully generic solution that helps me copy the flow between my Dev, UAT and production environment

Generic graph call

 

Then finally I can run through the Excel data with an Apply to each step. In this case I only did a Compose to collect each row but most likely you would now do something with the data like updating a SharePoint list.

Step through the data from my excel

 

  1. No need for Excel Tables.
  2. Large tables are quick to load. (4000 rows takes about 4 seconds)
  3. My flow is a generic process that is easy to copy between environments
  4. No file locking on my Excel files.

9 thoughts on “Microsoft Flow – Read large Excel files within seconds without creating tables using Microsoft Graph

  1. commented on January 22, 2020 by jo moma

    not enough info

  2. commented on February 3, 2020 by Farhan Rob

    Hi Pieter,

    What is the trigger of this flow?

    • Hi Farhan,
      You can use any trigger you like. The trigger isn’t really relevant. What should start your process?

      • commented on February 3, 2020 by Farhan Rob

        Thanks for the quick response Pieter.

        I have an automated process from SAP to send an Excel sheet as an attachment to my email nightly. The issue arises that the data is not in a table within the Excel file and there is no way to configure that. So I stumbled upon your post and how this HTTP method can be used to decipher data that is not within a table.

        I’d love if you could give me an example of how to utilize this with the problem statement that I have described above if you have the time.

        Farhan

      • If the excel appears as an attachment to an email address that has it sole purpose of receiving these emails then you could trigger on the email arriving.

        Then get the flow to copy the attachment to a file in SharePoint and then use MS graph to read a range from the excel file.

      • commented on February 3, 2020 by Farhan Rob

        Is this range dynamic? Or is it possible to set a maximal range to capture all the data regardless of column length.

      • No, you will have to set it to range values.

        So if your excel has a maximum of 200 rows then you would have to set it to read 200 rows. It might mean that you end up with empty rows.

Leave a Reply

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

%d bloggers like this: