cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Meier
Frequent Visitor

Faster way to read excel rows.

Hello everyone, I am having a problem where reading the rows of an excel file takes a few minutes, is there any way to speed up reading the rows of an excel file? I am using the "List rows present in a table" followed by "Apply to each" that checks a table in the excel file.

 

To sum up things: I would like to check the rows of a table in an excel file stored in OneDrive much quicker than 3-10 minutes. There are about 356 rows in that table, but this table will grow over time.

1 ACCEPTED SOLUTION

Accepted Solutions
Paulie78
Super User
Super User

@Meier it's not completely clear from your condition what your comparison is, but it is clear that you have an or condition so I have tried to provide an example that works with the sort of thing you are doing, take a look at this screenshot:

2020-12-15_23-42-36.png

 

  1. List rows present in a table as normal.
  2. Filter array uses an or condition and two "contains" expressions against the Excel column "Application Number"
  3. A compose step is used to evaluate the number of matching records from the filter array. So if the length returned is 0 then you know there were no matching records, if it is greater than zero you know that there were.

You could use the number of rows returned to execute a condition or just go straight into a apply each loop on the output of the filter action. If none were returned then it will simply do nothing, if some were then you could loop around each result. 

Does that make more sense as a possible route?

View solution in original post

7 REPLIES 7
fchopo
Super User
Super User

Hello @Meier 

Some questions:

1) Do you have to read all the rows in the Excel table?

2) Which kind of operation are you doing in the apply for each?

3) Could you share your flow so we can help you?

Regards,
Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!
Meier
Frequent Visitor

Screenshot 2020-12-13 145937.jpg

Hello @fchopo, thank you for replying. To answer your questions,

 

1. Yes, I would like to parse all content in the column of the table,

2. I checking to see if a value exist in the table, row by row, and

3. Sure, I've uploaded the screenshot of the basis of the flow.

Paulie78
Super User
Super User

What I would do is add a filter action after "List Rows Present in a Table" which removes all the records you are not interested in. Then you can go into your loop. 

By doing this you will:

  • Remove any records that you are not interested in before going into the apply each loop.
  • Remove the need for the condition in the apply each loop.

Which will make it significantly faster. Depending on what actions you are performing within the apply each you may also be able to use concurrency which will speed things up further. 

Hello @Meier 

As you have to read all excel rows, I would run the apply to each in parallel:

ForEach-parallelism-setting

 

ForEach-parallelism-50

 

This would increase the speed of your flow.

Hope it helps!

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!
Meier
Frequent Visitor

Hello @Paulie78 thank you for replying. How can I use the filter action to check the contents of a table for each row, instead of doing a loop for checking each? 

Meier
Frequent Visitor

Hello @fchopo thank you also for replying. Currently, the checking of the rows for the excel table is triggered when an email is received. So, the problem that I am having is that a high volume of email is received but the loop through each row of the excel file sometimes take a considerable amount of time where some of the flows aren't trigger for an incoming email.

 

If i ran the look up for each rows in parallel from my trigger, wouldn't i still have to deal with the time problem for each item recieved? As mentioned by @Paulie78, could i also not use a filter to quickly check if an item exist then continue?

Paulie78
Super User
Super User

@Meier it's not completely clear from your condition what your comparison is, but it is clear that you have an or condition so I have tried to provide an example that works with the sort of thing you are doing, take a look at this screenshot:

2020-12-15_23-42-36.png

 

  1. List rows present in a table as normal.
  2. Filter array uses an or condition and two "contains" expressions against the Excel column "Application Number"
  3. A compose step is used to evaluate the number of matching records from the filter array. So if the length returned is 0 then you know there were no matching records, if it is greater than zero you know that there were.

You could use the number of rows returned to execute a condition or just go straight into a apply each loop on the output of the filter action. If none were returned then it will simply do nothing, if some were then you could loop around each result. 

Does that make more sense as a possible route?

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Top Solution Authors
Top Kudoed Authors
Users online (1,652)