Using Excel with Appian

Microsoft Excel is a common tool for organizing and communicating business data. Appian provides multiple capabilities to automate reading, writing, and manipulating data within Excel workbooks.

The strategy you use to interact with Excel depends on your goals and the data involved. The following sections cover reading data, writing data, and additional manipulation actions to help you choose the right approach.

Read data from an Excel workbook

Appian provides smart services, plug-ins, and Robotic Process Automation (RPA) actions to read data from Excel workbooks.

Generally, you'll use the smart services and functions in the Excel Tools plug-in for most read operations. Use RPA if you need more sophisticated functionality. RPA requires additional infrastructure and considerations around robot availability and management, so evaluate whether other features meet your needs before choosing RPA.

The following table lists the recommended approach for each use case.

I'm trying to…	Use…
Read data from an XLSX or CSV file	Import CSV to Database or Import Excel to Database smart services in the Excel Tools Plug-in.
Use data from an Excel file in a record type or custom data type (CDT)	Use the Import Excel to Database smart service in the Excel Tools Plug-in to import each sheet to a database table. Then, create a record type backed by the table, or create a data store and CDT for the table.
Read data from multiple sheets where the number of sheets is dynamic	In a robotic task, use the Get worksheet names action to count the number of worksheets. Then, build a loop with the Get value action.
Read a specific cell, row, or column from an XLSX or CSV file	Read Excel Cell by Name or Read Excel Cell by Number functions in the Excel Tools Plug-in.
Read a specific cell, row, or column from an XLS, XLSM, or XLSB file	Get value action in a robotic task.

Performance considerations for reading Excel data

Keep the following limits in mind when reading Excel data with RPA:

• File type constraints: If you need to import a large amount of data (20,000+ rows) into an Appian database, but your file type (XLS, XLSM, XLSB) prevents using out-of-the-box capabilities, use RPA to convert the file to XLSX first. Converting the file to XLSX allows plug-ins or smart services to read and load the data, which improves performance and reduces errors.
• Batch processing for large files: If converting the file is not feasible and you must use only RPA actions, design your robotic task to extract data in batches. Use the Start Process action to send each batch to the process iteratively. Batch processing prevents storing large amounts of data in a single process variable, which can cause memory issues.

Write data to an Excel workbook

Appian provides smart services, plug-ins, and Robotic Process Automation (RPA) actions to write data to Excel workbooks.

Use Appian smart services for data export and document generation. RPA provides more sophisticated functionality when smart services do not meet your formatting or file type requirements.

The following table lists the recommended approach for each use case.

I'm trying to…	Use…
Write a record list or records-powered grid to an XLSX file	Configure the Export to Excel option for your record lists and records-powered grids.
Write transactional data from a database to an XLSX file (no formatting needed)	Export Data Store Entity to Excel smart service. If multiple tabs need to have data, use multiple nodes of this smart service.
Write transactional data to CSV	Export Data Store Entity to CSV smart service.
Write transactional data to a template with supported formatting	Export Data Store Entity to Excel smart service.
Write transactional data to a template without supported formatting	Excel Tools Plug-in.
Write formatted text into an Excel file	Write into worksheet in a robotic task.
Write formulas into an Excel file	Write into worksheet in a robotic task.
Write data to an XLSM, XLSB, or XLS	Write into worksheet in a robotic task.
Export data from a grid that doesn't use a record type as the source	Call a!exportDataStoreEntityToExcel() from the interface.
Update specific cells in a particular sheet	Export Data Store Entity to Excel using the Custom Cell Positions and Custom Cell Values input parameters.
Export more than 50 columns	Excel Tools Plug-in.
Export with a custom sort (not the primary key)	Excel Tools Plug-in.
Write calculated fields into an Excel file	Use Appian expressions and update fields in the database, then use the Export Data Store Entity to Excel smart service.

Performance considerations for writing Excel data

Keep the following limits in mind when writing data to Excel with RPA:

• Memory constraints: The Excel: License Not Required module loads workbooks into agent memory. The more columns you write, the fewer rows you can write before triggering an out-of-memory exception. Opening large files can also decrease RPA agent performance. To handle larger files, use the Excel: License Required module, which does not rely on RPA agent memory.
• Query limits: Appian enforces limits on how much data you can retrieve in a single operation. See Query Record Type Limits and Query Limits to External Databases. To stay within these limits, build loops in your robotic task to write data in batches.

Manipulate and format Excel files

Appian provides smart services, plug-ins, and Robotic Process Automation (RPA) actions to manipulate and format Excel files.

The following table lists the recommended approach for each use case.

I'm trying to…	Use…
Merge Excel workbooks	Merge Excel Documents in the Excel Tools Plug-in.
Convert an XLSX to a CSV	Convert Excel to CSV in the Excel Tools Plug-in.
Convert any Excel file extension to another Excel file extension (XLSX, XLS, XLSB, XLSM, CSV)	Save workbook as in a robotic task.
Protect an Excel workbook with password	Encrypt Excel with Password smart service in the Excel Tools Plug-in.
Run a macro (for example, to refresh or generate a chart)	RPA keyboard shortcuts
Insert rows or columns	Insert row or column in a robotic task.
Delete rows or columns	Delete from row or column in a robotic task.

Feedback