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

Comments

Popular posts from this blog

Selenium Tutorial: Ant Build for Selenium Java project

Ant is a build tool which could be used to have your tests running either from command line or from Hudson CI tool. There is detailed documentation available for ant here but probably you need to know only a little part of it for you selenium tests. The essentials which are needed to know are: Project Target (ant execution point and collection of tasks) Tasks (could be as simple as compilation) And there would usually be following targets for Selenium tools - setClassPath - so that ant knows where you jar files are loadTestNG - so that you could use testng task in ant and use it to execute testng tests from ant init - created the build file clean - delete the build file compile - compiles the selenium tests run - executes the selenium tests Here is my project set up for ant -

Verify email confirmation using Selenium

Note: If you are new to java and selenium then start with selenium java training videos .     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

Real Time JMeter Result Using Backend Listener

Since JMeter 2.13 Backend Listener has been available to create real time graph of JMeter Test. Following tutorial explain the entire process in detail. At the end of this tutorial you would be able to create JMeter Live Test Result dashboard similar to following - This tutorial borrows information from many sources and my own experiments with JMeter live reporting dashboard. I have added source of information wherever applicable But before we can build such a snazzy JMeter Live Reporting dashboard we need to understand two more components - influxDB (a time series database) and Grafana Dashboard This is a big tutorial, so take deep breath :-) and follow on. Once you complete set up specified in this tutorial then you can watch JMeter Training Video Tutorial to watch this in action. What is Time Series Database? A time series is a sequence of data points , typically consisting of successive measurements made over a time interval . Examples of time ...