Datatable

5/4/2015:

Various operations involved in DataTable:

Source: http://learnhpquicktestprofessional.blogspot.com/2013/02/data-table-methods.html  and

http://www.softwaretestingstuff.com/2008/11/datatable-in-qtp.html

Working With Data Table.

DataTable is just like Microsoft Excel file. The DataTable contains a global sheet and a separate sheet for each action in the test. So we two types of sheets in data table.

1. Global sheet

2. Local sheet

Global sheet is available to all actions in the test and local sheet is available to the corresponding action. The best practice is to use local data sheet while parameterizing the test.

Globalsheet: Globalsheet in any script is unique, Globalsheet is used in order to store values in the sheet and the values stored in Globalsheet can be used in all the actions. For storing a value in the global sheet we use the following function:

Datatable.Value(“Fieldname”,”Global”) = Value

Note: If you dont mention, QTP will take from Global sheet by default.

For using the values from the global sheet we use the following function:

Value = Datatable.Value(“Fieldname”,”Global”)

Localsheet: Each and every action in the script will have its own Localsheet, name of the Localsheet of a particular action is same as the action name. For using the data from the Local sheet we use following function:

Datatable.Value(“Fieldname”,dtlocalsheet)

Or

Datatable.Value(“Fieldname”,”Sheetname”)

Note: You could mention the numerical value of the column(or sheet) or name of the column(or sheet)

Types of DataTable:
Design Time DataTable: While creating editing the test scripts, you enter data directly in the data table displayed there. You can view data table by selecting View >> Data Table option. This data table is called Design Time DataTable.

Runtime DataTable: The data table used while running the test is called Runtime DataTable. The data in the runtime data table may be same as design time data table and/or can be entered by importing data from excel sheet, text file etc. The runtime data table can be seen in the Test Results window when run session is ended.

Parameterization using Data driven Wizard: The Data Driver enables you to quickly parameterize several (or all) property values for test objects, checkpoints, and/or method arguments containing the same constant value within a given action.

You can choose to replace all occurrences of a selected constant value with a parameter, in the same way that you can use a Find and Replace All operation instead of a step-by-step Find and Replace process. QuickTest can also show you each occurrence of the constant so that you can decide whether or not to parameterize the value.

If any property of an object is taken, that property can be parameterized using Data driven Wizard, different steps for parameterization using Data driven Wizard is as follows:

• Go to tool bar option Tools and select Data Driver option

• We will get a list of all the properties of all the objects which can be parameterized (i.e. for all the objects where a value for a property is given in the script)

Step-by-step Parameterization: Enables you to view the current values of each step containing the selected value. For each step, you can choose whether or not to parameterize the value, and if so, which parameterization options you want to use, the Next button is enabled when you select this option, this will enable us to select an other instance of the constant selected and parameterize that instant, it will continue the same process till all the instances of the given value are done.

Parameterize all: Enables you to parameterize all occurrences of the selected value throughout the action. When you select this option the Parameter details area is enabled. The Finish button is enabled when you select this option.

Here Parameter tab can be selected using the button given at the “Parameter” radio button, and in the next screen select the datatable Field from where the property for which the value belongs is parametererized, click on “Finish” button thus all the instances of the value selected for parameterization will be parameterized.

DataTable Parameter:

Each column in global/local sheet of datatable is called a Parameter. We can rename the parameter just by double clicking on the column header and giving the name to it. Data can be entered in the column/parameter simply by clicking on the cell and entering the value.

Parameterizing the test:

Instead of using hardcoded values in the script, we can store the test data in the data table and give reference of same in the script which help perform the same operations on the AUT with multiple sets of data. This all is done through data table methods and properties.

DataTable Methods:

These methods are applied on either DataTable object or DTSheet object or DTParameter object. Here are the methods and their properties:

AddSheet: This method is used to add a new sheet to the data table.

Syntax: DataTable.AddSheet(“Sheet Name”)

Eg: DataTable.AddSheet(“MySheet”)

Note: All the methods of data table object will have impact only during Runtime

Datatable.AddSheet "New1" --- this one added a new sheet 'New1' to the Datatable during Runtime. Thus we can make out that Datatable could have more than one local sheet.
 How to get the name of the sheet???

DeleteSheet: This method is used to delete a sheet from the data table.

Syntax: DataTable.DeleteSheet(“” or )
Eg: DataTable.DeleteSheet(“Global”)
(OR) DataTable.DeleteSheet(1)

Import: This method is used to import the contents of an excel file into data table. While importing from an excel file we need to ensure the following things:
1.The number of sheets in excel file & in data table should be equal or more.
2.The sheet names may not be same. (We can modify the sheet names after importing)
3.The parameters names should be same.
Syntax: DataTable.Import(“”)
Eg: DataTable.Import(“D:\QTP\input.xls”)
Steps to import the data manually:
1.Right click --> Import from file --> Click on OK
3.Select the file name from which the data needs to be imported
4.Click on Open

Note: File should be in .xls format(saved in Excel 2003 format)

ImportSheet: This method is used to import the data from the specific sheet of the excel file to the specific sheet of the data table.
Syntax: DataTable.ImportSheet “”,,
Eg: DataTable.ImportSheet “D:\QTP\input.xls”,2,1

Syntax: Datatable.Importsheet(“nameofExcelfilewithpath”, “nameofthesourcesheet”, “nameofthedestinationsheet”)

Steps to import manually:
1.Right click in the required sheet of data table
2.Select Sheet --> Import --> From File --> Click on OK
3.Select the required file from which the data needs to be imported
4.Select the required sheet name from the dropdown
5.Click on OK

Export: This method is used to export the contents of the data table to an excel file. Even if the file is not available, it will create the file & export. If the file is available it will over ride.
Syntax: DataTable.Export(“”)
Eg: DataTable.Export(“D:\QTP\output.xls”)

Steps to export manually:
1.Right click in the data table
2.Select File --> Export
3.Provide the File Name --> Click on Save

ExportSheet: This method is used to export the contents of a specific sheet of the data table to the excel file.
Syntax: DataTable.ExportSheet “”,
Eg: DataTable. ExportSheet “D:\QTP\output.xls”,2

Syntax: Datatable.Exportsheet(“nameofExcelfilewithpath”, “Nameofthesheet”)
Steps to import manually:
1.Right click in the required sheet of data table
2.Select Sheet --> Export
3.Provide the File Name --> Click on Save

GetCurrentRow: This method is used to identify the current active row number
Eg: ActRow=DataTable.GetCurrentRow
msgbox ActRow
This function is used to get the current row of a particular sheet. When an action is run by using run setting as “run on all rows”, that action will run as many times as the number of rows in the excel sheet of the action, in order to find the row for which action is running currently we use GetCurrentRow function.

Syntax: Datatable.GetSheet(“Sheetname”).GetCurrentRow

GetRowCount: This method is used to get the number of records available in the data table.
Eg: RowCnt=DataTable.GetRowCount
msgbox RowCnt
Note: Methods like GetCurrentRow, GetRowCount, etc defaultly works on global sheets.
This function is used to get the no of rows in a particular sheet.
Syntax: Datatable.AddSheet(“Sheetname”).GetRowCount


LocalSheet: This method is used to work with the corresponding local sheet from which action we execute the script.
Eg: RowCnt=DataTable.LocalSheet.GetRowCount
msgbox RowCnt

GetSheetCount: This method is used to get the number of sheets available in the data table.
Eg: SheetCnt=DataTable.GetSheetCount
msgbox SheetCnt

SetCurrentRow: This method is used to set the focus to the specified row of the ‘data table-global sheet’.
Syntax: Datatable.SetCurrentRow()
Eg: Datatable.SetCurrentRow(17)

SetNextRow: This method is used to set the focus to the immediate next row of the current active row.
Syntax: DataTable.SetNextRow

SetPrevRow: This method is used to set the focus to the previous row of the current active row.
Syntax: DataTable.SetPrevRow

Value: This method is used for 2 purposes.
1. To read the value from the data table.
Syntax: Val=DataTable.Value(“”,“”)
Eg: Val=DataTable.Value(“Num1”,2)
msgbox Val
Note: We will not define the row number. By default it will pick the value of the current focus row.

2. To write the value into the data table.
Syntax: DataTable.Value(“”,“”)= “Value”
Eg: DataTable.Value(“Result”,”Action1”)=“Pass”
(OR) DataTable.Value(3,2)=“Pass”

Script to perform DDT manually:

DataTable.Import("D:\Practise\QTP Testing\input.xls")
RC=DataTable.GetRowCount
msgbox RC
For i=1 to RC
DataTable.SetCurrentRow(i)
VbWindow(“VbWindow”).VbEdit(“VbEdit”).SetDataTable.Value (“num1”,1)
VbWindow(“VbWindow”).VbEdit(“VbEdit_2”).SetDataTable.Value(“num2”,dtGlobalSheet)
VbWindow(“VbWindow”).VbButton(“Add”).Click
ExpVal=Datatable.Value(“ExpRes”,1)
ActVal=VbWindow(“VbWindow”).VbEdit(“VbEdit_3”).GetROProperty(“text”)
DataTable.Value(“ActRes”,1)=ActVal
If ExpVal=ActVal Then
DataTable.Value(“Result”,1)=”Pass”
Else
DataTable.Value(“Result”,1)=”Fail”
End If
Next
DataTable.Export(“D:\Practice\QTP Testing\output.xls”)

DataTable Object - PropertiesGlobalSheet Property:Description: Returns the first sheet in the datat table.
DataTable.GlobalSheet.AddParameter “Time”,”08:00”

LocalSheet Property:
Description: Returns the current (active) sheet of the data table.
MyParam=DataTable.LocalSheet.AddParameter “Time”,”09:00”

RawValue Property:
Description: Returns the raw value of the cell in the specified parameter of the current row of the data table. The raw value is the actual string written in the cell before the cell is computed, such as the actual text from a formula.
FormulaVal=DataTable.RawValue(“Date”,”ActionA”)

Value Property:
Description: Data table default property. Retrieves or sets the value of the cell in specified parameter and the current row of the data table.
DataTable.Value(2,3)=”New York”

DTParameter - Methods

DtSheet.AddParameter:   (Adds a new column.)

DataTable.GetSheet(“dtGlobalSheet”).AddParameter “NewColumnName”,”FirstRowVaule”

What if you just mention: DataTable.GetSheet(Env) in the code. Does it mean that you are making sheet Env as active.???? 

 

DtSheet.GetParameter:

Description:  Retrieves the specified parameter from the run time data table sheet.

DataTable.GetSheet(“My Sheet”).GetParameter(“Destination”)

DtParameter – Properties

Name Property:

Description: Retrieves the name of the parameter in run time data table.

ParamName=DataTable.LocalSheet.AddParameter(“NewColumnName”,”Row1Value”).Name

 

RawValue Property:

Description: The raw value of the cell in the current row of the parameter in the run time data table. The raw value is the actual string written in the cell before the cell is computed, such as the actual text from a formula.

The following example uses the RawValue property to find the formula used in current row of the Date column in the ActionA sheet of the run time data table.

The statement below runs the Value:=Now()

FormulaVal=DataTable.GetSheet(“ActionA”).GetParameter(“Date”).RawValue

 

Value Property:

Parameter default property. Retrieves or sets the value of cell in the current row of parameter in the run time data table.

DataTable.GetSheet(“ActionA”).GetParameter(“Destination”).Value=”New York”

ValueByRow Property:

Retrieves the value of the cell in the specified row of the parameter in the run time data table.

DataTable.GetSheet(“ActionA”).GetParameter(“Destination”).ValueByRow(4)

 

DTSheet Methods

AddParameter Method:

Add the specific parameter(column) to the sheet in Run Time data table, sets the value of the first row to the specified value, and returns the parameter so that you can directly set or retrieve properties of new parameter in same statement.

Variable=DataTable.AddSheet(“My Sheet”).AddParameter(“Time”,”08:00”)

DeleteParameter Method:

Deletes the specified parameter from the sheet in run time data table.

DataTable.GetSheet(“My Sheet”).DeleteParameter(“Time”)

GetCurrentRow Method:

Returns the row number of the current(active) row in the run time data table sheet.

Rownum=DataTable.GetSheet(“My Sheet”).GetCurrentRow

Reporter.ReportEvent 1, “Row Number”, rownum

GetParameter Method:

Returns the specified parameter from the run time data table sheet.

DataTable.GetSheet(“My Sheet”).GetParameter

GetParameterCount Method:

Returns the total number of parameters(columns) from the run time data table sheet.

Paramcount=DataTable.GetSheet(“My Sheet”).GetParameterCount

Reporter.ReportEvent 2, “There are”, &paramcount, “columns in this sheet”

GetRowCount Method:

Returns the total number of rows in the longest column in the run time data table sheet.

rowcount=DataTable.GetSheet(“My Sheet”).GetRowCount

Reporter.ReportEvent 2, “There are”, &rowcount, “active rows in this sheet”

SetCurrentRow Method:

Sets the specified row as the current row in the run time data table sheet.

DataTable.GetSheet(“My Sheet”).SetCurrentRow(2)

SetNextRow Method:

Sets the row next to the current row as the new current row in the run time data table sheet.

DataTable.GetSheet(“My Sheet”).SetNextRow

SetPrevRow Method:

Sets the row above the current row as the new current row in the run time data table sheet.

DataTable.GetSheet(“My Sheet”).SetPrevRow

DTSheet Properties:

Name Property:

Returns the name of the run time data table sheet.

Sheetname=DataTable.LocalSheet.Name

Reporter.ReportEvent 1, “The active sheet is”, sheetname

- Pathfinder:
Locate method:
Returns the full file path that UFT uses for the specified relative path (resolves the path) based on the folders specified in the Folders search list (Tools > Options > GUI Testing tab > Folders pane) of the Options dialog box.
For more details on the Folders search list, see the HP Unified Functional Testing User Guide.

Syntax

PathFinder.Locate (RelativePath)

Argument	Type	Description
RelativePath	String	The relative path you want to resolve.

Example

The following example uses the Locate method to find the path in which the MyEnvFile.txt file is located.
y=PathFinder.Locate ("MyEnvFile.txt")