Unanswered

How to do apply to each value in Excel row for 20000 rows file?

(0) Share

Report

Posted on by biboy852

I'm having a large Excel file which contains up to 20000 rows including space, comma for some rows as it's using to manage users. I When I do list row in Excel it works and runs within 3mins.

However when it goes to the part Apply to each value and filter array as I want to filter Date row from last week. This taking more than 10mins and the flow failed. Are there any other better ways to work with large Excel file? Thank you!

Categories:

General topics

I have the same question (0)

All responses (7)

Answers (0)

Michael E. Gernaey 53,932 Moderator on at

Like (0)

Report
Copy link

Link copied!

You want to use a Do Until loop, not an apply to each. You would need to essentially create a counter, based on the number of rows you have in Excel. The Do Until supports setting timeouts for each loop.

You would want to break down your loop into chunks, meaning you won't necessarily have to process all 20,000 in a single loop of the Do Until, but that's up to you but you want to break it down into chunks that won't time out (based on your time out configuration)

The trick will be to get your data so that you can do this. I haven't seen your data, but there are lots of great guides that provide specific examples that you can pick one that matches your situation.

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

Was this reply helpful? Yes No
takolota1 4,978 Moderator on at

Like (1)

Report
Copy link

Link copied!

@biboy852

If you just want to filter to the rows where the date is from last week, then you shouldn’t need the Apply to each at all. You should be able to combine expressions in a single Filter array action to check the date column for values greater than today - 14 days and less than today - 7 days. And that should run in a second or two.

Was this reply helpful? Yes No
biboy852 32 on at

Like (0)

Report
Copy link

Link copied!

How can I take the row to run filter? It becomes blank as an output.

Was this reply helpful? Yes No
takolota1 4,978 Moderator on at

Like (0)

Report
Copy link

Link copied!
@biboy852 To reference a column in the Filter array you need to use expressions like

item()?['InsertColumnNameHere']

Then you can put that in expressions like formatdatetime.

It looks like you are currently comparing two static variable inputs for each row. That’s not going to dynamically check each row, it just checks those static variable values each time.

Was this reply helpful? Yes No
biboy852 32 on at

Like (0)

Report
Copy link

Link copied!

Not sure if my output for filter last week day is correct or not. It's still showing blank after run the flow...

Was this reply helpful? Yes No
takolota1 4,978 Moderator on at

Like (0)

Report
Copy link

Link copied!

@biboy852

With that set up the filter array is checking

if data-date is greater than or equal to today’s date

Unless you have future dates in this column I’d expect it to return a blank or no rows.

Was this reply helpful? Yes No
biboy852 32 on at

Like (0)

Report
Copy link

Link copied!

I have the remove blank filter before run the filter array to get date from last week on the Date column.

Was this reply helpful? Yes No