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.
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. Please also note that,
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
Now, you can easily import the following Flow from here.
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.
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.
- 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.
- 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)
- 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
- 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.
- 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.
To do this, we’re calculating the length of the value property, of the previous action.
- We can then use Apply to each to process each Excel row downloaded above. In this case, I’m just incrementing the row counter value, for this demo.
In your flow, this is where you should be writing the logic for processing each Excel rows.
- One final part. We’ll increment the counter variable, skipCount, value by 5000. So, as to download the next batch of 5000 rows.
- 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.
- If you want, you can directly import this flow from this link.
- After import, be sure to update the configuration of the cards to match your setting.