QTP Compare Webtable Row with Excel ROW

Question

I have a Webtable with 10 rows and similar table in Excel sheet with 100 rows of data.

I need to check whether the 10 rows of webtable data is present in excel and also need to print in which row in excenter image description hereel it is matching.

Answer 1

You haven't provided any details about your webtable, so I am assuming it has only column with multiple rows.if it has multiple initiate an array and then compare it. It will return true if all the values in the excel matches with the webtable.

Function Chktbl (coulmnname,sheetname,tblname)
Chktbl = True 
Dim marray,excelop,weblistcount,l,m,k
l = datatable.LocalSheet.GetRowCount
For m = 1 To l
datatable.LocalSheet.SetCurrentRow m
excelop=trim(Datatable.Value(""&coulmnname,""&sheetname))
webcount = Browser("name:=.*").Page("title:=.*").Webtable("name:="&tblname).Rowcount
For k = 1 to webcount
marray =  Browser("name:=.*").Page("title:=.*").Webtable("name:="&tblname).getcelldata (k,1)
If excelop= "" Then
Exit For    
End If
If Ucase(excelop) = Ucase(trim(marray)) Then

Exit For
ElseIf k = webcount Then
reporter.ReportEvent micFail,"No match","Fail"
Chktbl = False
Exit For 

End If

Next 

Next

End Function

Answer 2

First, read into an array your rows of data from the WebTable

Dim Table, CheckArray
Set Table = Browser("YourBrowser").Page("YourPage").WebTable("YourTable")
ReDim CheckArray(Table.RowCount - 1)  ' Arrays index from 0, table rows from 1
For iLoop = 1 to Table.RowCount
    CheckArray(iLoop-1) = Table.GetCellData(iLoop, 1)
Next

Once you have this array you can loop through each value and check against the Excel table. There are a few ways you could achieve this.

1. Load the excel table as a datatable and iterate through the rows looking for the matches
2. Directly query the file using SQL to check if the values in the web table exist
3. Access the file via the COM approach and use Excel's Find functionality to locate your data.

The least complex of these is 1, so I'll detail that approach here:

Datatable.Import("YourExcelFilePathHere.xls", Global)
iRowsToCheck = DataTable.GetRowCount
For iLoop = 0 to Ubound(CheckArray)
    bFound = False
    For iSubLoop = 1 to iRowsToCheck
        DataTable.SetCurrentRow(iSubLoop)
        If CheckArray(iLoop) = Datatable.Value("ColumnName") Then
            bFound = True
            Exit For
        End If
    Next
    If bFound Then
        CheckArray(iLoop) = CheckArray(iLoop) & " was found"
    Else
        CheckArray(iLoop) = CheckArray(iLoop) & " was not found"
    End If
Next

This imports your excel file, and for each element in the webtable array, checks for it in the excel table. If found, it exits and marks the array item as found. If it completes without setting bFound to True then it wasn't found and updates accordingly.

You can then output the content of your array as you please to get your results.