Insert Data Table into Excel File
Creates and inserts values from a data table into an existing Excel file.
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.
excelSetTable --dataTable(DataTable) [--headers(Boolean)] --file(Excel) [--getfirstsheet(Boolean)] --sheet(String) [--usingcell(Boolean)] --cell(String) --row(Numeric) --column(Numeric) (Boolean)=value
Dependencies
- To use this command, open an Excel file with the Open Excel file (
excelopen) 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 |
|---|---|---|---|---|
| Data table | dataTable |
Required |
Data Table |
Data table from which values inserted into the file are obtained. |
| Has headers | headers |
Optional |
Boolean |
Enable to use the worksheet's first line as a header. |
| File | file |
Required |
Excel |
Variable that stores the Excel instance. |
| Use first sheet | getfirstsheet |
Optional |
Boolean |
Uses the first sheet to create the table. |
| Sheet | sheet |
Required when the Use first sheet parameter is disabled |
Text |
Name of the spreadsheet in which the table is created. If you enter a name that does not match any spreadsheet in the file, a new one is created with the given name. |
| Specify cell | usingcell |
Optional |
Boolean |
Enables the option to enter the complete cell coordinate. Example: "A1". |
| Cell | cell |
Required when the Specify cell parameter is enabled |
Text |
Coordinate of the cell in which the table begins. Example: "A1". |
| Row | row |
Required when the Specify cell parameter is disabled |
Number |
Value that corresponds to the cell row in which the initial value should be inserted. Example: "1" in "A1". |
| Column | column |
Required when the Specify cell parameter is disabled |
Number |
Value that corresponds to the column cell in which the initial value of the table should be inserted. Example: "A" in "A1". |
Output parameters
| Designer mode label | Script mode name | Accepted variable types | Description |
|---|---|---|---|
| Success | value | Boolean |
Returns True if the table is created successfully, or False otherwise. |
Example
The Open Excel File command opens an Excel file and imports its data into a variable named "dataTable". The command runs again, opening another Excel file and creating a new spreadsheet, in which the data from "dataTable" is inserted.
defVar --name dataTable --type DataTable
defVar --name excelFile --type Excel
defVar --name excelFile02 --type Excel
excelOpen --file "file2" excelFile02=value
excelOpen --file "file1" excelFile=value
excelGetTable --file ${excelFile} --getfirstsheet--entiretabledataTable=value
excelSetTable --dataTable ${dataTable} --file ${excelFile02} --sheet Sheet03 --usingcell--cell A1
excelClose --file ${excelFile02} --save
excelClose --file ${excelFile} --save
❕ Important: To run the sample script, you need 2 different excel files, then enter their file path in the Open Office File command.