Dt problem


I have two excel what i want to do is i want to check the slip number from excel 1 and check if that i available in excel2 if not available then save them in different DT
excel1.xlsx (8.5 KB)
excel2.xlsx (8.4 KB)

excel1 has multiple column with column name but required is slipnumber
excel2 do not have column name but its fixed row 0

it can be done with:

  • join datatable activity and postprocessing the join result
  • LINQ

Hi @manoj_verma1 ,
please use this zip file…
i tried this excel with linq.
i got the output

linq.zip (1.7 KB)

1.read 1st excel
2.read second excel(without add headers)
3. use this linq
(From DT1 In dt1.AsEnumerable() Join DT2 In dt2.AsEnumerable() On
DT1(“slipnumber”).ToString() Equals DT2(0).ToString() Select
in an assign activity.
note:dt1 and dt2 is two read range output.

Sahad kk

In addition to above also have a look on the different Join Types

In your scenarion you will detect such records by

  • Join Type: Left Join
  • dtLeft: dt1, dtRight: dt2
  • Join Col - the slip number columns

Filter / Detection - dt2 from Join Pair is null

Tge corresponding LINQ would look like this:

dtResult =

(From d1 In dtData1.AsEnumerable
Group Join d2 In dtData2.AsEnumerable 
On d1("slipnumber") Equals d2(0) Into gj = Group
From g In gj.DefaultIfEmpty
Where isNothing(g)
Select r = d1).CopyToDataTable
1 Like

Hi @manoj_verma1

If you only want Slip Numbers then you can follow the below approcach


	From item in dt_Excel1.AsEnumerable.Select(Function(r) r("slipnumber")).ToArray.Except(dt_Excel2.AsEnumerable.Select(Function(r) r(0)).ToArray)
	select dt_Output.Rows.Add(item)

Hope it helps.

Hi this is breaking when there is one slipnumber or no slipnumber matching

@ppr @kumar.varun2 @sahad.kk how can i remove the records from excel1 that are found

give a try on

dt1.AsEnumerable.Except(dtFoundRecords.AsEnumerable, DataRowComparer.Default).CopyToDataTable

or another LINQ

i tried out this one its working but failing when 1 or no matcching records found
getting errro as source contains no data rows

A defensive handling we can do by

Assign Activity
LHS: Result | List(Of DataRow)
dt1.AsEnumerable.Except(dtFoundRecords.AsEnumerable, DataRowComparer.Default).toList

then check within an if acitvity
Result.Count > 0
Then: dtResult = Result.CopyToDataTable
Else: dtResult = dt1.Clone

assign activity has two fields:

ohh sorry i was asking for list of data row that you mentioned

Create in advance a variable of Datatype: List(Of DataRow)

1 Like

except is coming as not a member of system.data.datatable

DT1.Except( DT2, DataRowComparer.Default).CopyToDataTable

ensure following:

<AssemblyReference>System.Data.DataSetExtensions</AssemblyReference> its already present

Here we go:


(From d1 In dt1.AsEnumerable
Group Join d2 In dt2.AsEnumerable 
On d1("slipnumber") Equals d2(0) Into gj = Group
From g In gj.DefaultIfEmpty
Where isNothing(g)
Select r = d1).ToList

dt1.AsEnumerable.Except(dtNoMatch.AsEnumerable, DataRowComparer.Default).toList


Find starter help here:
manoj_verma1.xaml (11.0 KB)

Hi @manoj_verma1

To achieve this

you can simply filter using the “where” clause like this

dt_Excel1.AsEnumerable.Where(Function(Row) Not dt_Excel2.AsEnumerable.Select(Function(r) r(0)).ToArray.Contains(Row("slipnumber"))).CopyToDataTable

Hi varun this gave me all of slipnumbers instead i want only the one that is there in both

Hi @manoj_verma1

For the above input files shared, can you also share the output excel file.