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 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
Excel template format
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 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 template
When the file is downloaded from the application, only the header row is displayed.
Creating a Binary File rule for the template
- Click
- Enter a name in the Label field.
- Enter excel in the App name field.
- Enter xlsx in the File type field.
- Click
- Click .
- Click .
- Navigate to the Excel template file that you created and click .
- Click .
- Click .
- Click 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 Platform. This section explains only the required parameters. You might have to configure other parameters based on your application requirements.
- Add the MSOFileTransferButtons control to the section where you want the download and upload buttons to appear in your application.
- On the Parameters tab, enter the name of the download and upload activities.
- 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 in the application.
- In the Step Page fields, enter the context of the page.
- In the FSFileName field, enter the file name to use for the downloaded file.
- 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"
- 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.
- In the Step Page fields, enter the same page context that you entered for the download activity.
- In the FSFileName field enter
pxRequestor.pyFileUpload
. This is the hardcoded location where the Excel file is uploaded. - 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"
- 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}