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.
First, you'll create a new process model. Then, you'll add a process variable, which you'll use later on to filter data.
To create a process model and process variable:
customerId
.1
.Next, you'll configure the node inputs for the Export Data Store Entity To Excel smart service.
To configure the node inputs:
Enter a query selection to select which columns you want to export. For example:
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.
For the Value field, enter a query filter for your data. For example:
a!queryFilter("Customer.id", "=", pv!customerId)
For the Value field, enter a name for your document. You can also add the start time of the process, like the example below, to make documents easier to identify.
"Orders Excel - " & pp!starttime
For 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"
.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"}
.Click Expression Editor 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.
Lastly, you'll configure the node outputs for the Export Data Store Entity To Excel smart service.
To configure the node outputs:
Create a new custom output to get the order ids for a particular customer. 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.
To add a second smart service node:
For the Value field, enter a query filter for your data. For example:
a!queryFilter("Customer.id", "=", pv!customerId)
="Orders Details"
.For the Value field, 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.
Export Multiple Data Store Entities to Excel or CSV