Faster way to read excel rows.

[Meier]

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.

[fchopo]

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

[Meier]

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]

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. 

[fchopo]

Hello @Meier 

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

This would increase the speed of your flow.

Hope it helps!

Ferran

[Meier]

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]

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 · Accepted solution]

@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:

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?