Skip to main content

Using ExcelADO to fetch data from excel

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 is a function which returns collection of items from named range 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

################################################
This Function takes file name and cell range name as arguments. Call to this function would return String with collection of items. These items then can then be separated using split function in vbs.

MsgBox fExcelCellADO("Path to Excel", "MyRange")

I will be more than glad to know a better approach than this.

~ T

n.b I have seen problem while fetching data from just one named cell in office 2003, it works perfect with office 2007

Comments

Popular posts from this blog

Distributed Load Testing with JMeter

Distributed Testing with JMeter When one JMeter client is not able to offer amount of threads required for load testing then distributed testing is used. In distributed testing - One instance of JMeter client can control number of JMeter instances and collect data from them Test plan does not need to be copied to each server, the client sends it to all servers note - JMeter will run all the threads on all the servers, hence 100 threads on 5 JMeter server would pump 500 threads in total. If many server instances are used, the client JMeter can become overloaded and so the client network connection. This has been improved in latest versions of JMeter by switching to Stripped modes, but you should always check that your client is not overloaded When Client (master) and Server (slave) nodes are on same network (no SSH required) Configure Client Node Herein client is referred as the machine controlling test execution on other JMeter nodes. This is also referred

Appium and android mobile app automation

Next appium and Android mobile app automation video tutoria l is live. If you are new to appium then please check - appium-tutorial This video tutorial covers - Start vysor (Just for this session and not mobile automation :)) Start appium and start appium inspector Desired Capabilities platformName - Android deviceName - L2N0219828001013 (as seen on "adb devices") Saved Capability Sets Start Session Scan app elements using appium inspector Get appPackage and appActivity using "APK info" app Install "APK info" app and open app whose appPackage and appActivity are required i.e. calculator Check top section of app icon com.android.calculator2 is app package com.android.calculator2.Calculator is app activity testng.xml file settings for running Android app tests Test details com.seleniumtests.tests.mobile.AndroidAppTest and CalculatorScreen class View beautiful STF test report  

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