Skip to main content
Power Automate
    • Connectors
    • Templates
    • Digital process automation
    • Robotic process automation
    • Business process automation
    • Process advisor
    • AI Builder
  • Pricing
  • Partners
    • Blog
    • Documentation
    • Roadmap
    • Self-paced learning
    • Webinar
    • Business process and workflow automation topics
    • Overview
    • Issues
    • Give feedback
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • User groups
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Microsoft Power Automate Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power Automate
    • General Power Automate Discussion
    • Using Connectors
    • Building Flows
    • Using Flows
    • Power Automate Desktop
    • Process Advisor
    • AI Builder
    • Power Automate Mobile App
    • Translation Quality Feedback
    • Connector Development
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Galleries
    • Community Connections & How-To Videos
    • Webinars and Video Gallery
    • Power Automate Cookbook
    • Events
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Community Engagement
    • Community AMA
    • Community Blog
    • Power Automate Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power Automate Community
    • Galleries
    • Power Automate Cookbook
    • Excel read rows unlimit

    Excel read rows unlimit

    03-13-2020 12:48 PM - last edited 04-02-2020 12:21 PM

    juresti
    Continued Contributor
    5982 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    juresti
    juresti Continued Contributor
    Continued Contributor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    Excel read rows unlimit

    ‎03-13-2020 12:48 PM

    Title:

    Excel read rows unlimit

     

    Description:

    This flow helps you read excel data rows beyond your limit for any plan.

    The data is read in chunks, written to temporary text files, then read back in and processed.

     

    Detailed Instructions: 

    In loop get all rows control the number of loops by setting the value is equal to "number".

    The change limits should match the equal to value. I could not find documentation on this but this also controls the number of loops.

    To come up with the number of loops, you will need to do some math.

    For example, I want to read 10,000 rows at 500 each loop, so that makes 20 loops. 20 x 500 = 10,000.

     

    You will also need some math in the list rows present action step.

    The top count and skip counts will control which set of data is read each loop.

    Using the same example of 500 each loop, set top count to 500 and the skip with the formula to multiply the loop by 500

    mul(variables('loopTrack'),500).

     

    The JSON action is where you need to set your own columns and types.

    You should be able to figure out the column name and data type structure by looking at the Parse JSON.

    Then you can set your columns and data types and even add more columns.


    This flow writes each record into sharepoint, the destination can be changed.

     

    Questions:

    Please post any questions.

     

    Anything else we should know: 

    Note that your hourly, items per 5 minute, and other limits can not be over come.

    That is why this flow does not use parallelism to help keep it under the those limits, and it can't use parallelism due to the use of variables within the loops.

    Yes, the flow can take a long time to run on large data, yet it achieves the goal of automating it for you while you sleep.

    So it is probably best suited for plans where you can only read less than 5,000 rows and the file has less than 100,000 rows. 

     

    My sample run of 50,000 rows written to sharepoint from excel

     

    thumb.PNG12.PNG

     

    Preview file
    27 KB
    exceldatarowunlimit_20200402191617.zip
    Labels:
    • Labels:
    • Automated Flows
    • Solutions
    Message 1 of 25
    5,982 Views
    3 Kudos
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    • « Previous
      • 1
      • 2
      • 3
    • Next »
    usiala
    usiala
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎10-05-2022 01:10 AM

    Hi,

    I haven't tested it yet

    but, if it does what you say, then you are a life saver.

    Thanks

    i will test and come back to you.

    BR

    Message 11 of 25
    231 Views
    0 Kudos
    Reply
    MustH
    MustH Helper III
    Helper III
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎12-30-2022 05:25 AM

    @juresti @Yi66 

    My Flow is only creating 768 Rows, why is this happening? and the flow ran successfully.!!

    MustH_0-1672406628944.png


    The Items are getting created 256 why is this happening?

    MustH_2-1672406724096.png

     

    I have 3000 Rows in my excel and I have set the Top Count to 1000, Skip count to  >> mul(variables('loopcount'),1000)



    Help!!!!

    Message 12 of 25
    173 Views
    0 Kudos
    Reply
    MauroViano
    MauroViano Advocate I
    Advocate I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎12-30-2022 06:45 AM

    @MustH in the "List rows present in a table" steps. go to the 3 dots, enable pagination up to 3000. Happened to me too before, that fixes it.

    Message 13 of 25
    163 Views
    1 Kudo
    Reply
    MustH
    MustH Helper III
    Helper III
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    a month ago

    @MauroViano  Well, What if I have 10,000 rows?
    it will read only 256 rows? 

    Help @juresti 

    Message 14 of 25
    150 Views
    0 Kudos
    Reply
    MauroViano
    MauroViano Advocate I
    Advocate I
    In response to MustH
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    a month ago

    @MustH  as long as you have enough repetitions its ok, the "List rows present in a table" connector comes with a default limit of 256 you can change that with pagination as i explained above.

    What you MUST make sure of is that the connector can fetch more or equal the amount of records that you have as "Top count" otherwise you'll mess up (which is what happened to you).

    Be aware that you might want to separate your process into smaller steps or have 1 process call child process every X records, as you might probably reach the action Limit quite fast if you want an action on every row and the file is big...  (see https://www.youtube.com/watch?v=DLhwnZ5JRvE for help)

     

    Message 15 of 25
    139 Views
    1 Kudo
    Reply
    MustH
    MustH Helper III
    Helper III
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    a month ago

    @MauroViano  Ok I understand that


    But I have set the top count to 1000, and Pagination to 5000

    Now, will I be getting 10,000 records in excel? as I want to get the records and send them email 1 by 1 

    Message 16 of 25
    127 Views
    0 Kudos
    Reply
    MauroViano
    MauroViano Advocate I
    Advocate I
    In response to MustH
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    a month ago

    @MustH 

    You'll get your flow stopped by the Action Limit, but yes, with 10 loops of 1000, it should with no problem go to 10.000, the problem you'll face is the action limit as i assumed.

    if you wish to act on the 10.000 records you will have to go with parent/child flows (see https://learn.microsoft.com/en-us/power-automate/limits-and-config).

    This workaround works for skipping the 5000 pagination limit on free accounts, but does nothing on the "Action Limit" which is 500 actions per flow.

     

    What im trying to say is that this flow has no use for you if you are going to run an action on every row, you will be forced to go with parent/child flows because of the amount of actions you are trying to process in a single flow... So power automate will disable your flow as soon as you get to the 501th record.

     

    My advice: You need to take the same concept of skipping x*500 (x=repetitions) and taking first 500 results, but doing it from a parent flow, executing a child flow for every 500 records on the file, and the child flow should get those 500 records and send the email, and the parent flow should continue with the next 500 and so on...

    I hope i have been of assistance. Regards.

    Message 17 of 25
    119 Views
    1 Kudo
    Reply
    MustH
    MustH Helper III
    Helper III
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    a month ago

    @MauroViano  

    Well, I understood your point but I am confused in making that Parent child flows. because I am very new to Power Automate and I have to do this task.. 

    and one more thing. the person who made this flow is also performing action ( creating an item in SharePoint from Excel file) of 50,000 rows?

    how this flow is not getting stopped at 501th? record??? 

    Message 18 of 25
    111 Views
    0 Kudos
    Reply
    MustH
    MustH Helper III
    Helper III
    In response to MauroViano
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    a month ago

    @MauroViano  could you please help me making this flow.. 

    I will be very thankful to you !!! Please!!

    Message 19 of 25
    108 Views
    0 Kudos
    Reply
    MauroViano
    MauroViano Advocate I
    Advocate I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    3 weeks ago

    @MustHsorry, been caught on work. ill help. First, go to power automate (https://make.powerautomate.com)

    Go To Solutions, create a new solution. Create a Flow there that starts with your trigger, then set up a "List Rows Present in a Table", enable pagination to 2k, set up a Variable as the Length of the step "List Rows present in a table".

     

    Create an instant flow(in the same solution) which starts off of a button, add input on that initial step as Number, named "Runs".

    now create an action "List rows present in a table", set "Top Count" as 200, Set Skip count as mul( "Runs",200)

    Set your send mail action here with the rows from the previous step.

    After that set up an action "Respond to Powerapps or Flow"

     

    BACK TO THE ORIGINAL FLOW.

    Now you can create the final "Call a Child flow" action, and referr this first flow to the second, once you pick the second flow it will enable a field so you can define "Runs" for the other flow, Runs should be your variable (Length of list rows) plus 1.

     

     

     

    Message 20 of 25
    58 Views
    1 Kudo
    Reply
    • « Previous
      • 1
      • 2
      • 3
    • Next »

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign up free
    • Sign in

    Browse

    • Templates
    • Connectors
    • Partners

    Downloads

    • Mobile
    • Gateway

    Learn

    • Documentation
    • Learn
    • Support
    • Community
    • Give feedback
    • Blog
    • Pricing

    • © 2023 Microsoft
    • Contact us
    • Trademarks
    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Terms & conditions
    California Consumer Privacy Act (CCPA) Opt-Out Icon Your California Privacy Choices