Likewise, Microsoft Excel testers can read input data dynamically at runtime from UFT DataTable to parametrize input fields. Datatable in UFT makes it very easier for testers to create data-driven test cases that can be used to run an Action multiple times. By default, each action in UFT has its own Datatable but it can be accessed from any action within a test. Datatable is loaded with amazing functionalities and it’s up to you how you leverage its benefits in your automation test scripts. There are two types of DataTable in UFT.
- Local DataTable − Each action has its own local DataTable, which can also be accessed across actions. Whenever an action is created UFT adds a local sheet for the specific action. To know more about action please refer to my post on actions Everything about Actions in UFT.
- Global DataTable − Each test has one global data sheet that is accessible across actions.
The DataTable is accessible from the Data Tab of the lower pane in UFT.
The automation test script is executed as many times as many records are there in the datatable for that particular action. It means if there are 10 records in the datatable of an action of a Test, that action will be executed 10 times. Usually, we do not want this to happen. It is can be controlled by changing UFT Run Settings. Go to File -> Settings -> Run (Tab) as shown below and set run from row data row and to data row to 1.
You can write repeated steps inside a loop in the test script and iterate the datatable in UFT to read all rows.
Operations On UFT DataTable
UFT provides three types of objects to access and perform various kinds of operations on a UFT Datatable.
Sr.No | Object Type And Description |
---|---|
1 | Data Table Object. Provides methods to perform operations on run-time data table object only. |
2 | DTParameter Object Methods. Provides methods to perform operations on parameter (column) in a sheet in the run-time data table. |
3 | DTSheet Object Methods. Provides methods to perform operations on a sheet in the run-time data table. |
DataTable Object
DataTable Object Methods
The following methods are associated with Datatable Object.
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
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
Few Examples of Datable Object Methods
Consider the following Table for Datatable for the below examples.
AddSheet: The method is used to add a sheet at runtime. Once the execution gets over runtime added sheets won’t be available for any use.
1 |
DataTable.AddSheet "Sheet1" |
Output:
GetSheet: It returns the specified sheet from the run-time data table.
1 |
DataTable.GetSheet("Sheet1").GetRowCount 'Returns the Sheet1 from the run-time data table. |
GetRowCount: It returns the total number of rows in the longest column of a sheet in the data table. The following example uses the GetRowCount method to find the total number of rows in the longest column(the column having maximum records) of the MySheet run-time datasheet and displays it on the message box.
1 2 |
numOfRows=DataTable.GetSheet("MySheet").GetRowCount Msgbox numOfRows 'Message box will displays 3 |
GetSheetCount: It returns the total number of sheets in the run-time data table.
1 2 |
sheetcount = DataTable.GetSheetCount Msgbox sheetcount 'Message box will displays 3 as there are three sheets in the DataTable |
SetCurrentRow: It sets the specified row as the current (active) row in the run-time data table. The first row is numbered 1. The following example set the current row value to 2 and retrieve the value from Environment column for the second row and displays its value in the message box.
1 2 3 4 |
DataTable.GetSheet("Global") DataTable.SetCurrentRow(2) 'Setting current row to 2 strEnv = DataTable.Value("Environment","Global") Msgbox strEnv 'Message box displays UAT |
DataTable Object Properties
The following properties are associated with Datatable Object.
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]) |
Few Examples of Datable Object Properties
Consider the following DataTable.
Value: How to read data from DataTable in UFT
The following example returns the Global Sheet, sets the row value to two, and gets the value of the Amount column.
1 2 3 4 |
DataTable.GetSheet("Global") DataTable.SetCurrentRow(2) 'Setting current row to 2 valAmt = DataTable.Value("Amount","Global") Msgbox valAmt 'Message box displays 150 |
Value: How to set a value into the UFT DataTable Sheet at runtime
The following example returns the Global Sheet, sets the row value to two, and sets the value “D5340” in the ItemNo column.
1 2 3 |
DataTable.GetSheet("Global") DataTableSetCurrentRow(2) 'Setting current row to 2 DataTable.Value("ItemNO","Global")= "D5340" |
RawValue: How to read raw value from DataTable in UFT
The following example retrieves the raw value of the cell in row number two in the Amount column.
1 2 3 4 |
DataTable.GetSheet("Global") DataTable.SetCurrentRow(2) 'Setting current row to 2 valAmt = DataTable.RawValue("Amount","Global") Msgbox valAmt 'Message box displays C3*D3 that is formula written in the cell |
DTParameter Object
DTParameter Object Properties
Property Name | Description | Syntax |
---|---|---|
Name | Returns the name of the parameter in the run-time datatable. | DTParameter.Name |
RawValue | Returns the raw value of the cell in the current row of the runtime datatable. | DTParameter.RawValue |
Value | Retrieves or sets the value of the cell in the current (active) row of the parameter in the run-time datatable. | DTParameter.Value |
ValueByRow | Retrieves the value of the cell in the specified row of the parameter in the run-time datatable. | DTParameter.ValueByRow (RowNum) |
Few Examples of DTParameter Properties
Name: How to get Column name of UFT DataTable
The following example will add a parameter in the local sheet and return the value that has been given to the parameter.
1 2 |
strParamname = DataTable.LocalSheet.AddParameter("Food", "Burger").Name Msgbox strParamname ' Mesage box displays Food |
ValueByRow: How to retrieve the value of the cell in the specified row using the parameter name
1 2 |
Msgbox DataTable.GetSheet("Global").GetParameter("ItemNo").ValueByRow(2) 'Message box displays B220 |
DTSheet Object
DTSheet Object Methods
Method Name | Description | Syntax |
---|---|---|
AddParameter | Adds the specified parameter (column) to the sheet in the run-time data table and sets the value of the first row to the specified value | DTSheet.AddParameter(ParameterName,Value) |
DeleteParameter | Deletes the specified parameter from the sheet in the run-time data table. | DTSheet.DeleteParameter(ParameterID) |
GetCurrentRow | Returns the row number of the current (active) row in the run-time Data pane sheet. | DTSheet.GetCurrentRow |
SetCurrentRow | Sets the specified row as the current (active) row in the run-time data table. | DTSheet.SetCurrentRow(RowNumber) |
GetRowCount | Returns the total number of rows in the run-time DataTable | DTSheet.GetRowCount |
GetParameter | Retrieves the specified parameter from the run-time Data pane sheet. | DTSheet.GetParameter(ParameterID) |
SetNextRow | Sets the row after the current (active) row as the new current row in the run-time Data pane sheet. | DTSheet.SetNextRow |
SetPrevRow | Sets the row above the current (active) row as the new current (active) row in the run-time Data pane sheet. | DTSheet.SetPrevRow |
GetParameterCount | Returns the total number of Columns in the run-time DataTable. | DTSheet.GetParameterCount |
Few examples of DTSheet Object Methods
AddParameter: How to add a new Column in UFT DataTable at runtime
The following example will add a new sheet MySheet and add a new parameter to it
1 2 |
DataTable.AddSheet("MySheet") ParamName=DataTable.GetSheet("MySheet").AddParameter("COLUMN1","VALUE1") |
GetParameterCount: How to count Column numbers in UFT DataTable
The following example will count the number of columns in a UFT DataTable Sheet.
1 2 |
parameterCount=DataTable.GetSheet("Global").GetParameterCount MsgBox parameterCount 'Message box displays 5, Refer above DataTable Snapshot |
DTSheet Object Properties
Property Name | Description | Syntax |
---|---|---|
Name | Returns the name of the run-time data sheet. | DTSheet.Name |
Example: How to get active sheet name from UFT DataTable at runtime
The following example uses the Name method to return the name of the active run-time datasheet.
1 2 |
Sheetname = DataTable.LocalSheet.Name MsgBox Sheetname 'Message box displays active sheet name |
Recommended Posts
- What are new features in the new UFT One Datatable
- 20 Useful VBA Date Functions in UFT You Must Know
- Import Excel File into Datatable in UFT
- Capture Screenshots in UFT and Save them in Word Doc
- Automation Object Model in UFT One (AOM)
- Component Object Model in UFT | DOM | TOM & BOM
- File System Object (FSO) UFT | VBA
- Read, Write and Update Excel File In UFT
- Reading and Updating MS Access Database Using ADODB in UFT
- Read and Update XML File in UFT | VBA
- VBScript Loops: Do Loop, For Loop, For Each, and While Loop
- VBScript MySQL Database Connection in UFT