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. 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.

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.

    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'])
Power Automate Conditions
  • 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.
Power Automate Apply to each

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.
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.
  • 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.

35 thoughts on “Power Automate: Read Excel File

      • 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?

        Like

  1. very helpful. Thanks!
    I’m a beginner of PowerAutomate. It would be great if you share some basic concepts of flow.

    Like

    • 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.

      Like

  2. 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?

    Like

    • 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

      Like

  3. 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?)

    Like

    • 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.

      Like

  4. 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!!!

    Like

    • 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.

      Like

      • 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 ๐Ÿ‘๐Ÿ‘๐Ÿ‘

        Like

  5. How we can update Excel attachment Data (Mail) in SharePoint Library file, if file is exist or create new file if its not?

    Like

  6. 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.

    Like

    • 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.

      Like

  7. 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.

    Like

    • 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.

      Like

    • 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.

      Like

  8. Where would you put in an action to write each row to a sharepoint list? Ideally I’ll have one of these to populate the list and then I’ll need to rewrite it to take in updates as the excel file is updated and added to over time.

    Thank you for this post and especially for making the exported flow available! I know you spent a lot of time on it and it is greatly appreciated.

    Like

    • Data is parsed on the basis of Excel Tables. We can have ‘n’ number of sheets but the table names are unique across the sheets. When we use this action, it will present you all the table names present in your Excel across the sheets. Just select the appropriate table name.

      Like

  9. Hi Piyush,

    I imported your flow and only added an action to import the data from excel into my SharePoint list.
    Unfortunately the flow keeps running even though it already has imported all the entries of the excel file. It just imports the entries over and over again.
    Do you have an idea, what could be wrong with the flow?

    BR
    Christopher

    Like

  10. Hi Piyush,
    Thank you for this Flow. I have 48000 records in a xls can i use this Flow by updating “Top Count” from 5000 to 50000 . I need to update data from xls to SharePoint List.

    Like

  11. Hi Piyush,
    For the First Time, it will be working fine. But the second time it will load the duplicate rows. But my excel file doesn’t contain any duplicate rows.
    Any Advice Please!

    Like

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.