Table of Contents

Article

Configuring an application to bulk upload data by using an Excel spreadsheet

Newly created applications require data before they can be used. For example, an application might require creating multiple operators and multiple access groups, loading multiple data instances, and so on. You can bulk upload this data into the application by using an Excel spreadsheet instead of manually entering it. To do this, you can use Pega 7 Platform features to download an Excel template that can be populated with the required data, and then uploaded back into the application.

To use Excel for uploading data in bulk, you must create the following items:

  • An Excel template
  • A binary file to save the Excel template
  • Download and upload activities
  • A user interface section that uses controls for downloading and uploading the template
The example used in this article shows how the components fit together. You need to customize your implementation based on your application requirements.

Excel template format

The Excel template must be in Open XML format with the .xlsx file extension.

Enter the header information in the first row of the spreadsheet. This row is optional, but include it to avoid confusion about the contents of each column. The second row specifies the Pega 7 Platform properties that correspond to the data in the column. The properties must include the keyword input (required by Excel) after the property name and be enclosed in braces, for example:

{.pxResults().pyUseridentifier input}

Sample Excel file

Sample Excel template

When the file is downloaded from the application, only the header row is displayed.

Creating a Binary File rule for the template

  1. Click Create > Technical > Binary File.
    Create Binary File rule
  2. Enter a name in the Label field.
  3. Enter excel in the App name field.
  4. Enter xlsx in the File type field.
  5. Click Create and Open.
    Upload template file
  6. Click Upload File.
  7. Click Choose File.
  8. Navigate to the Excel template file that you created and click Open.
  9. Click Upload File.
  10. Click Save.
  11. Click Download file to download the file and make sure that it is correct.

Configuring the control and activities

You configure the download and upload buttons by using the MSOFileTransferButtons control that is provided with the Pega 7 Platform. This section explains only the required parameters. You might have to configure other parameters based on your application requirements.

  1. Add the MSOFileTransferButtons control to the section where you want the download and upload buttons to appear in your application.
    Download and upload button configuration
  2. On the Parameters tab, enter the name of the download and upload activities.
  3. Use the MSOGenerateExcelFile activity for the download activity to generate an Excel file by using the template Excel file. This is the activity that is called when you click Download in the application.
    Download activity configuration
  4. In the Step Page fields, enter the context of the page.
  5. In the FSFileName field, enter the file name to use for the downloaded file.
  6. In the TemplateRFB field, enter the name of the template that you created and uploaded in the Binary File rule in the following format: "excel!<file>!xlsx"
  7. ​Use the MSOParseExcelFile activity for the upload activity to parse the uploaded Excel file by using the template Excel file. After the uploaded file has been parsed, the list of parsed items is available on the embedded page list on the step page and can be used in your application.
    Upload activity configuration 
  8. In the Step Page fields, enter the same page context that you entered for the download activity.
  9. In the FSFileName field enter pxRequestor.pyFileUpload. This is the hardcoded location where the Excel file is uploaded.
  10. In the TemplateRFB field, enter the name of the template that you created and uploaded in the Binary File rule in the following format: "excel!<file>!xlsx"​
  11. Click the Pages & Classes tab and complete it based on your application configuration. In this example, Code-Pega-List is used for the step page, pxResults() is used to create the bulk operators. The pxResults() and Operator ID properties are the same ones that are used in the template file input fields, for example, {.pxResults().pyUserIdentifier input}
    Pages & classes configuration 

Published September 21, 2015 — Updated February 2, 2016


88% found this useful

Related Content

Have a question? Get answers now.

Visit the Pega Support Community to ask questions, engage in discussions, and help others.