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.
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.
length(body('List_rows_present_in_a_table')?['value'])
- 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.
Final Overview
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.
- 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.
How will it work if I do not have a table in the excel?
LikeLike
It will not. The prerequisite of parsing an Excel file, whether in Flow or using Graph API, is that the data should be present in a table.
LikeLike
If we have excel files which don’t have tables. We cannot automate that process?
Is there any other way around to do something with those excels?
LikeLike
very helpful. Thanks!
I’m a beginner of PowerAutomate. It would be great if you share some basic concepts of flow.
LikeLike
It’s tough to summarize it here. Better to follow some online articles/post around the same and start using it. The layout of the Flow can be confusing at times, but once habituated, it becomes very easy. The key is to visualize the steps to solve the problem sequentially.
LikeLike
Hi Piyush – this was working well for me till few days back. Now the flow is continuously running in a loop. Unable to debug as flow is eventually cancelled. Did Skip count stop working?
LikeLike
No. everything seems to be working. Just double check if the exit condition of Do-Until is matched. Also, if there are too many rows in the excel then, it might take a while
LikeLike
I am trying to implement this – I have an error on Length function
Do you have this on you tube please!
(length is as collection?)
LikeLike
Length counts the value of the previous action.
“length(body(‘List_rows_present_in_a_table’)?[‘value’])”
This helps in determining if this is the last batch or not.
LikeLike
HI Piyush
I got the flow going successfully but nothing is happening
Only difference I see is function you have entered is in Red color mine is Blue
I entered true and false value by picking function in expression
(I created the flow 3 time still same
Any Advice Please!!!
LikeLike
Hi, Not sure what could be wrong there. But, I’ve exported the Flow here. If you’d like, you can import and compare the same.
If you do import, just make sure to update the configuration of the cards to match your setting.
LikeLike
Thanks
It will be better for everyone if you update the screen shots of the flow from template, I think there is a difference between post and template.😔
On the other hand share your expertise how to write them to SharePoint list with Date please
Thanks and appreciate sharing your flow template 👍👍👍
LikeLike
How we can update Excel attachment Data (Mail) in SharePoint Library file, if file is exist or create new file if its not?
LikeLike
If you want to copy a mail attachment file to a library then, you can also refer the this post.
LikeLike
If I try to use “List rows present in a table”, the flow fails saying “No table was found with the name ‘TableName’ even though I can open the Excel file and clearly see the table name.
LikeLike
Normally, the table names should appear automatically, in the drop down. You can try with another Excel file for testing. Maybe the table was not created properly in the Excel.
LikeLike
If I try to use the “List rows present in a table” my flow fails and says No table was found with the name ‘TableName’ when I can open the Excel file and clearly see the table with the correct name.
LikeLike
my value is dynamic from formular .some time value is error. please help to solve the problem
LikeLike
I’m sorry but, which value is dynamic? Do you mean to say that, the Excel file is dynamic?
LikeLike
Hi can you please share the flow
LikeLike
Let me check. Can you let you by next week.
LikeLike
I’ve exported the flow here. Be sure to update the configuration post import, to match your local setting.
LikeLike
willing to purchase it if it wont be shared for free.
LikeLike
Not required. We’re here doing our community service 🙂
I’ve shared the link above. Hopefully it’ll work this time!
LikeLike
Dear Mr Piyush thank you soooo much
LikeLike
Hi Piyush
could you elebrate syentex for Increment variable – allExcellRowsBy1 please
LikeLike
Hi,
So this is a dummy variable. I’m just demonstrating that we have all the Excel rows. I’m just incrementing this counter variable by 1, each time, an Excel row is processed. So, at the end, the value of this variable matches the number of rows in our Excel file.
In reality, inside the “Apply to Each” loop, you’ll get each rows of excel one by one. This is where, you should be doing you file processing logic.
LikeLike
please advice how did you increment all excel rows by 1 ?
LikeLike
We don’t need to increment the rows ourselves. “Apply to Each” automatically parses each Excel row one by one. I’m just incrementing a number variable by 1, each time a row is processed. So at the end, my counter variable will be equal to the total number of Excel rows.
This, as you can understand, is for the demo purpose only. In reality, inside the “Apply to Each”, you should be writing your logic for processing each row of the file.
LikeLike