Need a Linq expression for the following case

[Sudharsan_Ka]

Hella all,

I have two excel files Input.xlsx and output.xlsx

I need too update the values based on one column

task will be like

• Add two new column in the output excel(That is done)
• From output.xlsx file get the value of col “FCODE” and check on input.xlsx file and compare it with column “Furn.” and get the cod and name values and fill in the output if the FCODE is not available enter “#N\A”

Check out this input and output file
Input.xlsx (8.2 KB)
Output.xlsx (8.7 KB)

I have tried with the following workarounds
Method 1 - Used formula in excel and autofill,the file has 40000 rows it is taking much time. (This is fine but i dont know how to make the bot until it is processed)
Method 2- Used LookUp and it throws error

@ashwin.ashok @ppr @lakshman @Nithinkrishna

This is urgent , help on this guys.

Regards
Sudharsan

[Nithinkrishna]

@Sudharsan_Ka

ck on input.xlsx file and compare it with column “F

Hey @Sudharsan_Ka

Here it is…

PFA - nmnithinkrishna_ExcelJoin_23Apr2022_1500.zip (15.9 KB)

Hope that helps.

Thanks
#nK

[Srini84 (Srinivas)]

@Sudharsan_Ka

Try as below

(From d1 In dtOutput.AsEnumerable
Group Join d2 In    dtInput.AsEnumerable On d1("FCODE").ToString.Trim  Equals d2("Furn.").Tostring.Trim Into gj = Group
From g In gj.DefaultIfEmpty 
Select ra = {
d1("FCODE"),
If(isNothing(g), "#NA",g("Cod ")),
If(isNothing(g), "#NA",g("Name"))}

below is the output

Hope this may help you

Thanks

[Sudharsan_Ka]

Hi @Srini84 , @Nithinkrishna Actual input and output will have plenty of column so i need to just use the two data table for comparing and adding each and every row in the new tempDT(Build Datatable) which has only two column which is Furn. and Name

Condition was wrong one which i gave before and sorry for that ,So i need to compare the FCode with the cod in the input column and get the furn. and Name values and add them in the tempDT

After completing i will add the values in the actual excel file
Output.xlsx (8.9 KB)
Input.xlsx (8.2 KB)

Can you give me expression for this case

Regards
Sudharsan

[Nithinkrishna]

Hey @Sudharsan_Ka

You need to just change the column name in the sample code shared.

Thanks
#nK

[Sudharsan_Ka]

Yes @Nithinkrishna I have done that but you have specified all the columns right in your expression

[Nithinkrishna]

Hey @Sudharsan_Ka

Yep, that part is to populate the values.

Thanks
#nK

[Sudharsan_Ka]

Thank you so much both of you for now lookupdatatable works perfectly @Srini84 @Nithinkrishna

WIll see how long it works

Regards
Sudharsan

[postwick (Paul)]

Just so you know, Linq is not faster than For Each etc. (if that’s why you are trying to avoid just doing this with regular activities)

[Sudharsan_Ka]

Actually @postwick now i have used with normal activities like foreach and lookupdatatable and it works fine

Regards
Sudharsan