Compare two different data tables through linq

Help Studio
1

Hello All,

in the attached excel file sheet1 and sheet 2 are the two data tables In sheet1 there is column “Code” so i need to match with sheet2 column name “Code” if data is match then i need to write in “check” column in sheet 1 but “check column” data present in sheet2. if not match then leave it blank. As you can see in sheet 3. could you please help me to write it linq query,
EXcel.xlsx (10.0 KB)

2

Hi @pankajj

Read the two input sheets by read range activity like dt1 and dt2

Create a datatable for output using buuld datatabpe activity like Out_dt

Out_dt = (
From row1 in dt1
Join row2 in dt2
On Cstr(row1(“code”))Equals.Cstr(row2(“code”))
Select.Out_dt.Rows.Add(row1(“column1”),row1(“column2”),row1(“columnN”),row2(“column1”),row2(“column2”),row1(“columnN”))
).CopyToDataTable

Thanks
Robin

3

Hi Robin,

It showing error. could you please help me on that.

error
error1920×1080 121 KB

4

Hi @pankajj
Put it like this
On Cstr(row1(“code”)).Equals(Cstr(row2(“code”)))

and Select.Out_dt.Rows.Add(row1(“column1”),row1(“column2”),row1(“columnN”),row2(“column1”),row2(“column2”),row1(“columnN”)

here i mentioned column1 , column2 and columnn for you reference only. kindly change the column names as per your document row1(“columname”) means datatatable1(“Firstcolumn”) and
row2(“columname”) means datatatable2(“Firstcolumn”)

Hope it solves your issue

thanks
robin

5

Hi @Robinnavinraj_S

I tried with that but it is showing error.

error2
error21920×1080 110 KB

6

the LINQ join condition LIne has to use an EQUALS as following:

On Cstr(row1("code")) Equals Cstr(row2("code"))

7

Hi @ppr

I wrongly put syntax for inner join. Now only I saw the thread clearly
we cannot use inner join for this scenario
kindly go through the description and input excel attached by @pankajj

Thanks
Robin

8

Hi @ppr ,

Could you please help me on that.

9

when starting with such dataprocessing a pairing of the Join DataTable activity and some postprocesing (with or without LINQ) is well balanced approach.

The LINQ only approach would implement a Left Join along with an ItemArray construction.

dtResult = dt1.Cone

dtResult =

(From d1 In dt1.AsEnumerable
Group Join d2 In dt2.AsEnumerable
On d1("Code").toString.Trim Equals d2("Code").toString.Trim  Into gj = Group
From g In gj.DefaultIfEmpty
Let mtc = If( isNothing(g), "", g("Check").toString)
Let ra = New Object(){d1(0), d1(1),d1(2),d1(3), mtc}
Select r=dtResult.Rows.Add(ra)).CopyToDataTable
10

Hi @pankajj,

Please follow the below steps,

  1. Use Build Datatable, and create the number of columns which you required in the resultant excel. [OutputExcelResult]
  2. Read both sheet through Workbook ReadRange. [MailDT,MailExcelDT]
  3. Use Assign activity,
    OutputExcelResult =
(From d1 In MailDT.AsEnumerable
Group Join d2 In MailExcelDT.AsEnumerable On d1(2) Equals d2(1) Into tk = Group
From t In tk.DefaultIfEmpty
Select dd = {d1(2), d1(1) ,If(isNothing(t), "", t("Check").toString) }
Select OutputExcelResult.Rows.Add(dd)).CopyToDataTable
  1. Use Workbook Write range and pass OutputExcelResult as the input.

I hope your query is solved. Try it once, and let us know

For reference, I will attached the workflow.

Main.xaml (8.8 KB)

Regards,
@90s_Developer