Get Excel Table

Gets the value of a table from an Excel file, based on its row and column coordinates.

Command availability: IBM RPA SaaS and IBM RPA on premises

Script syntax

IBM RPA's proprietary script language has a syntax similar to other programming languages. The script syntax defines the command's syntax in the script file. You can work with this syntax in IBM RPA Studio's Script mode.

excelGetTable --file(Excel) [--getfirstsheet(Boolean)] --sheet(String) [--entiretable(Boolean)] [--userange(Boolean)] --range(String) --fromrow(Numeric) --fromcolumn(Numeric) [--torow(Numeric)] [--tocolumn(Numeric)] [--hasheaders(Boolean)] (DataTable)=value (Numeric)=rows (Numeric)=columns

Dependencies

To use this command, open an Excel file with the Open Excel file (excelopen) command.

Limitations

IBM RPA displays date values along with the time, regardless of how it is formatted.
Example: 01/14/2022 -> 01/14/2022 12:00:00AM
Starting from version 23.0.16, empty cells now return a null value instead of an empty string. This can affect scripts that use Filter Table (filterTable) command.

Input parameters

The following table displays the list of input parameters available in this command. In the table, you can see the parameter name when working in IBM RPA Studio's Script mode and its Designer mode equivalent label.

Designer mode label	Script mode name	Required	Accepted variable types	Description
Excel instance	`file`	`Required`	`Excel`	Variable that stores the Excel instance.
Use First Spreadsheet	`getfirstsheet`	`Optional`	`Boolean`	Enable to read the data available in the first spreadsheet.
Spreadsheet	`sheet`	`Required when the Use First Spreadsheet parameter is disabled`	`Text`	Name of the spreadsheet that contains the wanted table.
Get Entire Table	`entiretable`	`Optional`	`Boolean`	Enable to obtain the value of the entire table.
Use Interval	`userange`	`Optional`	`Boolean`	Enable to set the interval between the table cells.
Interval	`range`	`Required when the Use Interval parameter is enabled`	`Text`	Specifies the range of cells from which the table is retrieved. The range is defined by separating the first and last cells with a colon (:). Example: A1:D5
From Row	`fromrow`	`Required when the Use Interval parameter is disabled`	`Number`	Sets the initial row from where the data must be retrieved.
From Column	`fromcolumn`	`Required when the Use Interval parameter is disabled`	`Number`	Sets the initial column from where the data must be retrieved.
To Row	`torow`	`Optional`	`Number`	Defines the last row to retrieve data from.
To Column	`tocolumn`	`Optional`	`Number`	Defines the last column to retrieve data from.
Has Headers	`hasheaders`	`Optional`	`Boolean`	Enable to use the first row of the spreadsheet as a header.

Output parameters

Designer mode label	Script mode name	Accepted variable types	Description
Table	value	`Data Table`	Returns the Data Table obtained from the Excel file.
Rows	rows	`Number`	Returns the total number of rows of the obtained data table.
Columns	columns	`Number`	Returns the total number of columns of the obtained data table.

Example

The command retrieves all the content of the first spreadsheet from the Excel file named "excelFile". After that, the contents of the data table and the number of rows and columns of the spreadsheet in question are displayed on the IBM RPA Studio console.

defVar --name excelFile --type Excel
defVar --name dataTable --type DataTable
defVar --name rows --type Numeric
defVar --name columns --type Numeric
excelOpen --file "file" excelFile=value
excelGetTable --file ${excelFile} --getfirstsheet--entiretabledataTable=value rows=rows columns=columns
excelClose --file ${excelFile} --save
logMessage --message "\r\nData Table: ${dataTable}\r\nRows: ${rows}\r\nColumns: ${columns}\r\n" --type "Info"
// Result: Data Table: Info 01, Info 02, Info 03
// Rows: 1
// Columns: 3

Important:To run the sample script, you must use or create the file, fill the "1" row of the A, B and C columns, then enter the file path in the File path parameter of the Open Excel File command.