UFT/QTP DataTable

A DataTable provides a way to create data driven test cases. It's same as Excel sheets and can be used to run an Action Multiple times.

There are two types of DataTable in QTP/UFT:

1. Design-Time DataTable:-

As the name suggest the data table during the script design time is known as design time data table.

Design time datatable are having two types of sheet:-

A)Local Data Table:- Each action has its own private data table also known as local data table which is can also be accessed across actions.The name local data table is somewhat misleading because it's in fact possible to access any action's local data table from any other action.

 B) Global Data Table:- Each test has one global data sheet that is accessible across actions.

Important Point About DataTable:

1. A DataTable is stored as "Default.xls" file in the Test Folder.

2. The same DataTable cannot have duplicate parameter names but we can use same name parameters in different sheets(Global mad Local DataTable)

Difference between Local and Global DataTable:

Global Data Table parameters take data from the Global sheet in the Data Table. The Global sheet contains the data that replaces global parameters in each iteration of the test. By default, the test runs one iteration for each row in the Global sheet of the Data Table.

Local Data Table parameters take data from the action's sheet in the Data Table. The data in the action's sheet replaces the action's Data Table parameters in each iteration of the action. By default, actions run only one iteration.

2. Run-Time Data Table:-  

The run-time datatable contain a copy of the design time data table when a script is executed. It may contain values that are changed during script execution and are presented in the test result summary. The changes made to the data table during the run-time are not saved to design time data table.

Data like parameterized output , checkpoint values , output values  are stored in the Run-time Table. It is an xls file which is stored in the Test Results Folder.  It can also be accessed in the Test Fusion Report.

When to use the Global or a Local DataTable:

Global Sheet is used for Global use, that means what you entered in global sheet that should be used in any action in the Test for example: test1 having 4action. at that time you want to parameterize the some object in all actions with same Test Data. at this times used global sheet.

Action Sheet is used for local use, that means if you want to parameterize the actions individually with independent test data in test, at that time we are use Action Sheet.

DataTable Object Model:-

QTP/UFT provides an object model to access various properties and method in a datatable.

there are three types of obejcts:

DataTable, DTSheet, DTParameter.

1. DataTabel:- gives detailed information about the datatable methods and properties.

Data Table Object Methods:

Method Name	Description	Syntax
AddSheet	Adds the specified sheet to the run-time data table	DataTable.AddSheet(SheetName)
DeleteSheet	Deletes the specified sheet from the run-time data table	DataTable.DeleteSheet SheetID
Export	Exports the Datatable to a new file in the specified location	DataTable.Export(FileName)
ExportSheet	Exports a Specific Sheet of the Datatable in run-time	DataTable.ExportSheet(FileName,SheetName)
GetCurrentRow	Returns the active row of the run-time data table of global sheet	DataTable.GetCurrentRow
GetParameterCount	Returns the number of columns in the run-time data Table of Global Sheet	DataTable.GetParameterCount
GetRowCount	Returns the number of rows in the run-time data table of Global Sheet	DataTable.GetRowCount
GetSheet	Returns the specified sheet from the run-time data table.	DataTable.GetSheet(SheetID)
GetSheetCount	Returns the total number of sheets in the run-time data table.	DataTable.GetSheetCount
Import	Imports a specific external Excel file to the run-time data table.	DataTable.Import(FileName)
ImportSheet	Imports the specified sheet of the specific excel file to the destination sheet.	DataTable.ImportSheet(FileName, SheetSource, SheetDest)
SetCurrentRow	Sets the Focus of the Current row to the Specified Row Number	DataTable.SetCurrentRow(RowNumber)
SetNextRow	Sets the focus of the next row in the run-time data table	DataTable.SetNextRow
SetPreviousRow	Sets the focus of the previous row in the run-time data Table	DataTable.SetPrevRow

Data Table Object Properties:-

Property Name	Description	Syntax
GlobalSheet	Returns the first sheet of the run-time data table.	DataTable.GlobalSheet
LocalSheet	Returns the Active local sheet of the run-time data table.	DataTable.LocalSheet
RawValue	Retrieves the raw value of the cell	DataTable.RawValue ParameterID, [SheetID]
Value	Retrieves the value of the cell in the specified parameter.	DataTable.Value(ParameterID, [SheetID])

2. DTSheet:- gives detailed information about the DTSheet methods and properties.

DTSheet Methods:-

Method Name	Description	Syntax
AddParameter	Adds the specified column to the sheet in the run-time data table.	DTSheet.AddParameter(ParameterName, Value)
DeleteParameter	Deletes the specified parameter from the run-time data table.	DTSheet.DeleteParameter(ParameterID)
GetCurrentRow	Returns the row number of the active row in the run-time Data Table.	DTSheet.GetCurrentRow
GetParameter	Returns the specified parameter from the run-time Data Table.	DTSheet.GetParameter(ParameterID)
GetParameterCount	Returns the total number of Columns in the run-time Data Table.	DTSheet.GetParameterCount
GetRowCount	Returns the total number of rows in the run-time Data Table.	DTSheet.GetRowCount
SetCurrentRow	Sets the Focus on the specified Row of the Data Table	DTSheet.SetCurrentRow(RowNumber)
SetNextRow	Shifts the Focus to the next Row of the Data Table.	DTSheet.SetNextRow
SetPrevRow	Shifts the Focus to the Previous Row of the Data Table.	DTSheet.SetPrevRow

3. DTParameter:-gives detailed information about the DTParameter methods and properties.

DTParameter Object Properties:-

Method Name	Description	Syntax
Name	Returns the name of the parameter in the run-time data table.	DTParameter.Name
RawValue	Returns the raw value of the cell in the current row of the run-time data table.	DTParameter.RawValue
Value	Retrieves or sets the value of the cell in the Active row of the parameter in the run-time data table.	DTParameter.Value
ValueByRow	Retrieves the value of the cell in the specified row of the parameter in the run-time data table.	DTParameter.ValueByRow(RowNum)

How To Import and Export Excel Sheet Into DataTable:-

1. DataTable.Import(FileName):-

DataTable.Import("C:\Users\VMalav\Documents\Project_Work\HP Project\Automation Work\HP TestAutomation\Data Files\SupportHome_Page.xls")

2. DataTable.ImportSheet(FileName, vSrcSheet, vDstSheet):-

vFileName ="C:\Users\VMalav\Documents\Project_Work\HP Project\Automation Work\HP TestAutomation\Data Files\SupportHome_Page.xls"

ImportSheet in LocalSheet :

DataTable.ImportSheet(vFileName, 1, 1) -> Import 1st sheet of excel into global sheet

DataTable.ImportSheet(vFileName, 1, 2) -> Import 1st sheet of excel into Local or Action sheet

3. DataTable.Export(FileName):

DataTable.Export("C:\Run-Time DataTable.xls") -> Export run-time datatable in "Run-Time DataTable.xls" 

4. DataTable.ExportSheet(FileName,  vSrcSheet, [Optional] vDstSheet):-

DataTable.ExportSheet("C:\Run-Time DataTable.xls", 1, 1) -> Export runtime datatable to excel sheet.

How To Read Data from DataTable:-

1. Reading Data From Global DataTable:-

Msgbox DataTable.Value("A", dtGlobalSheet)

or

Msgbox DataTable.Value("A", 1)

or

Msgbox DataTable.Value("A", Global)

or

Msgbox Datatable ("A" , 1 )

or

Msgbox Datatable("A", dtGlobalSheet)

or

Msgbox Datatable("A", Global)

2. Reading Data From Local DataTable:-

Msgbox DataTable.Value("A", dtLocalSheet)

or

Msgbox DataTable.Value("A", 2)

or

Msgbox DataTable.Value("A", "Action1")

or

Msgbox Datatable ("A" , 2 )

or

Msgbox Datatable ("A" , "Action1")

or

Msgbox Datatable ("A", dtLocalSheet)

3. Reading a value from another action's datatable:-

Msgbox DataTable.GetSheet("Action2").GetParameter("A").Value

Msgbox DataTable.GetSheet("Global").GetParameter("A").Value

Msgbox DataTable.GetSheet(1).GetParameter("A").Value

4. Reading a value from external action's datatable:-

Msgbox DataTable.GetSheet("sac [rd]").GetParameter("A").Value

rd - > Test name

sac-> Action name

5. Retrieving a value of the cell from the specified row of the datatable:-

Msgbox (DataTable.GetSheet(2).GetParameter("A").ValueByRow(3))

or

Msgbox (DataTable.GetSheet("Action1").GetParameter("A").ValueByRow(3))

or

Msgbox (DataTable.GetSheet("Global").GetParameter("A").ValueByRow(3))

6. Retrieving a value of the cell in the specified row of the Global or Local datatable:-

Msgbox(DataTable.GlobalSheet.GetParameter("A").ValueByRow(3))

Or

Msgbox(DataTable.LocalSheet.GetParameter("A").ValueByRow(3))

How To Write Data in DataTable:-

1. Adding a value to Global Sheet:-

DataTable.GlobalSheet.AddParameter "A", 1

or

DataTable.GetSheet(1).AddParameter "A", 1

or

DataTable.GetSheet("Global").AddParameter "A", 1

or

DataTable.Value("A", 1)=99

or

DataTable.Value("A", Global)=99

or

DataTable("A", 1)=77

or

DataTable("A", Global)=77

2. Adding a value to Local Sheet:-

DataTable.LocalSheet.AddParameter "A", 1

or

DataTable.GetSheet(2).AddParameter "A", 1

or

DataTable.GetSheet("Action1").AddParameter "A", 1

or

DataTable.Value("A", 2)=99

or

DataTable.Value("A", "Action1")=99

or

DataTable("A", 2)=63

or

DataTable("A", "Action1")=63

Below statement always writes into the Global Sheet:-

DataTable("A")=100

Questions & Answers:-

Q. If the Global Data sheet contains no data and the Local Datasheet contains two rows of data, how many times will the test iterate?

Ans:The test will iterate only once - global iteration.

Q.What are Differences between Design time Data Table and Run-time Data Table?

Ans:

Design Time Data Table:

It  is  viewed  in  the  QTP  Main Test

It  is  created  prior  to  the  test execution

It  represents  data  from  external Sources

Run-Time Data Table:

It is viewed in the QTP Test Result Window

It is created in Test Results after test execution.

It represents a live version of design Time Data table.

Q.Inserting a Call to Action is not importing all columns in Datatable of globalsheet. Why? 

Ans: Inserting a call to action will only Import the columns of the Action called

Example

Consider the following DataTable −

'Accessing Datatable to get Row Count and Column Count
rowcount = DataTable.GetSheet("Global").GetRowCount
msgbox rowcount      ' Displays 4
  
colcount = DataTable.GetSheet("Global").GetParameterCount
msgbox colcount   ' Displays 3
  
DataTable.SetCurrentRow(2) 
val_rate = DataTable.Value("Rate","Global")
print val_rate   ' Displays 7%
  
val_ppl = DataTable.Value("Principal","Global")
print val_ppl  ' Displays 2556
  
val_Time = DataTable.Value("Time","Global")
print val_Time  ' Displays 5