Compare two DTs and match each cell

Help Studio
, , ,
1

Hi
I need to comapre datatable from sql and other datatable from UI
and finally track the result to excel.

I have used joined table, but not much helping.
I want each cell to compare and last must display either matching or not

2

Write that sql datatable and Ui datatable to one excel file with different sheets , send as a dummy files so that we can work on it.
And tell which columns to compare.

3 Likes
3

test.xlsx (7.9 KB)
pls find the same.
The number of columns, number of rows and column names and row names are not constant
I need to compare all rows
sql data and Ui data no of columns are same.
Note: there will huge data.

column1 of sql compared to column1 of Ui etc etc

4

Please refer this,

2 Likes
5

I don’t want like common or uncommon rows. I want output as shown

output
output1306Ă—398 52 KB

6

can anyone suggest how to delete columns of dt

7

@KarthikBallary Can you tell us what did you mean by ,The number of columns, number of rows and column names and row names are not constant ? :sweat_smile:

8

You could use the Join DataTable activity:

Perform Join Operation
a. Left: This will return all records in the Source dataset which are not matched in the Comparison dataset. Including any new records.
b. Right: This will return all records in the Comparison dataset not matched in the Source dataset. Including any new records.
c. Both: This will return only records which are matched between the Source and Comparison datasets.

You could use VBA also. Any differences, cell by cell will be highlighted and an output file created. This can be easily adapted to compare rows by unique field also:

Function Main () As String

Dim comparisonDims As Collection
Dim iRow As Long
Dim iCol As Long
Dim OutputFile As String
Dim ColumnLetter As String
Dim dt As String

'set up file name to save to and path
dt = Format(CStr(Now), "YYYY_MM_DD_hhmm")
'enter your path to create the output file
OutputFolderPath = ""
OutputFile = OutputFolderPath + dt + "_file.xlsx"

'set up the Source Sheet items to use
Set sourceDims = UnknownRange("enter your source sheet name")
Set comparisonDims = UnknownRange("enter the sheet to compare against")

'validate the Sheets to make sure data is structured correctly
If TypeName(sourceDims(1)) = "String" Then
    Main = sourceDims(1)
    
ElseIf TypeName(comparisonDims(1)) = "String" Then
    Main = comparisonDims(1)

'no errors so begin creating the data elements required for the analysis    
Else
    varSouSheet = sourceDims(1)
    souRng = sourceDims(2)
    lastRowSou = sourceDims(3)
    lastColSou = sourceDims(4)
    varCompSheet = comparisonDims(1)
    compRng = comparisonDims(2)
    lastRowSou = comparisonDims(3)
    lastColSou = comparisonDims(4)

	'Activate the comparison sheet to work on
	Worksheets("Comparison").Activate

	'Cycle through all rows and columns
	For iRow = LBound(varCompSheet, 1) To UBound(varCompSheet, 1)
			
			For iCol = LBound(varCompSheet, 2) To UBound(varCompSheet, 2)
					
				If varSouSheet(iRow, iCol) = varCompSheet(iRow, iCol) Then
								
					' Cells are identical. Do nothing.
								
				Else
							
					ColumnLetter = Split(Cells(1, iCol).Address, "$")(1)
					
					Range(ColumnLetter + CStr(iRow)).Interior.Color = RGB(255, 143, 143)
				
				End If
				
			Next iCol
								
	Next iRow

	'Prepare Final excel file for output
	Worksheets("Source").Select
	Cells.Select
	Cells.EntireColumn.AutoFit
	Cells.EntireRow.AutoFit

	Worksheets("Comparison").Select
	Cells.Select
	Cells.EntireColumn.AutoFit
	Cells.EntireRow.AutoFit

	Worksheets("Sheet1").Delete

	Application.DisplayAlerts = False
	ActiveWorkbook.SaveAs _
		Filename:=OutputFile, _
		FileFormat:=xlOpenXMLWorkbook, _
		CreateBackup:=False
	Application.DisplayAlerts = True

End If
    
End Function

Function UnknownRange(sheetName As String) As Collection

Dim Ouputs As Collection
Set Outputs = New Collection

Worksheets(sheetName).Activate

If WorksheetFunction.CountA(Cells) = 0 Then
Outputs.Add ("Error. There is no range to be selected. No cells contain any values.")
Set UnknownRange = Outputs
Exit Function
Else
Dim FirstRow&, FirstCol&, LastRow&, LastCol&
Dim myUsedRange As Range
FirstRow = Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByRows).row

On Error Resume Next
FirstCol = Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column
If Err.Number <> 0 Then
Err.Clear
Outputs.Add ("Error. There are horizontally merged cells on the sheet that should be removed in order to locate the range. Please unmerge all cells.")
Set UnknownRange = Outputs
Exit Function
End If

LastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).row
LastCol = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
Set myUsedRange = Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol))

Outputs.Add myUsedRange
Outputs.Add myUsedRange.Address(0, 0)
Outputs.Add LastRow
Outputs.Add LastCol

Set UnknownRange = Outputs

End If

End Function
2 Likes
9

I mean dt from sql- the column name, number of rows= dt from UI(column names, no of rows)

10

I used join table- there I can find full, inner, left.
Thanks for detailed code. How do I write or call this code in UiPath

11

Place the code into a string and feed it to the Invoke VBA Activity which itself should be within an Excel Application Scope activity:

image

For the Datatable Join activity, apologies. The Join Datatable does the job, however, I was referring to this, which aligns to my instructions:

2 Likes
12

Ok, I will check and let u know. Thank you

14

Input datatable mustv have same structure-what it means?

15

When you are joining two tables, they should have the same structure

However, as you are reading data from SQL, you may want to get the data into excel first, then read it from there to be sure the table metadata is the same:

1 Like