Skip to main content

Data Driven Testing with QTP Using Excel ADO

We all are aware of externalizing test data from test scripts in test automation.
We often stumble upon using excel as test data file, and I have seen crude ways of handling excel.
i.e. - Creating Excel Application object
Opening work book and fetching data
Disadvantages of this approach are detailed on Microsoft website here - http://support.microsoft.com/kb/278973/EN-US/
In a gist following above mentioned approach would be process heavy and would make script execution (especially in case of long running test suites) slow
Another crude way is to use sheet-name (which is still acceptable), Row and Column number (i.e. - (2, 5)), Row and Column range (B5, B10). Using row and column identifier is fragile as insertion of new data set in excel would break existing test scripts.
A better approach to overcome these is to use ExcelADO along with name of Range in Excel.
ExcelADO overcomes process over head and using Range to overcome fragility over Row and Column Range.
In excel an individual cell or collection of cell could be referenced with name. To do this - Select required cell range and use 'Insert' > 'Name' > 'Define' Menu
Following are couple of functions to do same using named range cell in excel file -


'‘**************************************************
'Function Name : Func_GetExcelData
'Description : This function is used to return value from named cell range in excel file
' This method should be used when getting data from individual cell and not from collection fo cell
'Input parameters : strFileName - Name of test data file
' strQuery – SQL Query to execute

'Date of Creation : 19th Feb 2010
'‘**************************************************

Function Func_GetExcelData(strFileName, strQuery)

Dim cn
Dim rs

Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strFileName & ";" _
& "Extended Properties=""Excel 8.0;HDR=Yes;"""


Set rs = CreateObject("ADODB.Recordset")
rs.Open strQuery, cn

'Remove Carriage Return and return the value
dataString = Split (rs.GetString, Chr(13))
Func_GetExcelData = dataString(0)

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Function



'‘**************************************************
'Function Name : Func_GetExcelRangeData
'Description : This function is used to return multiple values from named cell range in excel file
'Input parameters : strFileName - Name of test data file
' strQuery – SQL Query to execute

'Date of Creation : 26th Feb 2010
'‘**************************************************
Function Func_GetExcelRangeData(strFileName, strQuery)

Dim cn
Dim rs

Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strFileName & ";" _
& "Extended Properties=""Excel 8.0;HDR=Yes;"""


Set rs = CreateObject("ADODB.Recordset")
rs.Open strQuery, cn

'Remove Carriage Return and return the value
dataString = rs.GetString
Func_GetExcelRangeData = dataString

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Function




'‘**************************************************
'Function Name : Func_SetExcelData
'Description : This function is used to set data in one cell of excel file
'Input parameters : strFileName - Name of test data file
'strQuery – SQL Query to execute
'strVal - Value to be set
' intIndex - Index where value is to be updated

'Date of Creation : 19th Feb 2010
'‘**************************************************
Function Func_SetExcelData(strFileName, strQuery, strVal)

Dim cn
Dim rs

Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strFileName & ";" _
& "Extended Properties=""Excel 8.0;HDR=Yes;"""


Set rs = CreateObject("ADODB.Recordset")
rs.ActiveConnection = cn

rs.LockType = 2
rs.Source = strQuery
rs.Open

rs.MoveFirst
rs.Fields(0).Value = strVal
rs.Update

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Function

Labels:

Comments

Post a Comment

No spam only genuine comments :)

Popular posts from this blog

Using chrome console to test xPath and css selectors

Note: If you are new to java and selenium then start with selenium java training videos .       Since the advent of selenium there have been many plugin to test xPath / css selectors but you don’t need any of them if you have chrome browser. Using Chrome console you can test both xPath and css selectors. Launch website to be tested in chrome browser and hit F-12 and you would see chrome console opened in lower pane of application - Hit escape key and console would open another pane to write element locators - And now you can start writing xPath or css selectors in chrome console and test them - The syntax for writing css id - $$(“ ”) And hit the enter key. If your expression is right then html snippet of the application element corresponding to the css selector would be displayed - If you mouse over the html snippet in chrome console then it would highlight the corresponding element in application - If you want to clean console of previously wri
Read more

XPath and single quotes

I had tough time dealing with XPath and single quote. Though W3C recommends using ' to escape it but I never got it working, let me know if any of you get through. Came across this blog and found that “concat” could be used in this situation. So original XPath expression is – //meta[@name=’DESCRIPTION’][@content=’Tester’s Test’] This is some thing which certainly fails as single quote in “Tester’s” marks it at end of string and then XPath blows up, next trial was – //meta[@name=’DESCRIPTION’][@content=’Tester''s Test’] This does not work despite w3c recommendation! And then I used concat function and split the string as – concat(‘Tester’,”’”,’s Test’) NOTICE that single quote is kept in double quote while other characters are kept in single quotes. So XPath looks as – //meta[@name=’DESCRIPTION’][@content=concat(‘Tester’,”’”,’s Test’)] And this works charm.
2 comments
Read more

Return only first or last element from webelements collection

Note: If you are new to java and selenium then start with selenium java training videos .     We often come across situation when there are multiple elements on a page and we probably like to exercise only a few of them using selenium webdriver. May be just first and last element. For example on a search result page we may like to click on only first and last link and not all. This is when Iterables API comes handy. (By the way I am assuming that you have already completed watching selenium training videos :)). Once we have collection of web element then we can use Iterables to get only first or last element as following - Consider that we fetch collection of element as - List< WebElement > webElements = getDriver().findElements(By. id ( "htmlID" ));   Now we can get the first web element from this collection as -  WebElement firstElement = Iterables. getFirst (webElements,  getDriver().findElement(By. id ( "defaultElement" )));   Herein second
2 comments
Read more