Fastest Way To Read Large Excel Table In Power Automate
With this method I was able to get 50,000 rows from an Excel file in 7 seconds. By comparison, the Excel action built in Excel action is quite slow. It took over 5 minutes to get the same amount of rows. This is the quickest way to get all of the rows in an Excel table and it can handle up to 100,000 rows. You must learn this method if you want to build high-performance flows!
Table of Contents • Introduction: The Car Sales Report • Create An Excel Table With 50,000 Rows • Get The Drive ID & Item ID From The Excel File • Configure A Connection To Microsoft Graph • Transform The Response Into A Flattened Array Of Excel Rows • Parse The JSON Array Into A Set Of Dynamic Values
Introduction: The Car Sales Report
Each month car manufacturing company uses an automation to read the rows from a large Excel table and write them into an analytics database.
The Power Automate flow uses the HTTP With Azure AD – Invoke An HTTP Request action combined with the Microsoft Graph API to GET all rows to from an Excel table instead of the Excel connector.
Using the HTTP With Azure AD Connector the automation is able to fetch 50,000 rows in only 7 seconds. To contrast, the Excel connector built into Power Automate takes over 5 minutes to do the same actions.
Create An Excel Table With 50,000 Rows
Open SharePoint and create a new Excel file named Car Sales Report.xlsx inside of a document library.
Then insert a new table object called tblCarSales into Sheet1. Include the following table columns:
Use this sample data to populate the table.
|1996||Dodge||Ram Van 3500||24,883|
There are only 10 lines in the sample data but it can be copied into the spreadsheet as many times as desired to simulate a large data set. The example we are doing here together has 50,000 rows.
Get The Drive ID & Item ID From The Excel File
We need to get the Excel workbook’s Drive ID and Item ID to use in the Microsoft Graph API call. To find these IDs return to Power Automate and insert the Excel for Business (Online) – List Rows Present In A Table action into the flow. Choose the Excel table named tblCarSales from the Car Sales Report.xlsx spreadsheet.
Set the Top Count field to 1. We don’t need to get the rows from this spreadsheet We only want to get the information about where the table is located from the action.
After the flow is run the Drive ID (“drive”) and Item ID (“file”) can be found in the action’s inputs.
Next, create a set of two Data Operations – Compose actions named Compose: Drive ID and Compose: Item ID.
Get the Drive ID using this Power Automate expression.
And get the Item ID using this Power Automate expression.
Configure A Connection To Microsoft Graph
The Microsoft Graph API provides access to Microsoft 365 data and services, such as allowing developers to programmatically interact with Excel files stored in SharePoint. We can access it using the HTTP With Azure AD connector. Add the HTTP With Azure AD – Invoke An HTTP Request action to the flow.
Use this URL for both the Base Resource URL and Azure AD Resource URI fields when configuring the connection.
Read The Rows From An Excel Table Using HTTP With Azure AD
Now that we are connected to the MS Graph we can use the Create Table Row endpoint for Excel workbooks endpoint to get all of the rows from a table. Select the GET HTTP method to indicate a resource is being read.
Use this code in the Url of the request.
Transform The Response Into A Flattened Array Of Excel Rows
The Graph API call responds with a JSON containing all of the Excel table rows. But we must do some additional transformations of the JSON to get the data into a useable state. Add a Data Operations – Parse JSON action to the flow.
Then use this code in the Content property of the action.
Run a test of the flow to see what the JSON response looks like from the Invoke an HTTP Request – Read Rows In Table action. Highlight and copy the array in the body property of the JSON. Then use the Generate from sample feature and paste the array into the input box. The JSON schema should appear exactly as in the image above.
Next, add a Data Operations – Select action to the flow and switch the Map property to text mode.
Then use this code in the Map property.
Finally, insert another Data Operations – Select action and use it to define column names and create an array of records.
Year column value expression:
Make column value expression:
Model column value expression:
Cost column value expression:
Parse The JSON Array Into A Set Of Dynamic Values
We could directly reference the values in the Select: Create Rows action but it will be easier if we generate values that appear in dynamic content menu. Add a Data Operations – Parse JSON action and add it to the flow. Use the output of the Select: Create Rows action in the Content property.
Run a test of the flow to see what the JSON response looks like from the Select: Create Rows action. Highlight and copy the array in the body property of the JSON. Then use the Generate from sample feature and paste the array into the input box. The JSON schema should appear exactly as in the image above.
Now the Excel table column names appear in the Dynamic Content box.
Run The Power Automate Flow To Read Rows From An Excel Table
We have now finished building the flow. Give the flow a test run to see how fast it completes the actions. The example below read 50,000 rows in 18s.
Did You Enjoy This Article? 😺
Subscribe to get new Power Apps & Power Automate articles sent to your inbox each week for FREE
If you have any questions or feedback about Fastest Way To Read Large Excel Table In Power Automate please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.