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
