Very Slow For Each Loop

[Bobby_J (BOB)]

Hi,

I am attempting to do use UiPath to automate consolidation between multiple Excel files which is currently being done manually/with formulas. As part of this, I read in the Excel workbooks to datatables, remove unneeded columns, and add in information which would be done through formulas. In one basic example, I am reading strings in several columns, adjusting format, and concatenating them to allow lookup in the 2nd Excel.

This process is very slow - what takes ~1 second for 5,000 lines in excel is approximately 1,000 times slower in UiPath (and spikes 2 of 4 cores to ~95% use). None of the manipulations are complex - 4 items involved, and each has a basic string operation (substring, padright, etc.)

In similar cases I’ve encountered, UiPath will typically do 100s - 1000s of rows per second. Does anyone know reasons why this is so slow, or what can be done to fix it? I’ve adjusted the formulas a few times and there is no noticeable effect

For Each Row (in Data Table) {
str_YY = row.item(“Year”).tostring.substring(2,2) // Other format only uses YY, not YYYY
str_InvoiceNum = row.item(“Invoice”).tostring.padleft(10,‘0’) // other format always has 10 digits
[…]

row.item(“Foreign_Key”) = str_YY + str_InvoiceNum + […]
}

[Bobby_J (BOB)]

Replacing all the string reads with simple static values (e.g., str_YY = “21”) speeds this up slightly (~8/sec), but still nowhere near what it ‘should’ be.

[prasath17 (Guru)]

@Bobby_J

For Each Row (in Data Table) {
str_YY = row.item(“Year”).tostring.substring(2,2) // Other format only uses YY, not YYYY
str_InvoiceNum = row.item(“Invoice”).tostring.padleft(10,‘0’) // other format always has 10 digits
[…]

row.item(“Foreign_Key”) = str_YY + str_InvoiceNum + […]
}

hi @Bobby_J - If you could share the exact requirement with some sample data, We could take a look and try to see if this can be done in a one line invoke code which would avoid looping…

 dt.AsEnumerable.Where(function(d) d(“Foreign_Key”).ToString = 
 d(“Year”).ToString.substring(2,2) + d(“Invoice”).tostring.padleft(10,‘0’))

Something like this ( Pseudocode )

[Bobby_J (BOB)]

Thanks for the response - I don’t have much experience with invoke code, but I think that could work. Let me see if I can figure it out!

[prasath17 (Guru)]

@Bobby_J - Just to make sure, Above provided sample is not invoke code , simple assign statement

[Bobby_J (BOB)]

I am trying to use DataColumn.Expression Property (System.Data) | Microsoft Learn, which appears to be a correct way to do this (at least in .net), however using either assign or invoke code throws an exception by the invoked object (I am assuming the DataColumn on the left hand of the equation).

My guess is that this is one of two things - (1) the object [ dt_payment.columns(“combinedColumns”).expression = ]doesn’t recognize the string of code being assigned to it as valid or (2) the string of code’s syntax is invalid due to scoping issues (e.g., the .net example just references the column name without any further context, while UiPath needs something like = dt_payment(“Invoice”).tostring

Do you have any insight into what the proper format would be? Or is there a better property/method to be using? I tried to understand your pseudocode, and think I vaguely get it (not a .net expert), but UiPath doesn’t seem to have the .AsEnumerable or other parts of the code, nor am I familiar with the syntax of the “function(d)” (looks like SQL, but mixing that with the other portions is throwing me a bit).

[prasath17 (Guru)]

@Bobby_J - If you share the sample excel sheet and expected output format, we can try something share the output?

[Bobby_J (BOB)]

Thanks for your help!
Example.xlsx (9.7 KB)

Here’s a rough example - again, the string manipulations are arbitrary, as are the number of columns (ideally this can support any manipulation that can be done in Excel (using equivalent “string” methods) and can support 3-6 columns).

[prasath17 (Guru)]

Hi @Bobby_J - Here you go … Please verify is this is expected result…Once you confirmed , I will shared the sample xaml for you

To answer your question to your other post, yes Invoke code(LINQ query Inside) is similar to For Each, It take each row from the datatable and iterates it. So it will be much much faster when you are dealing with large data…

Hope this helps…

[Bobby_J (BOB)]

Yes, that looks like the correct input - Thank you very much!

I am very interested to see how your example works - as I mentioned, more advanced methods are much more capable than what I was working with and I have only limited exposure to the broader .net framework/capabilities, but something like this could be useful for quite a few automations.

[prasath17 (Guru)]

@Bobby_J - Please find the workflow attached…
Concatenate_Bob.zip (47.3 KB)

Invoke Code Used:

dt.AsEnumerable().ToList().ForEach(Sub(row) row("Desired Output (Static)")=row("Invoice Number").ToString.PadLeft(10,"0"c)+"_"+row("Year").ToString.Substring(2,2)+"_"+row("Month").ToString.PadLeft(2,"0"c))

Please this against your real data and let me know, how quick this gives the result.

@Bobby_J

I have only limited exposure to the broader .net framework/capabilities

Same here. I come from mainframe background, but i do have lot of coding experience. I always search the forum and created lot of practice exercises when I see something interesting.

[Bobby_J (BOB)]

Good News - I updated for production data and the code runs and processes the table in about 2 seconds! Now that I had confidence in the code (and poked around in the data table) I was able to cleanse the data and resolve an error I kept on getting around the invoked code.

Sincere Thanks! This was a huge help! Hopefully the next time this pops up I’ll be able to devise the code snippet myself!

[prasath17 (Guru)]

@Bobby_J - Glad to know that it worked well.

Thanks for sharing the amazing news.

[Bobby_J (BOB)]

Although there may be other reasons, sanitize your data, folks. At least one reason was I was reading in extra blank rows with null/blank values…

[prasath17 (Guru)]

@Bobby_J - we can use the LINq query to filter the non blank first and then pass the filtereddt to the invoke code.

[Bobby_J (BOB)]

One day - I’ll be there. Fortunately for now, this was just rows at the bottom for totals/subtotals. I only responded to my original post so that hopefully unrelated issues with the solution don’t get lost if people search it.

Your solution worked perfectly - but without some amount of digging, I would never have discovered the issue around my inputs, and why 99% of rows had the right value… and then fail.