This page will walk you through how to export one or more data sources to Excel or CSV using the Export Data Store Entity to Excel smart service or Export Data Store Entity to CSV smart service, respectively.
Both the Export Data Store Entity to Excel ("Export to Excel" for short) and the Export Data Store Entity to CSV ("Export to CSV" for short) Smart Services can write data to a new document or update an existing document. You can specify the folder in which the document will be created.
In this example, we will export two Data Store Entities (DSEs), one DSE per sheet, in a single Excel file. The steps will be similar when using an Export to CSV Smart Service. The exported Excel file will have two sheets with the queried data (one about customers and another about customer order details) from the DSEs.
To create the first sheet, we will need to add a smart service to our process model.
Click the Selection input and click the Expression Editor icon next to the Value field. Enter a query selection to select which columns you want to export. Consider using an alias for your fields like the example below.
1
2
3
4
5
6
7
8
a!querySelection(
columns: {
a!queryColumn(
field: "Customer.Name",
alias: "Customer"
)
}
)
Note: Nested CDTs will not be exported unless you specify the field. For example, employee.department.name
will be exported, but employee.department
will not.
Click the Filters input and in the Value field enter a query filter for your data like the example below. Note that we used our previously created process variable "customerId" in our query filter.
a!queryFilter("Customer.id", "=", pv!customerId)
Click the New Document Name input and enter a name for your document in the Value field. You can also add the start time of the process, like the example below, to make documents easier to identify.
"Orders Excel - " & pp!starttime
Click the Save in Folder input, and in the Value field select the folder you want to save your new document in.
Note: Anyone with at least Viewer permissions to this folder will be able to see this document. Learn more about configuring folder security.
="Orders"
and in the Sheet Number input enter 1. If you don't enter a sheet name, the default will be set to "Sheet 1." You can leave the sheet number blank if you don't want to specify the number. When creating a document, the default is set to 1.="A5"
.={"A1", "B1", "A2", "B2"}
in the.In the Custom Cell Values input, click the Expression Editor icon next to the Value field and enter ={"Data Store Entity: ", "Orders", "Exported On: ", tp!starttime}
.
Note: When using cell position and values, the starting cell has to be below the other cells.
Create a new custom output to get the order ids for this particular customer. Enter an expression that returns all orderIds. In the example below, we created a query and saved it to a process variable named "orderIds."
1
2
3
4
5
6
7
8
9
10
11
12
13
14
a!queryEntity(
entity: cons!CASE_APP_OrderDataStoreEntity,
query: a!query(
filter: a!queryFilter(
"Customer.id",
"=",
pv!customerId
),
pagingInfo: a!pagingInfo(
1,
- 1
)
)
).data.id
To add a second sheet to our Excel file, we are going to add another Export to Excel smart service to our process.
Click the Filters input and in the Value field enter a query filter for your data like the example below. Note that we also used our previously created process variable "customerId" in our query filter.
a!queryFilter("Customer.id", "=", pv!customerId)
In the Sheet Name input, enter ="Orders Details"
and in the Sheet Number input enter 2.
Note: If you do not enter a sheet number for the second smart service, the exported data will replace the existing data on the existing sheet.
Your process model should look similar to the screenshot below:
After you run your process, you will be able to see a new document added to the folder that you saved it in, like the screenshot below.
Export Multiple Data Store Entities to Excel or CSV