if there are 2 excel files, the data from 1 st excel file should be get into second file but only matched column data should be get.
You can use the below LINQ Expression in assign activity,
- Assign -> Output_DT = DT1.AsEnumerable.Where(Function(X) DT2.AsEnumerable.Any(Function(Y) Y("Column name").ToString.Equals(X("Column name").ToString))).CopyToDataTable()
X(“Column name”) - Here give the column name of DT1 with in braces.
Y(“Column name”) - Here give the column name of DT2 with in braces.
Hope it helps!!
there are 35 columns, all should be done at a time
Do you mean, you want to compare each and every column in DT1 and DT2… @anjani_priya
yes every column should compare
Okay @anjani_priya
Compare each column value between two DataTables (DT1 and DT2) and store the filtered rows where all column values are equal in Output_DT, then use the below LINQ Expression,
- Assign -> Output_DT = DT1.Clone()
- Assign -> Output_DT = DT1.AsEnumerable().Where(Function(row1) DT2.AsEnumerable().Any(Function(row2) DT1.Columns.Cast(Of DataColumn).All(Function(col) row1(col.ColumnName).ToString() = row2(col.ColumnName).ToString()))).CopyToDataTable()
Hope it helps!!
1 Like
Assign
To: matchingColumns
Value:
(From col1 In dataTable1.Columns.Cast(Of DataColumn)()
Join col2 In dataTable2.Columns.Cast(Of DataColumn)()
On col1.ColumnName Equals col2.ColumnName
Select col1.ColumnName).ToList()
Assign
To: resultTable
Value:
(From row1 In dataTable1.AsEnumerable()
Join row2 In dataTable2.AsEnumerable()
On row1("ID") Equals row2("ID") ' Adjust the joining column
Select resultTable.LoadDataRow(matchingColumns.Select(Function(col) row1(col)).ToArray(), False)).CopyToDataTable()
should compare all the columns.
Hie @anjani_priya i"m attaching screenshot for comparing two excel sheet modify with your column name and also more columns.
datatable1
datatable2
use Assign activity and the variable is also datatable
compare output
cheers
Happy Automation
1 Like
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.