In Microsoft Flow it is possible to use the Excel connectors to read data form a spreadsheet. When you want to read large Excel files you might run into issues.
Excel connector issues
Table of Contents
There few major issues with the excel connector are:
- You data needs to exist within Excel Tables.
- Large tables take long to load. (4000 rows takes about 2 minutes)
- The Excel connector likes hard coded names for sites, libraries and Excel files.
- 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:
Read large Excel files
If you want more however you might want to look into the graph API when you read large Excel files. 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.
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.
To get the ID ( if not available in dynamic content you can use the following expression.
body('HTTP_2')?['ID']
Now I can use the ID returned for the library. I used a compose in between these actions to hold the library details.
To create this compose action you might have to use a filter action to select the right document library from the HTTP 3 action.
For help on filtering arrays in power automate please look at my post about array filtering. this should now give you the single library id. I’ve created a compose action called Compose 2 to hold this library record.
This now give me the fully generic solution that helps me copy the flow between my Dev, UAT and production environment
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.
- No need for Excel Tables.
- Large tables are quick to load. (4000 rows takes about 4 seconds)
- My flow is a generic process that is easy to copy between environments
- No file locking on my Excel files.
[…] while back I wrote a post about reading Excel files stored in SharePoint using Microsoft Graph. This has been a post that has been helpful to many people so why hasn’t this been turned […]
not enough info
Hi Jo, can you explain what you mean?
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?
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.
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.
Instead of infopulse, I found this showed how to use the new azure site.
https://www.c-sharpcorner.com/article/calling-graph-api-from-power-automate-flow/
Thanks Chad that link is useful.
There is definitely not enough info to use this article as a reference. “This now gives the ID” … not for me, it does not. I do not have an object for ID from which to choose, while in your screenshot, you are able to select ID. So I must manually type in the hostname-SiteID-WebID. Then, it would be very helpful if you would include explanation and a screenshot of your compose action. It would also help if you explained the breakdown of the URI in the last HTTP call. And lastly, where did “formulas” come from in your “Apply to each 4” action? These instructions seem like they could be helpful and I thank you for putting this together, but they are just too incomplete and confusing.
Hi Phil,
If the ID doesn’t appear in the dynamic content then the tooltip in the screenshot shows the expression needed. Anyway, I will include the expression in the post as code as well.
Thank you very much, Pieter. I appreciate that. I’m sure you can tell that I’m somewhat new to Flow. It’s always tough as a newbie to connect the dots.
Thank you for the feedback.if you need any further help or if you find any other incomplete posts please feel free to contact me.
Hello Peiter
thanks for posting this article .
just assume that there will be excel file received every day and user will upload into sharepoint library then flow can be triggered to process that data into SP list . but these files wont have fixed number of rows of data(but columns are static) , in this case how can I set the excel range dynamically ?so this solution wont work right ?
Hi Pieter,
thanks a lot for this article. However, when I try to list the rows in an excel (in my sharepoint online document library) with this Graph URL, I get a Gateway Timeout error (status code 504) :
https://graph.microsoft.com/v1.0/sites/@{body(‘HTTP’)?[‘ID’]}/lists/%7B765ec93b-e00d-4816-a890-8ae4094c2adf%7D/drive/root:/MyExcel.xlsx:/workbook/tables/Table1/rows
I also tried using your syntax with a range on my worksheet, but always the same Gateway Timeout error :
https://graph.microsoft.com/v1.0/sites/@{body(‘HTTP’)?[‘ID’]}/lists/%7B765ec93b-e00d-4816-a890-8ae4094c2adf%7D/drive/root:/MyExcel.xlsx:/workbook/worksheets(‘MyWorksheet’)/range(address=(‘A1:N71288’))
Note that the URL like below just to reach the excel file works fine, and my OAuth setup is also fine :
https://graph.microsoft.com/v1.0/sites/@{body(‘HTTP’)?[‘ID’]}/lists/%7B765ec93b-e00d-4816-a890-8ae4094c2adf%7D/drive/root:/MyExcel.xlsx
So something in the API endpoint syntaxes like the below are causing a “Gateway Timeout” error and the action never completes …
:/workbook/tables/Table1/rows
:/workbook/worksheets(‘MyWorksheet’)/range(address=(‘A1:N71288’))
If possible, I’d really appreciate any advice or tips on what could be cauing these gateway timeouts ??
Thanks and best regards
Hi Rahul,
Can you try reading a smaller range. I wonder if you are hitting a limit somewhere.
14 columns and 71288 rows makes a lot of data.
You could try and look at the settings of the HTTP request action. The Pagination setting might help. Also the Allow Chunking setting could be something that will help you.
Hello Pieter Veenstra,
Thanks for posting this article . I have the same requirement as YKSiddal.
Just assume that there will be excel file received every day and user will upload into SharePoint library then flow can be triggered to process that data into SP list . but these files wont have fixed number of rows of data(but columns are static) , in this case how can I set the excel range dynamically ?so this solution wont work right ?
Can you please respond to this please…
You could always use the Graph API call described here:
https://docs.microsoft.com/en-us/graph/api/table-get
Hello Pieter,
Your guides are extremely helpful, thank you! From this guide and from the Import an “Excel Spreadsheet into SharePoint List” guide, there seems to be a step “formulas”. What is in there? I am having trouble converting the HTML Body from the Excel sheet into an array format. I appreciate your support, thanks!
great tutorial, but how can i determine the range. its dynamically. with a macro i can determine number of colums and rows..
I have just like you no table in excel
Hi Raymond,
You could read a range instead. The graph api call can also be used for reading ranges of the excel file.
https://docs.microsoft.com/en-us/graph/api/worksheet-range?view=graph-rest-1.0&tabs=http
If you need help with that please feel free to hit the chat button.
Hello,
I have this error.
Either scp or roles claim need to be present in the token.
Hi Serge, which permissions did you apply to you app registration in Azure? Are they application or delegation permissions?
HelloPieter, thanks
Ive applied file delegation permission only.
If i put application permission its working fine.
But i need to restrict permission for only the sharepoint site the service account has access.
Apply to each with Excel will give a veeeery long running time. Don’t EVER USE APPLY TO EACH in Flow if it’s not really needed. It has some bad feature. You can use Data operation (for example: Filter array) instead of apply to each. It’s very rare when you really need to use apply to each, so it is easy to avoid it.
I couldn’t agree more.
My Flow Magic presentation is all about avoiding apply to each steps.
https://events.collab365.com/c365_sessionroom/gc5-magic-tricks-with-power-platform/
The only time that an apply to each is ok, is when you use the Pieter’s method, https://sharepains.com/2020/03/11/pieters-method-for-advanced-in-flows/