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

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 written element selectors then just hit ctrl+L keys and chrome console would be empty again. Pro
Read more

Verify email confirmation using Selenium

Email confirmation seems to be integral part of any registration process. I came across an application which lets you provide your email address. You can follow the sign up link in you mail and then complete the registration process. Lets consider we provide GMail address for it. Now if were to use only Selenium then we would have to follow following steps - Launch GMail using Selenium; Some how search for new mail in the list of available mails; Some how click on it; Parse the mail message; Get the registration link; Follow up with registration process What do you think of an approach in which you can
14 comments
Read more

Return only first or last element from webelements collection

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 argument -   (getDriver().findElement(By. id ( "defaultElement" )))    in the me
2 comments
Read more