QTP - Data Tables

QTP - Data Tables

DataTable Object

Description

The run-time Data Table.

Note: All methods performed on this object apply to the run-time DataTable object only. Changes to the run-time DataTable object are reflected in the run results, but the design-time Data Table is not affected.

Associated Methods

AddSheet Method
DeleteSheet Method
Export Method
ExportSheet Method
GetCurrentRow Method
GetRowCount Method
GetSheet Method
GetSheetCount Method
Import Method
ImportSheet Method
SetCurrentRow Method
SetNextRow Method
SetPrevRow Method

Associated Properties

GlobalSheet Property
LocalSheet Property
RawValue Property
Value Property

AddSheet Method

Description

Adds the specified sheet to the run-time Data Table and returns the sheet so that you can directly set properties of the new sheet in the same statement.

Syntax

DataTable.AddSheet(SheetName)

Argument	Type	Description
SheetName	String	Assigns a name to the new sheet.

Return Value

DTSheet Object.

Example

The following example uses the AddSheet method to create the new sheet, "MySheet" in the run-time Data Table and then adds a parameter to the new sheet.

Variable=DataTable.AddSheet ("MySheet").AddParameter("Time", "8:00")

DeleteSheet Method

Description

Deletes the specified sheet from the run-time Data Table.

Syntax

DataTable.DeleteSheet SheetID

Argument	Type	Description
SheetID	Variant	Identifies the sheet to be returned. The SheetID can be the sheet name or index. Index values begin with 1.

Example

The following example uses the DeleteSheet method to delete the sheet, "MySheet" from the run-time Data Table.

DataTable.DeleteSheet "MySheet"

Export Method

Description

Saves a copy of the run-time Data Table in the specified location.

Syntax

DataTable.Export(FileName)

Argument	Type	Description
FileName	String	The full file system path of the location to which the Data Table should be exported.

Example

The following example uses the Export method to save a copy of the test's Data Table in C:\flights.xls.

DataTable.Export ("C:\flights.xls")

ExportSheet Method

Description

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.

Syntax

DataTable.ExportSheet(FileName, DTSheet)

Argument	Type	Description
FileName	String	The full file system path of the Excel table to which you want to export a sheet.
DTSheet	Variant	The name or index of the run-time Data Table sheet that you want to export. Index values begin with 1.

Example

The following example uses the ExportSheet method to save the first sheet of the run-time Data Table to the name.xls file.

DataTable.ExportSheet "C:\name.xls" ,1

GetCurrentRow Method

Description

Returns the current (active) row in the first sheet in the run-time Data Table (global sheet).

Syntax

DataTable.GetCurrentRow

Return Value

Number

Example

The following example uses the GetCurrentRow method to retrieve the row currently being used in run-time Data Table and writes it to the report.

row = DataTable.GetCurrentRow

Reporter.ReportEvent 1, "Row Number", row

GetRowCount Method

Description

Returns the total number of rows in the longest column in the first sheet in the run-time Data Table (global sheet).

Syntax

DataTable.GetRowCount

Return Value

Number

Example

The following example uses the GetRowCount method to find the total number of rows in the longest column of the MySheet run-time data sheet and writes it to the report.

rowcount = DataTable.GetSheet("MySheet").GetRowCount

Reporter.ReportEvent 2, "There are " &rowcount, "rows in the data sheet."

The example below performs the equivalent of renaming a column (parameter) in the DataTable by copying the data from one column to a new column with a new name, and then deleting the old column.

'add a new column
DataTable.GetSheet("dtGlobalSheet").AddParameter "NewColumn","Row1Value"

'copy the cells from the old column into the new column
cnt=DataTable.GetRowCount
For i=1 to cnt
    DataTable.SetCurrentRow(i)
    OldVal=DataTable.Value("OldColumn","dtGlobalSheet")
    DataTable.Value("NewColumn","dtGlobalSheet")=OldVal
Next

'delete the old column
DataTable.GetSheet("dtGlobalSheet").DeleteParameter("OldColumn")

GetSheet Method

Description

Returns the specified sheet from the run-time Data Table.

Syntax

DataTable.GetSheet(SheetID)

Argument	Type	Description
SheetID	Variant	Identifies the sheet to be returned. The Sheet ID can be the sheet name or index. Index values begin with 1.

Return Value

DTSheet Object

Examples

The following example uses the GetSheet method to return the "MySheet" sheet of the run-time Data Table and add a parameter to it.

MyParam=DataTable.GetSheet ("MySheet").AddParameter("Time", "8:00")

You can also use this to add a parameter to the "MySheet" local sheet (note that no value is returned).

DataTable.GetSheet ("MySheet").AddParameter "Time", "8:00"

The example below performs the equivalent of renaming a column (parameter) in the DataTable by copying the data from one column to a new column with a new name, and then deleting the old column.

'add a new column
DataTable.GetSheet("dtGlobalSheet").AddParameter "NewColumn","Row1Value"

'copy the cells from the old column into the new column
cnt=DataTable.GetRowCount
For i=1 to cnt
    DataTable.SetCurrentRow(i)
    OldVal=DataTable.Value("OldColumn","dtGlobalSheet")
    DataTable.Value("NewColumn","dtGlobalSheet")=OldVal
Next

'delete the old column
DataTable.GetSheet("dtGlobalSheet").DeleteParameter("OldColumn")

GetSheetCount Method

Description

Returns the total number of sheets in the run-time Data Table.

Syntax

DataTable.GetSheetCount

Return Value

Number

Example

The following example uses the GetSheetCount method to find the total number of sheets in the run-time Data Table and writes it to the report.

sheetcount = DataTable.GetSheetCount

Reporter.ReportEvent 0, "Sheet number", "There are " & sheetcount & " sheets in the Data Table."

Import Method

Description

Imports the specified Microsoft Excel file to the run-time Data Table.

Notes:

The imported table must match the test. The column names must match the parameters in the test, and the sheet names must match the action names.

If you import an Excel table containing combo box or list cells, conditional formatting, or other special cell formats, the formats are not imported and the cell is displayed in the Data Table with a fixed value.

Syntax

DataTable.Import(FileName)

Argument	Type	Description
FileName	String	The full or relative path of the Excel table to import. It can be a file system or Quality Center path.

Example

The imported table replaces all data in the existing run-time Data Table (including all data sheets).

The following example uses the Import method to import the flights.xls table to the run-time Data Table.

DataTable.Import ("C:\flights.xls")

ImportSheet Method

Description

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).

Notes:

The column headings in the sheet you import must match the Data Table parameter names in the action for which the sheet is being imported. Otherwise, your test or component may fail.

The sheet you import automatically takes the name of the sheet it replaces.

If you import an excel sheet containing combo box or list cells, conditional formatting, or other special cell formats, the formats are not imported and the cell is displayed in the Data Table with a fixed value.

Syntax

DataTable.ImportSheet(FileName, SheetSource, SheetDest)

Argument	Type	Description
FileName	String	The full or relative path of the Excel table from which you want to import a sheet. It can be a file system or Quality Center path.
SheetSource	Variant	The name or index of the sheet in the file that you want to import. Index values begin with 1.
SheetDest	Variant	The name or index of the sheet in the Data Table that you want to replace with the SheetSource. Index values begin with 1.

Example

The following example uses the ImportSheet method to import the first sheet of the name.xls table to the name sheet in the test's run-time Data Table.

DataTable.ImportSheet "C:\name.xls" ,1 ,"name"

SetCurrentRow Method

Description

Sets the specified row as the current (active) row in the run-time Data Table.

Important Information

You can set a row only if it contains at least one value.
If you call another action (local or external) after performing this method, then when you return to the current action after the called action is complete, the current row in the action sheet is reset to the row corresponding to the current iteration (regardless of the value previously set in the DataTable.SetCurrentRow step.)

Syntax

DataTable.SetCurrentRow(RowNumber)

Argument	Type	Description
RowNumber	Number	Indicates the number of the row to set as the active row. The first row is numbered 1.

Example

The following example uses the SetCurrentRow method to change the active row to the second row in the global run-time Data Table.

DataTable.SetCurrentRow (2)

The example below performs the equivalent of renaming a column (parameter) in the DataTable by copying the data from one column to a new column with a new name, and then deleting the old column.

'add a new column
DataTable.GetSheet("dtGlobalSheet").AddParameter "NewColumn","Row1Value"

'copy the cells from the old column into the new column
cnt=DataTable.GetRowCount
For i=1 to cnt
    DataTable.SetCurrentRow(i)
    OldVal=DataTable.Value("OldColumn","dtGlobalSheet")
    DataTable.Value("NewColumn","dtGlobalSheet")=OldVal
Next

'delete the old column
DataTable.GetSheet("dtGlobalSheet").DeleteParameter("OldColumn")

SetNextRow Method

Description

Sets the row after the current (active) row as the new current row in the run-time Data Table.

Note: You can only set a row that contains at least one value. If the current row is the last row in the Data Table, applying this method sets the first row in the Data Table as the new current row.

Syntax

DataTable.SetNextRow

Example

The following example uses the SetNextRow method to change the active row to the next row in the run-time Data Table.

DataTable.SetNextRow

SetPrevRow Method

Description

Sets the row above the current (active) row as the new current (active) row in the run-time Data Table.

Note: If the current row is the first row in the Data Table, applying this method sets the last row in the Data Table as the new current row.

Syntax

DataTable.SetPrevRow

Example

The following example uses the SetPrevRow method to change the active row to the previous row in the global run-time Data Table.

DataTable.SetPrevRow

GlobalSheet Property

Description

Returns the first sheet in the run-time Data Table (global sheet).

Syntax

DataTable.GlobalSheet

Example

The following example uses the GlobalSheet property to return the global sheet of the run-time Data Table in order to add a parameter (column) to it.

ParamValue=DataTable.GlobalSheet.AddParameter("Time", "5:45")

You can also use this method to add a parameter to the global sheet (note that no value is returned).

DataTable.GlobalSheet.AddParameter "Time", "5:45"

LocalSheet Property

Description

Returns the current (active) local sheet of the run-time Data Table.

Syntax

DataTable.LocalSheet

Example

The following example uses the LocalSheet property to return the local sheet of the run-time Data Table in order to add a parameter (column) to it.

MyParam=DataTable.LocalSheet.AddParameter("Time", "5:45")

RawValue Property

Description

Retrieves the raw value of the cell in the specified parameter and the current row of the run-time Data Table. The raw value is the actual string written in a cell before the cell has been computed, such as the actual text from a formula.

Syntax

DataTable.RawValue ParameterID [, SheetID]

Argument	Type	Description
ParameterID	Variant	Identifies the parameter (column) of the value to be set/retrieved. Index values begin with 1. Note: The specified value must be an actual column header name that has been defined as a Data Table parameter. Entering A (or another default column label) as the column name is not valid unless A has explicitly been set as the name of a Data Table parameter.
SheetID	Variant	Optional. Identifies the sheet to be returned. The SheetID can be the sheet name, index or dtLocalSheet, or dtGlobalSheet. If no Sheet is specified, the first sheet in the run-time Data Table is used (global sheet). Index values begin with 1.

Example

The following example uses the RawValue property to find the formula used in the current row of the Date column in the ActionA sheet in the run-time Data Table. The statement below returns the value: =NOW()

FormulaVal=DataTable.RawValue ("Date", "ActionA")

Value Property

Description

DataTable default property. Retrieves or sets the value of the cell in the specified parameter and the current row of the run-time Data Table.

Note: This property returns the computed value of the cell. For example, if the cell contains a formula, the method returns True or False.

Syntax

To find the value:

DataTable.Value(ParameterID [, SheetID])

or

DataTable(ParameterID [, SheetID])

To set the value:

DataTable.Value(ParameterID [, SheetID])=NewValue

or

DataTable(ParameterID [, SheetID]) =NewValue

Argument	Type	Description
ParameterID	Variant	Identifies the parameter (column) of the value to be set/retrieved. Index values begin with 1.
SheetID	Variant	Optional. Identifies the sheet to be returned. The SheetID can be the sheet name, index or dtLocalSheet, or dtGlobalSheet. If no Sheet is specified, the first sheet in the run-time Data Table is used (global sheet). Index values begin with 1.
NewValue	String	Sets the value for the specified table cell.

Example

The following example uses the Value property to set the value in the current row of the Destination parameter (column) in the "ActionA" sheet in the run-time Data Table.

DataTable.Value ("Destination", "ActionA")="New York"

The following example uses the Value property to set the value in the current row of the second parameter (column) in the third sheet.

DataTable.Value (2,3)="New York"

Note: You could omit the word Value in the statements above, because Value is the default property for the DataTable object.

The following example uses the default property to set the value in the current row of the Destination parameter (column) in the current (active) local sheet.

DataTable("Destination", dtlocalSheet)="New York"

The example below performs the equivalent of renaming a column (parameter) in the DataTable by copying the data from one column to a new column with a new name, and then deleting the old column.

'add a new column
DataTable.GetSheet("dtGlobalSheet").AddParameter "NewColumn","Row1Value"

'copy the cells from the old column into the new column
cnt=DataTable.GetRowCount
For i=1 to cnt
    DataTable.SetCurrentRow(i)
    OldVal=DataTable.Value("OldColumn","dtGlobalSheet")
    DataTable.Value("NewColumn","dtGlobalSheet")=OldVal
Next

'delete the old column
DataTable.GetSheet("dtGlobalSheet").DeleteParameter("OldColumn")