web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate / Need help in handling ...
Power Automate
Unanswered

Need help in handling a excel file with more than 10000 rows of data.

(0) ShareShare
ReportReport
Posted on by

Hi All,

 

We're getting an excel file report from the business that has more than 10K rows of data. Our requirement is to copy the entire data and overwrite it into another master excel file. I've already developed a flow but it takes hell out of time to complete this process.

 

Is there any other to achieve this faster?

 

Thanks.!

Categories:
I have the same question (0)
  • MarconettiMarco Profile Picture
    3,812 Super User 2024 Season 1 on at

    Hello @Santhosh_V_26 ,

    have you already enabled the concurrency control into the Apply to each control?

     

    BR,

    Marco

  • takolota1 Profile Picture
    4,974 Moderator on at

    @Santhosh_V_26 @MarconettiMarco 

     

    Need to update and/or create many rows in Excel?

    Try Excel batch actions in Power Automate:

    https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-p/1624706

     

    Demo Video:

    https://youtu.be/HiEU34Ix5gA

     

    Also about how many cells is your table?

    And how many rows do you need to overwrite each time?

  • Santhosh_V_26 Profile Picture
    on at

    Thanks, @takolota & @MarconettiMarco for your response on this!

     

    Below is my entire scenario,

     

    We receive an excel file(.xlsx) with a table once a day that has approximately 10K rows with 6 columns. Once we received that we have to copy that data and then need to overwrite the data in our main excel file. I have developed a simple flow and it working fine but it takes around 5-6 hours to complete the process.

     

    @MarconettiMarco, I haven't made any changes in the Concurrency control.

     

    Kindly let me know if any details are required.

     

    Thanks for your help!!!

  • takolota1 Profile Picture
    4,974 Moderator on at

    @Santhosh_V_26 @MarconettiMarco 

     

    If you go to the 3 ellipses (dots) on the Apply to each loop, then go to settings to change the concurrency settings & max it out.

     

    But 10,000 updates will still take about 6 hours & may max out the daily calls your license can make. 

    The method mentioned above for batch processing may handle 10000 updates in less than 7 minutes and only use about 150 instead of 10000 calls.

     

    It just requires that Office Scripts is available in your environment.

  • Santhosh_V_26 Profile Picture
    on at

    Hi @takolota,

     

    Whether we need to make any changes to the two office scripts?

    Thanks.

     

    Regards,

    SK.

  • takolota1 Profile Picture
    4,974 Moderator on at

    @Santhosh_V_26 

     

    You shouldn’t. I designed it so all you should have to do is edit things in the flow.

    Did you find a bug/issue or something?

  • eliotcole Profile Picture
    4,363 Moderator on at

    Hi, @Santhosh_V_26 ... There is an easier way to do this, which involves using the Office 365 Groups connector and the Send an HTTP request action there. You just have to get used to sending these commands.

     

    When you have the value set from the get table (or get used range HTTP request - examples 1 & 2) just send it to the desired sheet using the add row function inside a Send an HTTP request.

     

    This action can be used to add multiple rows at once, and so long as you are formatting it all correctly (the real hassle) then you should be able to do this in just a few actions. It will still take some time, but if you've not got any real formatting going on in either sheet, then you're on the money for a relatively easy time.

     

    I would advise doing some testing, first, with small data sets, and this will enable you to better understand how to put together the request.

     

    All of this said, the solutions provided by others might be a bit easier to understand, and implement (that's not an insult to anyone, that's what you want ... easy to understand), so if any of this is beyond where you are, then ... y'know ... ignoooooore! 😅

     

    Full documentation is available on the docs site, here:

    With Tables

    https://docs.microsoft.com/en-us/graph/api/table-post-rows?view=graph-rest-1.0&tabs=http

    https://docs.microsoft.com/en-us/graph/excel-write-to-workbook#add-a-row-or-rows-to-an-excel-workbook-with-a-single-rest-request

     

    Without Tables

    https://docs.microsoft.com/en-us/graph/api/range-insert?view=graph-rest-1.0&tabs=http

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Forum hierarchy changes are complete!

In our never-ending quest to improve we are simplifying the forum hierarchy…

Ajay Kumar Gannamaneni – Community Spotlight

We are honored to recognize Ajay Kumar Gannamaneni as our Community Spotlight for December…

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 501 Super User 2025 Season 2

#2
Tomac Profile Picture

Tomac 323 Moderator

#3
abm abm Profile Picture

abm abm 237 Most Valuable Professional

Last 30 days Overall leaderboard