Hello, I need to speed up the process which has lookup and write cell activities.
I have 2 excel with more than 1000 rows and I need to look up, in each row, the value that belongs to each person (firstly I read both files into datatables and then I lookup the value of each row of the main datatable).
I know that I can perform the process with a LINQ query but I don’t know how to build it
Can you provide input and exact output for the same ?
Also can you provide the SS or Excel files for the same
Er Pratik Wavhal
I agree with @Pratik_Wavhal that more information is needed. However, one thing stuck out to me - do you need to be using ‘write cell’ activity? Constantly writing to excel is likely what is causing the slowdown. Instead, you should do all of the calculations within the datatable, then use a write range at the end to re-write the entire datatable back to excel at one time. That would significantly speed things up
In a way is like this: I have to write, for each ID, the value of the first table in the second table. I do it with a lookup by ID and then write cell where it writes the value in the column of the second table
You can use the merge datatable activity. First remove the blank ‘value’ column from your datatable, then merge dt2 into dt1. Now dt1 will have the columns: ID, NAME, VALUE, DATE, ADRESS, TEL, MAIL. You can re-order the columns by using the datacolumn.setordinal property if you’d like e.g. assign dt1.column(“Value”).SetOrdinal = dt1.columns.count - 1
This will be the fastest and easiest way.
The other way I would recommend is to use the inbuilt excel Vlookup() function instead of calculting within uipath. To do this you would iterate through each row and assign the
row.item("Value") = "=Vlookup(A" + RowNum.ToString + ",nameofworkbook'sheetname'$A$2$C$5,3,0)
This is just putting in an auto-incrementing excel formula into each cell. the RowNum should be tied to the for each output RowIndex (but first put RowIndex + 2 so it starts at 2 rather than 0). Then when you write wrange back to excel, it includes the vlookup formula for each value instead of passing over the exact values
I’ll try what you sugest and then inform you if I can solve it!
Thanks a lot
you can not use LINQ to update the data table (directly), instead you can use below code (use it inside Invoke Code activity)
dt1 = First table
dt2 = second table where we need to upate the VALUE column
For each rw as DataRow in dt2.Rows
rw("VALUE") = (from row in dt1 where row("ID").ToString = rw("ID").ToString Select x = row)(0)("VALUE").ToString
Let us know if this works for you
@Dave with the merge it doesn’t work because I have too many columns and then I have another excel where I have to compare data
@AkshaySandhu should I have de dt as arguments to use the code? Becasuse I write the code but it says that the dt is not declared
yes you need to pass the both dt as arguments
Based on the screenshots it should be fine. Be sure to include the .AddSchema property to your merge datatable method.
Otherwise why not go the other route and just fill in the datatable with excel formulas and have excel do the calculations for you?
I have updated my answer please check.
Hi Dave! I can’t use the merge activity because I am working in 4 different excel with many tables, rows and columns. Also, I need to do it in UiPath. That’s why I need to solve it with a QUERY Link. Thanks for all the ideas, I really appreciate them!
I have this error now: Invoke code: Exception has been thrown by the target of an invocation.