Export Multiple Data Store Entities to Excel

This page will walk you through how to export one or more Data Store Entities (DSEs) to Excel using the Export Data Store Entity to Excel/CSV smart service.

Using the Export to Excel/CSV Smart Service

The Export Data Store Entity to Excel/CSV smart service (we will refer to it as "Export to Excel/CSV" for short) can write data to a new document or update an existing document. You can specify which folder the document will exist in.

In this example, we will export two DSEs (one DSE per sheet) in a single Excel file. The exported Excel file will have two sheets with the queried data (one about customers and another about customer order details) from the DSEs. If you want to export multiple DSEs to a CSV, the data will be appended to the bottom of the file.

Add an Export to Excel/CSV 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/CSV 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 Source 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 view access to this folder will be able to see this document. For more information on configuring folder security, see Knowledge Center Security.

  9. In the Output Format input, you can change the document format to CSV if you want to export your data to a CSV file. It is set to Excel by deafult.

    Note: The CSV Delimiter input is specifically used for CSV formats. You can define your seperators in this input if you want to export a DSE to CSV format.

  10. 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.
  11. In the Starting Cell input, click the Expression Editor icon next to the Value field and enter ="A5".
  12. In the Custom Cell Positions input, click the Expression Editor icon next to the Value field and enter ={"A1", "B1", "A2", "B2"} in the.
  13. In the Custom Cell Vaules 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/CSV 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 rule, 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/CSV Smart Service

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

  1. Drag a Export Data Store Entity to Excel/CSV 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 Source 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.

FEEDBACK