DataTable in QTP

In QTP scripting there are many objects that are used, One of the Objects used in QTP scripting is DataTable Object.

Datatable Object and related Functions: Basic use of Datatable object is to create an object for adding, deleting, updating data of an excel sheet used. Main functions of the datatable object:

  • Importsheet : This function is used to Import sheet into the script, so that all the data in the imported sheet can be used in the script.

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

  • Exportsheet : This function is used to Export sheet from the script, all the data including output values are stored in the default sheet for the action, this sheet is exported and stored in a specified location using this function.

Datatable.Exportsheet(“nameofExcelfilewithpath”, “Nameofthesheet”)

  • Addsheet: Used for adding a sheet into the script file
  • GetRowCount: This function is used to get the no of rows in a particular sheet. Syntax: Datatable.AddSheet(“Sheetname”).GetRowCount
  • GetCurrentRow: 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

Organizing Data Sheets for Global & Local variables: There are two types of sheets in any QTP Script, they are

  • Globalsheet
  • Localsheet

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

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:


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.

Importing and Exporting Datasheets: Importing and Exporting of Datasheets are used for reading, writing and manipulating the data in an excel sheet.

Importing Datasheet: Imports a sheet of a specified file to a specified sheet in the run-time Data Table. The data in the imported sheet replaces the data in the destination sheet (see SheetDest argument).

Note: All the fields in the imported sheets should match with the data used in the script.
There are two functions for Importing, Importing the whole excel file and Importing the sheets in the file.

Function for Importing the whole Excel file:

Imports the file into the script with all the sheets which are there in the excel file.

Note: Sheets in the excel file should match with the Actions in the script to use the data efficiently, since action name and corresponding sheet name should be same.

Import “filenamewithpath”
Function for Importing the sheets from the excel file:
Imports the specified sheet into the script.

Importsheet “filenamewithpath”,”sourcesheetname”, ”destinationsheetname”

Exporting Datasheet: Exports a specified sheet of the run-time Data Table to the specified file.

  • If the specified file does not exist, a new file is created and the specified sheet is saved.
  • If the current file exists, but the file does not contain a sheet with the specified sheet name, the sheet is inserted as the last sheet of the file.
  • If the current file exists and the file contains the specified sheet, the exported sheet overwrites the existing sheet.

Same as Importing, There are two functions for Exporting, Exporting the whole excel file and Exporting the sheets in the file.

Function for exporting the whole Excel file: Exports the file into the script with all the sheets which are there in the excel file.

Export “filenamewithpath”
Function for Exporting the sheets from the excel file:
Exports the specified sheet into the script.

Exportsheet “filenamewithpath”,”sourcesheetname”, ”destinationsheetname”

Also See:

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top