Hi All,
I have a two excel in first excel column name as “name” & second excel column name as" Employee name"
Table 1
Under column we have a value as below
Aa
Bb
Cc
Table 2
Column names
111-aa
222-cc
333-bb
We need to spilt the column after - and consider only aa cc and bb and we need to match both column value irrespective of any order. If match we need to continue else we need pass exception as business. Please provide the suggestions thanks.
Read Range (Excel Read Range) - Output: dtTable1 - Sheet: “Sheet1”
Read Range (Excel Read Range) - Output: dtTable2 - Sheet: “Sheet1”
For Each Row (ForEachRow) - DataTable: dtTable1
Assign - ExtractedValueTable1 = row(“name”).ToString()
For Each Row (ForEachRow) - DataTable: dtTable2
Assign - ExtractedValueTable2 = row(“Employee name”).ToString().Split("-"c)(1)
If ExtractedValueTable1.Equals(ExtractedValueTable2) OrElse ExtractedValueTable2.Equals(ExtractedValueTable1)
Then
// Match found - implement your business logic here
Log Message - "Match found for " & ExtractedValueTable1
Else
// No match - handle exception or implement alternative logic
Log Message - "No match found for " & ExtractedValueTable1
For Each Row in dtTable1
For Each Row in dtTable2
Assign: employeeName2 = row(“Employee name”).ToString()
employeeName2=employeeName2.Split("-"c)(1).Trim
if:
row(“name”).ToString().Trim=employeeName2
else
Try catch
Throw:New BusinessRuleException(“Your Exception”)
Catch
Log message:“The Match is not found”
Hi krishna,
The above conditions is working only if the value matches exactly to table 2.
Where as in my conditions aa(table 1) can be anywhere in table 2… There the above conditions fails…
Please suggest alternate solutions
Thanks
Try this
InputDt.AsEnumerable().Select(Function(row) row.Field(Of String)("name")).Any(Function(name) InputDt.Columns.Cast(Of DataColumn)().Any(Function(column) column.ColumnName.Split("-"c).Last() = name))
Hi @vaishali
Follow the below steps -
→ Use read range workbook activity to read the First excel and store in a datatable variable called dt1.
→ Use another read range workbook activity to read the second excel and store in a datatable variable called dt2.
→ After read range use the for each row in datatable activity to iterate the each row in the dt1.
→ Inside for each take an assign activity to store the first row value of dt2.
- Assign -> SecondExcelvalue = dt2.Rows(0)("Employee name").ToString.Split("-")(1).ToLower
→ Take an If activity to check the condition
- Condition -> CurrentRow("name").ToString.ToLower.Equals(SecondExcelValue.ToString)
→ Don’t insert any activity inside Then block
→ Insert the activities for the Business exception in Else block
→ Out of the If condition take an assign activity to delete the first row in dt2.
- Assign -> dt2 = dt2.AsEnumerable.Skip(0).CopyToDataTable
Check the below workflow for better understanding,
2024.xaml (18.7 KB)
Excel 1 -
Excel 2-
Output -
Hope it helps!!
