I trying to reconcile two excel files, Here is the vb.net code and logic I’m currently following.
For Each row1 As DataRow In dt1.Rows
For Each row2 As DataRow In dt2.Rows
If row1(“Segment”).ToString() = row2(“Segment”).ToString() AndAlso row1(“Element”).ToString() = row2(“Element”).ToString() AndAlso row1(“record”).ToString() = row2(“record”).ToString() AndAlso row1(“Field”).ToString() = row2(“Field”).ToString() AndAlso row1(“Mapping comments”).ToString() = row2(“Mapping comments”).ToString() Then
row1(“Matched_info”) = row2(“Mapping comments”)
Else
row1(“Matched_info”) = “Not matched”
End If
Next
Next
I’m getting not matched , even though the data is being matched while comparing the excels manually
Can you check by using .Trim at the end of each .tostring, usually while using equals for comparing strings, the extra spaces won’t allow the strings to be matched.
@Karthik23
in such mismatches we would recommend trimming the data for the compares to avoid surrounding space blocking the match
Also we would recommend to rewrite the nested for each, as it will update the row1(MatchInfo) for each row2 row. We do feel that it was intended to check if there is a match dt2 or not
Maybe it could be due to the Capitals letter, white spaces or something like that
give a try adding a .Trim() and .ToUpper() methods after your .ToString(), this in order to compare the data with the same sintax, code should be like this
For Each row1 As DataRow In dt1.Rows
For Each row2 As DataRow In dt2.Rows
If row1("Segment").ToString().Trim().ToUpper() = row2("Segment").ToString().Trim().ToUpper() AndAlso
row1("Element").ToString().Trim().ToUpper() = row2("Element").ToString().Trim().ToUpper() AndAlso
row1("record").ToString().Trim().ToUpper() = row2("record").ToString().Trim().ToUpper() AndAlso
row1("Field").ToString().Trim().ToUpper() = row2("Field").ToString().Trim().ToUpper() AndAlso
row1("Mapping comments").ToString().Trim().ToUpper() = row2("Mapping comments").ToString().Trim().ToUpper() Then
row1("Matched_info") = row2("Mapping comments")
Else
row1("Matched_info") = "Not matched"
End If
Next
Next
Use the “For Each Row” activity to loop through each row in dt1. Inside the first “For Each Row” activity, add a nested “For Each Row” activity to loop through each row in dt2. Within the nested “For Each Row” activity, use an “If” activity to check if the specified column values match.
Add a condition to the “If” activity with the following expression:-
If the condition is true, assign the value of row2(“Mapping comments”) to row1(“Matched_info”) using the “Assign” activity:
row1(“Matched_info”) = row2(“Mapping comments”)
If the condition is false, assign “Not matched” to row1(“Matched_info”):
row1(“Matched_info”) = “Not matched”
Although the logic that you have written is working,
you gotta break the internal loop whenever your rows have matched.
Use a Break statement if you have written this in a code, or use a Break activity if it’s in sequence.
As mentioned the recommendations of trimming, using break also have a closer look on this line from your code
AND
As it is checked, that row1(“Mapping comments”).ToString() = row2(“Mapping comments”).ToString()
so row1/row2 do have the same value
Therefore: row1(“Matched_info”) = row–>1<—(“Mapping comments”) should do the same
With this in mind we can take the use case as a MatchCase Scenario and can remodel it with only 1 Loop
Loop over row1 in dt1AsEnumerable().
arrCheckCols = new String(){“Segment”,“Element”,“record”,“Mapping comments” }
@ppr , I tried adding break. However what’s happening is, the loop is breaking after each iteration. The row iteration of first foreach is being incremented, where as the loop iteration of second for each is being restarted from 1 row.
Please refer to below screenshot for the code that I’m using.
Hi @adiijaiin , I’m breaking the internal loop using break activity. However the iteration of the first for each being incremented, whereas the iteration of second iteration is being restarted i.e., starting from first row again.
I’m looking for a solution to compare the data of two datatables row by row.
try below code but just change the column values only along with trim and upper or lower
For Each row1 As DataRow In dt1.Rows
For Each row2 As DataRow In dt2.Rows
If row1("Sno").ToString() = row2("Sno").ToString() AndAlso row1("City").ToString() = row2("City").ToString() AndAlso row1("Location").ToString() = row2("Location").ToString() Then
row1("Matched") = "Matched"
Exit For
Else
row1("Matched") = "Not matched"
End If
Next
Next
Yoichi
Raja.G
mkt.scott4