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
[âŚ]
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.
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âŚ
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).
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).
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âŚ
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.
Please this against your real data and let me know, how quick this gives the result.
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.
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!
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.