Update column values of datatable based on the lookup result from another excel sheet

[MANISHA_V_ARORA (Miss Arora)]

For a column, we can have combination of different values as in here, it can be BLV, ABC,XYZ etc.

We need to lookup into another excel file what ABC mean for example, get that result and paste here in c_prop_cd wherever we find ABC in this column in one go…

How this can be done considering we have huge amount of data…

[Divyanshu_Divyanshu]

@MANISHA_V_ARORA
If BLV - Something
ABC - Nothing
XYZ - Anything

Are you looking this to update your file that value provide by me can be anything, i have just used for sample so that i can go ahead code give you the xaml

[MANISHA_V_ARORA (Miss Arora)]

Hi @Divyanshu_Divyanshu,

Sheet 1 is part of one excel file and sheet 2 is part of another excel file.

Here in sheet 1 under c_prop_cd we see BLV,CLV as seen in image1, we need to search for BLV,CLV in another excel file as seen in sheet2. For example, the matched row of BLV, pick the value from c_prop_desc column , here BLV means B Las Vegas pick this and paste in sheet 1, wherever we find BLV replace it with B Las Vegas.

[Divyanshu_Divyanshu]

@MANISHA_V_ARORA

Please Find attached
ExcelMatchingFilling.zip (13.3 KB)
code.
It is coded as you have explained your scenario.

[MANISHA_V_ARORA (Miss Arora)]

Thank you so much @Divyanshu_Divyanshu .

This solution is fine if we have less amount of data.

I did try this earlier.

I want know how do we do it for huge amount of data in one go. I can think of other option is vlookup in excel?

[Divyanshu_Divyanshu]

Ohk will share the faster method if you large data

[MANISHA_V_ARORA (Miss Arora)]

Any update?

[prasath17 (Guru)]

@MANISHA_V_ARORA - please check this post…it might help

LINQ query to find data present in another excel if data matches with corresponding value

@sandyk the join type is currently not derivable from the description as it is not specifying on how to handle non matching city codes from dt1 in dt2. The join datatable would help to do it with an out of the box uipath activity. Doing it with LINQ could look like this: (From d1 In dt1.AsEnumerable Join d2 In dt2.AsEnumerable On d1("City_Code").toString.Trim Equals d2("City_Code").toString.Trim Select d2).CopyToDataTable Find starter help here: Match_1Col_ResultKeyColMatchVal.xaml (8.6 K…

[NIVED_NAMBIAR (NIVED N)]

Hi @MANISHA_V_ARORA
you can try this way of updating with multiple data with single line of code

first read the excel sheet1 and store in dt1
read the excel sheet2 and store in dt2

Now use Invoke code activity by passing below arguments as parameters like below

dt1 -In/Out
dt2-In

Now use the below code in Invoke code activity

dt1.AsEnumerable().ToList().ForEach(Sub(row) row(“c_prop_cd”)= If(dt2.AsEnumerable().Where(Function(r) r(“c_prop_cd”).ToString.Equals(row(“c_prop_cd”).ToString)).Count<>0, dt2.AsEnumerable().Where(Function(r) r(“c_prop_cd”).ToString.Equals(row(“c_prop_cd”).ToString))(0)(“c_prop_desc”).ToString,row(“c_prop_cd”).ToString)

You can try this out and let me know if it works

Regards,
Nived N
HappyAutomation

[Divyanshu_Divyanshu]

@MANISHA_V_ARORA
Please use @NIVED_NAMBIAR code and let him know this is the fastest way to achieve that

[MANISHA_V_ARORA (Miss Arora)]

There is syntax error that am getting

[Divyanshu_Divyanshu]

@MANISHA_V_ARORA can you post your code here ,it seems you are missing some brackets

[MANISHA_V_ARORA (Miss Arora)]

@NIVED_NAMBIAR

dt1.AsEnumerable().ToList().ForEach(Sub(row) row(“c_prop_cd”)= If(dt2.AsEnumerable().Where(Function(r) r(“c_prop_cd”).ToString.Equals(row(“c_prop_cd”).ToString).Count<>0, dt2.AsEnumerable().Where(Function(r) r(“c_prop_cd”).ToString.Equals(row(“c_prop_cd”).ToString)(0)(“c_prop_desc”).ToString,row(“c_prop_cd”).ToString)

@NIVED_NAMBIAR @Divyanshu_Divyanshu

dt1.AsEnumerable().ToList().ForEach(Sub(row) row(“c_prop_cd”)= If(dt2.AsEnumerable().Where(Function(r) r(“c_prop_cd”).ToString.Equals(row(“c_prop_cd”).ToString).Count<>0, dt2.AsEnumerable().Where(Function(r) r(“c_prop_cd”).ToString.Equals(row(“c_prop_cd”).ToString)(0)(“c_prop_desc”).ToString,row(“c_prop_cd”).ToString)

[MANISHA_V_ARORA (Miss Arora)]

This is the arguments that i have passed…

@Divyanshu_Divyanshu @NIVED_NAMBIAR

[MANISHA_V_ARORA (Miss Arora)]

@Divyanshu_Divyanshu @NIVED_NAMBIAR

I have selected as VB as the language.
Please note my project is in C#

[MANISHA_V_ARORA (Miss Arora)]

[NIVED_NAMBIAR (NIVED N)]

Hi @MANISHA_V_ARORA

Please check my response

I had corrected error

I had forget to put ) ( since it was long code )

[Divyanshu_Divyanshu]

@MANISHA_V_ARORA

The below code does not have any error, plss use this

dt1.AsEnumerable().ToList().ForEach(Sub(row) row(“c_prop_cd”)= If(dt2.AsEnumerable().Where(Function(r) r(“c_prop_cd”).ToString.Equals(row(“c_prop_cd”).ToString)).Count<>0, dt2.AsEnumerable().Where(Function(r) r(“c_prop_cd”).ToString.Equals(row(“c_prop_cd”).ToString))(0)(“c_prop_desc”).ToString,row(“c_prop_cd”).ToString))

[MANISHA_V_ARORA (Miss Arora)]

Thank you so much @NIVED_NAMBIAR @Divyanshu_Divyanshu