Power Automate Parse Excel

Power Automate: Read Excel File

Premise

Excel files are one of the most common entities for data storage. In this post I’ll demonstrate how to parse Excel file in Power Automate and read each row. This post will be divided into 2 sections.

  • Parse Excel File in Power Automate (MS Flow)
  • Parse Large Excel File in Power Automate (MS Flow)

Parse Excel File in Power Automate

Parsing an Excel file is quite easy in Power Automate. We, by default get an action, List rows present in a table.

list rows present in a table

At the very basic, it expects the following 4 parameters: –

  • Location: URL of the SharePoint site where the excel file is stored.
  • Document Library: Name of the document library.
  • File: Select the name of the file.
  • Table: Select which table to parse form the Excel file.

By simply providing these 4 parameter, our Flow will be able to parse the given Excel file.

In order to parse the rows in the Excel file, data must be defined in an Excel table.

Parse Large Excel File in Power Automate

The above method will work properly for small files only. If your Excel has more than 5000 rows then, the above method will not work. As a matter of fact, by default, it will only parse 255 rows!

In order to read all the rows from a large Excel file, we need to recursively read data from it in batches, as explained in the flowchart below.

Parse Excel in MS Flows

Let’s check out the steps below: –

Power Automate Steps

  • Initialize a variable, isContinue, to indicate whether or not to fetch more Excel rows. By default, the value is set to true.
Power Automate Variable
  • Initialize a variable, skipCount. This denotes number of rows to skip. By default it will be set to zero. After downloading each batch of rows, we’ll increment it’s value.
Power Automate Variable Int
  • Now comes the most crucial element, Do until. For this, we first need to specify the break condition i.e., when to exit this loop. For us, when our variable, isContinue becomes false, we can exit it.

Before proceeding further, change the default limits of Do until as well. We have set the count to 5000 and Timeout to P5D (5 days)

Power Automate Do Until Excel Rows
  • Next, we’ll use, List rows present in a table, to download the first batch of 5000 rows.

Before using our logic, we need to change the setting of this action to perform pagination

List rows present in a table
  • The basic configuration of the action, List rows present in a table, will be same as described above. However, we need to modify its advanced setting. We’ll set the Top Count to 5000 (max supported) and Skip Count to our dynamic variable, skipCount, which will be zero by default. In short, give me the first 5000 rows of the Excel. Next time it will query to download 5001 to 10000 rows and so on.
List Rows Present in Excel Advanced
  • Once, the rows are fetched we’ll then check if the downloaded rows count is equal to 5000 or less. If the count is 5000 then there might be more rows to be downloaded. However, if the downloaded rows are less than 5000 then, for sure, this is the last batch and there are no more rows to be downloaded.
Power Automate Conditions
  • We can then use Apply to each to process each Excel row downloaded above. In this case, I’m just increment the row counter value.
Power Automate Apply to each
  • One final part. We’ll increment the counter variable, skipCount, value by 5000. So, as to download the next batch of 5000 rows.
Power Automate Increment variable

Final Overview

Power Automate Parse Large Excel File

Key Takeaways

  • The approach is very hard coded. We need to drill down to the exact file name in order to parse the same.
  • By default, a small number of rows are returned which, might be useful in most of the cases.
  • For a large Excel file, we need to download the rows sequentially, in batches.
  • A given batch size cannot be more than 5000.
  • This card can only read data from an Excel table. It will not be able to parse plain Excel rows.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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