Compare two different data tables through linq

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)

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


Hi Robin,

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

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


Hi @Robinnavinraj_S

I tried with that but it is showing error.

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

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

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


Hi @ppr ,

Could you please help me on that.

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

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)