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.

Using the Export Smart Services

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.

Add an Export to Excel Smart Service

To create the first sheet, we will need to add a smart service to our process model.

Configure Node Inputs

1. Create a new process model.
2. From the palette on the left, under Appian Smart Service expand Documentation Generation and drag the Export Data Store Entity to Excel smart service to the canvas. Alternatively, type the word "export" in the search box above the palette, this will filter the list of smart services containing this word. Then drag the Export Data Store Entity to Excel smart service to the canvas.
3. In the process model tool bar, click the Properties button and add a new process variable. In our example, we will name our process variable "customerId", make it a number(integer) type, and set the value to one. This process variable will be used in other steps to filter our data.
4. Double-click on the smart service node and in the Data tab, click the Data Store Entity input. To select a Data Store Entity, click the Directory icon next to the Value field to browse through the available DSEs.

5. 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.

6. 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)

7. 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

8. 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.

9. In the Sheet Name input, enter ="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.
10. In the Starting Cell input, click the Expression Editor icon next to the Value field and enter ="A5".
11. In the Custom Cell Positions input, click the Expression Editor icon next to the Value field and enter ={"A1", "B1", "A2", "B2"} in the.

12. 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.

Configure Node Outputs

1. On the Outputs tab, click the New Document result and save the activity class as a new process variable. This process variable will be used when setting up our second Export to Excel smart service.

2. 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
   

3. Click Ok.

Add a Second Export to Excel Smart Service

To add a second sheet to our Excel file, we are going to add another Export to Excel smart service to our process.

1. Drag a Export Data Store Entity to Excel smart service after the first smart service on the canvas.
2. Click on the new smart service node and in the Data tab, click the Data Store Entity input to enter a Data Store Entity in the Value field.

3. 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)

4. Click the Document to Update input, and in the Value field select the process variable you used to store your new document in the first smart service. You don't have to enter values for the Document Name and Save in Folder inputs if you are updating a document.

5. 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.

6. Click Ok.
7. Save and publish your process model.

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.