Export to Excel/CSV Smart Service

Summary

The Export Data Store Entity to Excel/CSV Smart Service allows designers to safely export large datasets.

It can be used to export data from Appian to other third-party applications. For example, a designer may want to export all data or just updates made within the last day.

It returns a document with the resulting Excel/CSV output.

Appian strongly recommends customers to use the Export Data Store Entity to Excel/CSV smart service in place of the Export SQL to Excel and Export SQL to Flat File (part of the Excel Tools Plug-in).

Properties

  • Category: Document Generation
  • Icon:
  • Assignment Options: This smart service can be either attended or unattended (default)

Node Inputs

Name Data Type Description Required Multiple
Data Source Data Store Entity The data store entity to query. Yes No
Selection Selection A list of fields that you wish to retrieve, created with a!querySelection(). No No
Aggregation Aggregation A list of fields and the operations you wish to perform on them when retrieving the data, created with a!queryAggregation(). No No
Filters Any Type This field determines the filters for the query. Only one filter or logical expression can be used at once. No No
New Document Name Text The name of the new document that is to be created by this smart service activity. No No
New Document Description Text A brief description of the document. No No
Save in Folder Folder The folder where the newly created document is to be stored. Applicable and required when creating a new document. No No
Document to Update Document The document to be updated as a result of the export to Excel operation. This can be useful when using the smart service multiple times in the same process model. If using CSV as the output format, new contents will be appended at the end of the file. No No
Output Format Text The format of file being exported. Valid values: "EXCEL" or "CSV". Default is "EXCEL". No No
Include Header Boolean Excel only. Outputs the field name in the first row of the document if set to true. Default: true No No
Sheet Name Text Excel only. The name of the sheet where data will be added as a result of executing this smart service. The following characters will be replaced with underscores: [ ] / \ ? * No No
Sheet Number Number (Integer) Excel only. The sheet number where data will be added as a result of executing this smart service. Default is 1, the first sheet. Setting the number to a non-existing sheet, creates a new sheet in the Excel document. No No
Starting Cell Text Excel only. The cell within the Excel sheet where the output will begin. Default: “A1”. No No
Custom Cell Positions Text Excel only. Array of cell positions where custom cell values should be added. No Yes
Custom Cell Values Text Excel only. Array of values to be added on the specified custom cell position. No Yes
CSV Delimiter Text CSV only. The field delimiter used to separate fields in the CSV file. Valid values are: "TAB", "COMMA", "SEMICOLON", "SPACE", "PIPE", "CARET". Default: "COMMA". No No

Node Outputs

Name Data Type Description
New Document Document The document generated as a result of executing this smart service.

See also: Document

Avoiding Query Timeouts when Exporting Data

Exporting data store entities from either record lists or smart services relies on sorting the entity by its primary key. This sorting ensures that data is exported consistently - that is, all rows are exported and no row is exported more than once.

Appian export operations use small batches to control the amount of memory consumed. The last row of a batch is tracked and used to determine where the next batch starts. By managing load this way, we avoid high memory consumption when exporting entities with a very large number of rows.

Designers should adhere to Database Performance Best Practices when creating their schema relationships. Because database views do not have a primary key, or the ability to have indexes, Appian recommends exporting data only from tables or materialized views in order to avoid query timeouts with large data sets.

Configuration Details

The following configurations and expected behavior apply when using the Export to Excel/CSV Smart Service in the process modeler:

  • To export fields in a particular order, make sure to specify the Selection parameter. Otherwise, the order of the exported fields will not be guaranteed.
  • You may specify either the Selection parameter or the Aggregation parameter but not both. If you do not specify either parameter, all fields are returned.
  • You may specify either a LogicalExpression or a list of Filters in the Filters parameter but not both.
    • To specify only one condition, use a Filter.
    • To specify more than one condition, use LogicalExpression or use a list of Filters.
  • When using fields of type Date or Date and Time in a filter and the filter's value uses either the todate(), now() or today() functions, make sure to cast their value using the appropriate type. For example: cast('type!{http://www.appian.com/ae/types/2009}Date', today()), or cast('type!{http://www.appian.com/ae/types/2009}Datetime', now())
  • To create multiple tabs in the same Excel spreadsheet with data from different data store entities, use multiple nodes of this smart service. You can use the Document to Update and Sheet Number parameters when exporting a second data store entity. For more details, see Export Multiple Data Store Entities to Excel

Additional Options

Data Types:

See Also

FEEDBACK