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

columndata

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…

@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

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.

@MANISHA_V_ARORA

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

1 Like

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?

Ohk will share the faster method if you large data

Any update?

@MANISHA_V_ARORA - please check this post…it might help

1 Like

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

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

1 Like

There is syntax error that am getting

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

@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)

This is the arguments that i have passed…

@Divyanshu_Divyanshu @NIVED_NAMBIAR

@Divyanshu_Divyanshu @NIVED_NAMBIAR

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


Hi @MANISHA_V_ARORA

Please check my response

I had corrected error

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

1 Like

@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))

image

2 Likes

Thank you so much @NIVED_NAMBIAR @Divyanshu_Divyanshu :slight_smile:

2 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.