Excel Merge
Merges the contents of Excel files into one file according to the order set.
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.
excelMerge --directory(String) [--filemask(String)] [--outputfile(String)] [--compatibilitymode(Boolean)] --fileordering(FileOrdering) (String)=file (Numeric)=numberoftabs
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 |
|---|---|---|---|---|
| Directory | directory |
Required |
Text |
Full path where the files are located. |
| File mask | filemask |
Optional |
Text |
Filter that is applied in the search. It must be inserted as follows: FILENAME.FILE_EXTENSION. Here are some examples:- *.xlsx: filters all files with the ".xlsx" extension; - name: filters all files containing the word "name" as part of the name.❕ Important: Do not use uppercase letters or spaces in FILENAME. |
| Output file | outputfile |
Optional |
Text |
File path to where the Excel files are merged to. |
| Compatibility mode | compatibilitymode |
Optional |
Boolean |
This option should be used if the extension of any of the Excel files is different from those specified in the File mask parameter. |
| File ordering | fileordering |
Required |
FileOrdering |
Order in which the file content is saved to the output file. |
Output parameters
| Designer mode label | Script mode name | Accepted variable types | Description |
|---|---|---|---|
| File | file | Text |
Returns a variable with the full path of the file in which the values are merged. |
| Number of sheets | numberoftabs | Number |
Returns the number of sheets that were created. |
Example
The following code example demonstrates how to merge all files from a specific directory into one file.
defVar --name sourceFileFolder --type String
defVar --name sheetsQuantity --type Numeric
excelMerge --directory "Enter source directory" --outputfile "Enter the destination excel file" --fileordering "None" sourceFileFolder=file sheetsQuantity=numberoftabs
logMessage --message "\r\n${sourceFileFolder}\r\n${sheetsQuantity}" --type "Info"
Limitations
If a file that does not exist is entered in the Output file parameter, a new one containing the contents of the Directory file is created.