DataTable Object

Description

The run-time data table.

Methods and Properties

AddSheet	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.
DeleteSheet	Deletes the specified sheet from the run-time data table.
Export	Saves a copy of the run-time data table in the specified location.
ExportSheet	Exports a specified sheet of the run-time data table to the specified sheet in the specified file.
GetCurrentRow	Returns the current (active) row in the first sheet in the run-time data table (global sheet).
GetRowCount	Returns the total number of rows in the longest column in the first sheet in the run-time data table (global sheet).
GetSheet	Returns the specified sheet from the run-time data table.
GetSheetCount	Returns the total number of sheets in the run-time data table.
GlobalSheet	Returns the first sheet in the run-time data table (global sheet).
Import	Imports the specified Microsoft Excel file to the run-time data table.
ImportSheet	Imports a sheet of a specified file to a specified sheet in the run-time data table.
LocalSheet	Returns the current (active) local sheet of the run-time data table.
RawValue	Retrieves the raw value of the cell in the specified parameter and the current row of the run-time data table.
SetCurrentRow	Sets the specified row as the current (active) row in the run-time data table.
SetNextRow	Sets the row after the current (active) row as the new current row in the run-time data table.
SetPreviousRow	Sets the row above the current (active) row as the new current (active) row in the run-time data table.
Value	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.

Back to top

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

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

Back to top

DeleteSheet Method

Description

Deletes the specified sheet from the run-time data table.

Syntax

DataTable.DeleteSheet SheetID

Example

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

DataTable.DeleteSheet "MySheet"

Back to top

Export Method

Description

Saves a copy of the run-time data table in the specified location.

Syntax

DataTable.Export FileName

IMPORTANT

This method is not supported for business components or BPT tests.

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

Back to top

ExportSheet Method

Description

Exports a specified sheet of the run-time data table to the specified sheet in the specified file.

• If you do not specify a destination sheet name, the source sheet name is used for the destination.

• 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, vtSrcSheet, [DstSheetName]

IMPORTANT

When you export a single sheet from a component or a BPT test, you use the following format:

DataTable.ExportSheet FileName, vtSrcSheet, [DstSheetName]

For the vtSrcSheet parameter, you can specify the source sheet in the Datatable by the index number of the sheet in the table. However, when working with a component, the index of the exported sheet is different if you are running the component by itself or in a Business Process Testing test:

• When running a component individually, the index 1 refers to the data sheet of the component.
• When running a BPT test, the index 1 refers to the global sheet of the Business Process Testing test run.

Because of these differing indexes, if you assign the value 1 for the vtSrcSheet parameter, it is imported to different places depending on where you are running your component.

As a result, it is recommended not to use the index of the sheet for the vtSrcSheet parameter.

Examples

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 

The following example creates separate output data table sheets for each iteration of the test.
The test retrieves the number of the row being handled in the current iteration, modifies a value in the global data sheet and then exports the global sheet to a new sheet, named according to the row number.

currentRow = DataTable.GetCurrentRow
DataTable("Param1", dtGlobalSheet) = currentRow
exportedSheetName = "Global-" & currentRow
DataTable.ExportSheet "c:\result.xls", "Global", exportedSheetName

Back to top

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

Back to top

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("Global").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","Global")
    DataTable.Value("NewColumn","Global")=OldVal
Next 

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

Back to top

GetSheet Method

Description

Returns the specified sheet from the run-time data table.

Syntax

DataTable.GetSheet SheetID

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("Global").AddParameter "NewColumn","RowValueNew"
DataTable.GetSheet("Global").AddParameter "OldColumn","RowValueOld"

'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","Global")
    DataTable.Value("NewColumn","Global")=OldVal
Next 

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

Back to top

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

Back to top

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"

Back to top

Import Method

Description

Imports the specified Microsoft Excel file to the run-time data table.

Syntax

DataTable.Import FileName

IMPORTANT

This method is not supported for business components or BPT tests.

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

Back to top

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

• 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, vtSrcSheet, vtDstSheet

IMPORTANT

When you import a single sheet to a component or BPT test, you use the following format:

DataTable.ImportSheet FileName, vtSrcSheet, vtDstSheet

For the vtDstSheet parameter, you can specify the source sheet in the Data table by the index number of the sheet in the table. However, when working with a component, the index of the sheet is different if you are running the component by itself or in a Business Process Testing test:

• When running a component individually, the index 1 refers to the data sheet of the component.
• When running a BPT test, the index 1 refers to the global sheet of the Business Process Testing test run.

Because of these differing indexes, if you assign the value 1 for the vtDstSheet parameter, it is imported to different places depending on where you are running your component.

As a result, it is recommended not to use the index of the sheet for the vtDstSheet parameter.

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"

Back to top

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

Back to top

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.RawValueParameterID, [SheetID]

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

Back to top

SetCurrentRow Method

Description

Sets the specified row as the current (active) row in the run-time data table.

Syntax

DataTable.SetCurrentRow(RowNumber)

IMPORTANT

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

Examples

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("Global").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","Global")
    DataTable.Value("NewColumn","Global")=OldVal
Next 

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

Back to top

SetNextRow Method

Description

Sets the row after the current (active) row as the new current row in the run-time data table.

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

Back to top

SetPrevRow Method

Description

Sets the row above the current (active) row as the new current (active) row in the run-time data table.

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

Back to top

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.

Syntax

To find the value:

• DataTable.Value ParameterID, [SheetID] or
• DataTable ParameterID, [SheetID]

To set the value:

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

Examples

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", "Action1")="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("Global").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","Global")
    DataTable.Value("NewColumn","Global")=OldVal
Next 

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

Back to top

See also:

• Crypt Object
• DataTable Object
• Description Object
• DeviceReplay Object
• DotNetFactory Object
• DTParameter Object
• DTSheet Object
• Environment Object
• Extern Object
• Parameter Object
• JSON Object
• JsonUtil Object
• MercuryTimers Object (Collection)
• MercuryTimer Object
• NV Object
• OptionalStep Object
• ParallelUtil Object
• LocalParameter Object
• PasswordUtil Object
• PathFinder Object
• PDFUtil Object
• Properties Object (Collection)
• QCUtil Object
• RandomNumber Object
• Recovery Object
• Remote Connection Object
• Reporter Object
• RepositoriesCollection Object
• Repository Object
• Services Object
• Setting Object
• SystemMonitor Object
• TestArgs Object
• TextUtil Object
• UIAutomation Object
• VisualRelation Object
• VisualRelations Object
• VisualRelationsCollection Object
• WebUtil Object
• XMLUtil Object