Fastest Way To Read Large Excel Table In Power Automate

Imported reference from Matthew Devaney's blog for learning purposes. Original: https://www.matthewdevaney.com/fastest-way-to-read-large-excel-table-in-power-automate/. Author: Matthew Devaney.

Fastest Way To Read Large Excel Table In Power Automate Posted by - Matthew Devaney on - October 15, 2023 38 Comments

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 Reques t 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: Year Make Model Cost

Use this sample data to populate the table. Year Make Model Cost 2000 Honda Accord 15,242.74 1998 Oldsmobile Aurora 14,691.91 1984 Maserati Quattroporte 7,568.83 2011 Chevrolet Malibu 22,711.39 2006 Mercedes-Benz G-Class 13,949.48 1985 Ford Laser 34,159.31 2009 Kia Spectra 29,023.15 1996 Dodge Ram Van 3500 24,883 1985 Buick Century 33,778.10 2009 Mercedes-Benz S-Class 27,039.57

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.

actions( 'List_rows_present_in_a_table:_Car_Sales_Report' )?[ 'inputs' ]?[ 'parameters' ]?[ 'drive' ] Code language: JavaScript ( javascript )

And get the Item ID using this Power Automate expression.

actions( 'List_rows_present_in_a_table:_Car_Sales_Report' )?[ 'inputs' ]?[ 'parameters' ]?[ 'file' ] Code language: JavaScript ( javascript )

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. https: //graph.microsoft.com Code language: JavaScript ( javascript )

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.

v1 .0 /drives/@{outputs( 'Compose:_Drive_ID' )}/items/@{outputs( 'Compose:_Item_ID' )}/workbook/worksheets/Sheet1/tables/tblCarSales/rows Code language: JavaScript ( javascript )

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.

body( 'Invoke_an_HTTP_request:_Read_Rows_In_Table' )?[ 'value' ] Code language: JavaScript ( javascript )

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. first ( item ()? [ 'values' ] ) Code language: CSS ( css )

Finally, insert another Data Operations – Select action and use it to define column names and create an array of records. Year column value expression: item () [0] Code language: CSS ( css ) Make column value expression: item () [1] Code language: CSS ( css ) Model column value expression: item () [2] Code language: CSS ( css ) Cost column value expression: item () [3] Code language: CSS ( css )

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 Copilot Studio articles sent to your inbox each week for FREE Enter your email address Sign Me Up Questions?

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. Matthew Devaney Power Automate

Save Docusign Document To SharePoint With Power Automate