The capabilities described on this page are included in Appian's standard capability tier. Usage limits may apply. |
Build a robotic task to parse Excel files
Learn how to automate Excel using Appian RPA.
Microsoft Excel is a widely used spreadsheet program available in most business settings. It's likely that you use Excel to complete a variety of tasks, such as data analysis, financial modeling, and project management, among others. With just a few clicks, Appian RPA can automate tedious and time-consuming tasks in Excel. This means the Excel: License Not Required module can read and write data within an Excel workbook for you.
Because the strategy you use to interact with Excel depends on your purposes and the data involved, we've assembled guidance and tips to improve your design experience. Learn more about the many ways to use Excel with Appian, based on your goals.
The Excel License Not Required module is compatible with the following Excel file types.
Tip: You cannot use this module to create a Macro-Enabled Workbook (.xlsm) file. However, you can open an .xlsm file and work with it.
Excel actions are listed in the Excel: License Not Required area of the palette. The actions described in this section do not need a licensed version of Excel on the host machine.
Tip: You can't mix low-code actions from the Excel: Licensed Not Required and Excel: License Required modules together. The technologies used in each module can't interact with the same workbook at the same time.
Does your host machine have a licensed version of Excel? | Then use the… |
---|---|
No | Excel: License Not Required module. |
Yes |
Excel: License Required module. The Excel: License Required module can complete more advanced actions and is better at managing large (in terms of file size or number of rows) Excel files. Note: The Excel: License Required module is only available on Windows machines. |
This action opens an existing Excel workbook or creates a new workbook.
In the CONFIGURATION area of the action pane, configure the following parameters:
Workbook Action: Select from one of the following options. Depending on the workbook action you select, additional parameters display.
Workbook Action | Additional Parameters |
---|---|
Open existing workbook |
File Path: This is the folder path and name of the Excel file, including its extension, to be opened. This field lets you enter a text string, choose a variable, or use the expression editor. |
Create new workbook | Folder: This is the path to the folder where you want to save a new workbook. This field lets you enter a text string, choose a variable, or use the expression editor. File Name: This is the name of the new workbook you are creating. This field lets you enter a text string, choose a variable, or use the expression editor. File Type: This field identifies the Excel file type for the new workbook. Options include .xlsx, or .xls. This field only displays if you are creating a new workbook. |
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action closes the Excel workbook and can optionally save changes.
In the CONFIGURATION area of the action pane, configure the following parameter:
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action retrieves the position of the last cell within a range or the total number of rows or columns up to and including the last cell. The result is stored in a robotic task variable that you must create. You could use this action to determine the limits for a loop or use it with the Get Value action.
In the CONFIGURATION area of the action pane, configure the following parameters:
In the OUTPUTS area of the action pane, configure the following parameters to store this result:
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action retrieves data from a workbook and stores it in a robotic task variable that you must create.
In the CONFIGURATION area of the action pane, configure the following parameters:
Component Type: Select the component from which you want to retrieve data. Each component type returns a different type of data. Depending on the component you select, additional parameters display.
Component | Returns | Additional Parameter(s) |
---|---|---|
Cell | primitive system data type | Cell: This is the column letter and row number for the cell from which you want to return data. This field lets you enter a text string, choose a variable, or use the expression editor. |
Column | an array with a data type determined by the file's content | Column: This is the column letter; e.g., A, B, C, for which you want to return data. You can enter text or select a variable. This field lets you enter a text string, choose a variable, or use the expression editor. |
Range | map array | Starting Cell and Ending Cell: These fields represent the start and end of the range for which you want to return data. These fields let you enter a text string, choose a variable, or use the expression editor. |
Specify header row: When this checkbox is marked, the robotic task uses the column headers as the key in the returned map. If the starting cell, ending cell, or any fields in the specified header row are empty, the column names (e.g., A, B, C) are the key in the returned map.
In the OUTPUTS area of the action pane, configure the following parameters to store this result:
When storing custom-formatted numbers, particularly those representing currency, save them as decimal numbers rather than text to maintain accuracy.
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action returns the worksheet names, including hidden worksheets, in the same order as they appear in the file. The returned value is an array of strings ordered by index; e.g., tab 1
, tab 2
, and is stored in robotic task variable that you create.
In the OUTPUTS area of the action pane, configure the following parameters to store this result:
In the AFTER COMPLETION area of the action pane, configure the following parameters:
After a workbook is opened or created, this action saves the workbook with the name and to the location that you specify in the parameters. If the file name in the configuration is different than the opened file, the robotic task saves the workbook as a new file.
Note: This action works with .xlsx and .xls files only.
Tip: You can't use this action to rename an existing workbook. However, if you want to rename an existing workbook, you can use the Rename a file or folder File System action to do that.
In the CONFIGURATION area of the action pane, configure the following parameters:
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action selects a specific worksheet within an Excel file so that the robotic task can interact with it.
In the CONFIGURATION area of the action pane, configure the following parameter:
In the AFTER COMPLETION area of the action pane, configure the following parameters:
This action writes data or a formula into a single cell or a range.
In the CONFIGURATION area of the action pane, configure the following parameters:
In the AFTER COMPLETION area of the action pane, configure the following parameters:
{"id": 1, "name": "banana"}, {"id": 2, "name": "apple", "color": "red"}, {"id": 3, "shape": "round", "botanicalName": "hesperidium"}
, RPA may not write the data in the column order you intended. To have more control over the column order, use the cast()
function to convert the CDTs to a map before writing to the worksheet.cast()
function with a!listType(type!Map)
and a!queryRecordType
as its arguments, and wrap it around the queryRecordTypes expression; e.g., cast(a!listType(type!Map), a!queryRecordType())
.a!queryRecordType
to select the fields you want to return.fn!remove()
function as IDs are returned automatically with record queries.a!flatten()
and a!map()
functions to ensure the output is in a flat structure suitable for writing into an Excel document.1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
fn!with(
local!dataWithoutIds: fn!remove(
a!queryRecordType(
recordType: recordType!Department,
fields: {recordType!Department.fields.name,
recordType!Department.relationships.employee.fields.status},
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: 100
)
).data,
{recordType!Department.fields.id, recordType!Department.fields.employeeId}
),
a!flatten(
a!forEach(
items: local!dataWithoutIds,
expression: a!map(
name: fv!item [recordType!Department.fields.name],
status: fv!item[recordType!Department.fields.status]
)
)
)
Excel License Not Required Actions