Dt problem

[manoj_verma1 (manoj verma)]

Hi,

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

[ppr (Peter Preuss)]

it can be done with:

• join datatable activity and postprocessing the join result
• LINQ

[sahad.kk]

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

linq.zip (1.7 KB)

Eg:
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
DT2).ToArray().CopyToDatatable
in an assign activity.
note:dt1 and dt2 is two read range output.

Thanks
Regards
Sahad kk

[ppr (Peter Preuss)]

@sahad.kk

(From DT1 In dt1.AsEnumerable() Join DT2 In dt2.AsEnumerable() On
DT1(“slipnumber”).ToString() Equals DT2(0).ToString() Select
DT2).ToArray().CopyToDatatable
in an assign activity.
note:dt1 and dt2 is two read range output.

Thanks
Regards
Sahad kk

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

Story line of Join Data Table

Dear innovator’s, All we know that there is a Join Data table activity in UiPath.If we get any matching scenarios with unique value will approach join data table activity instead of filter/loop/array matching activity to find an exact match. Join Data Table: [image] Combines rows from two tables by using values common to each other, according to a Join rule, which is specified in the Join Type property. Sample Table: [image] INNER Join: The INNER JOIN keyword selects records that have m…

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

[kumar.varun2 (Varun Kumar)]

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)
).CopyToDataTable

Hope it helps.

[manoj_verma1 (manoj verma)]

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

[manoj_verma1 (manoj verma)]

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

[ppr (Peter Preuss)]

give a try on

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

or another LINQ

[manoj_verma1 (manoj verma)]

@sahad.kk

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
DT2).ToArray().CopyToDatatable
in an assign activity.
note:dt1 and dt2 is two read range output.

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

[ppr (Peter Preuss)]

@ppr

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

A defensive handling we can do by

Assign Activity
LHS: Result | List(Of DataRow)
RHS:
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

[ppr (Peter Preuss)]

assign activity has two fields:

[manoj_verma1 (manoj verma)]

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

[ppr (Peter Preuss)]

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

[manoj_verma1 (manoj verma)]

@ppr

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

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

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

[ppr (Peter Preuss)]

ensure following:

AsEnumerable is not a member of 'System.Data.Datatable'

Hi @lucas.mcgonagle EDIT 20.10 fixed this issue for newly created projects. You can fix it by adding the Assembly Reference manually in your xaml file: [image] Simply open the file in Notepad and create new line with this String: <AssemblyReference>System.Data.DataSetExtensions</AssemblyReference> Then reopen your project and the error should be gone. Please be careful while doing so and always keep a backup.

[manoj_verma1 (manoj verma)]

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

[ppr (Peter Preuss)]

@manoj_verma1
Here we go:
Variables:

(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

Inspection:

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

[kumar.varun2 (Varun Kumar)]

Hi @manoj_verma1

To achieve this

@manoj_verma1

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

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

[manoj_verma1 (manoj verma)]

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

[kumar.varun2 (Varun Kumar)]

Hi @manoj_verma1

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