Linq to compare 2 excel sheets and select particular column to create 3rd sheet

Hi Team,

I have a sheet1 with below values

image

Sheet2

image

I need to compare sheet1 and sheet2 and get the matched records and corresponding grade and need to generate sheet3

sheet3

image

Could anyone help how to complete this using linq query?

Thanks,
Ula

you dont need linq, see this:

@Boopathi
Give a try on
(From d1 In dt1.AsEnumerable
Join d2 In dt2.AsEnumerable
On d1(“Name”).toString.trim Equals d2(“Name”).toString.trim
Select d1).CopyToDatatable

d1 = sheet1
d2 = sheet2
use the statement within an assign activity and assign it to e.g dt3 (DataType: DataTable)
Kindly Note: CopyToDataTable throws an exception if returned result is empty (does mean: has no rows)

5 Likes

Hi @ppr
I have one more query. Just consider my sheet1 and sheet2 is exists like this

Sheet1
image

sheet2
image

Want sheet 3 output like this

image

Could you please help me how to tweak the query. i.e i want matched column values in sheet2 also.

Thanks,
Ula

this can be achieve with join databable also. see the attached workflow i had done it for you.
Main.xaml (9.5 KB)

1 Like

@Boopathi

I created a XAML showcasing this scenario with:

  • LINQ
  • Join Datatable Activities

It is good to know both approaches as both has cons and pros
Kindly note:

  • As you will see both approaches requires some additional works / actions additional to the core Join action
  • Join DataTable comes out with a column structure of all combined columns. Thats why you will get the Name_1 column (Coming from second Table Join Column).
  • Join Datatable is a good alternate option as well, but the result requires some column removal. So its not doing all jobs directly without any post workings as sometimes is stated, but this fact not mentioning

Demo XAML here: Ula_TableJoin.xaml (13.0 KB)

1 Like

Thank you very much @ppr and @amit_joshi

1 Like

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