Importing a comma-separated values (CSV) file of data into your application is a common way to efficiently reuse and load data into your application. A CSV file is a standard import and export format particularly suited to handle a large volume of data, for example, data in a Microsoft Excel spreadsheet.

A common scenario is to import the data of a CSV file into a section of an application work form that is designed as a repeating grid. The repeating grid layout in a section of an application is particularly suited to loading CSV data.

Restriction

The procedure described in this article works on Internet Explorer only. It does not work on other browsers.

Prerequisite

This solution supports simple CSV data only. It does not support CSV header information. Therefore, you must define each property item before you begin the procedure described in this article.

Preview

This solution uses a flow action as each step, but you can modify it to use multiple processes on one step by using the Process Modeler Utility shape.

To import data from a CSV file to a repeating grid layout in your Pega 7.1.6 application, do the following tasks:

1. Upload the CSV file by using the FilePath control
2. Create an activity to read each line of the CSV file into a Clipboard property
3. Parse the data from each line of the CSV file to the repeating grid layout

Upload the CSV file by using the FilePath control

1. Copy a flow action and a section from an existing class to your Work class.
   1. Search for old:pzUploadFilePath to find Flow ActionpzUploadFilePath and Section pzUploadFilePath.
      ​
      Search results for text files for flow action and section named pzUploadFilePath
   2. Copy these records (Save As) to your Work class, CSVLoading.
      
      Specialize the section pzUploadFilePath for the app layer, work class, and ruleset
2. Create two properties for uploading the CSV file:
   • FilePath (Text)
   • FileData (Value List)

     The Action pzUploadFilePath uploads the CSV file under the \prpc_temp folder. These property names are used by the Java code that is generated later.

     
     Property Record Configuration for text property .FilePath
     
     Edit Property .FileData for value list type text
3. Create a data transform called SetUploadFileName and specify the FilePath (target) as a local property:
   @(Pega-RULES:String).substring(pxRequestor.pyFileUpload, 0, @indexOf(pxRequestor.pyFileUpload,".csv") + 4)
   
   Create a data transform and specify the target as .FilePath
4. Edit the Case Type: CSVloading,specifying the Action as pzUploadFilePath in the Assignment: Choosing File section.
   The flow action applies the section pzUploadFilePath as well.
   
   Edit case type CSVloading for assignment ChoosingFile action pzUploadFilePath
5. Open the Flow Action pzUploadFilePath, delete the Run Activity UploadFile, and set the Data Transform to SetUploadFileName, which is the data transform that you created in Step 3.
   
   Delete the run activity UploadFile for flow action pzUploadFilePath
   ​
   Data transform SetUploadFileName as a postprocessing action for flow action
6. In the Assignment: Mapping CSV, Form Configuration, specify the .FilePath text property as the field to be displayed.
   
   MappingCSV form displays the .FilePath text field
7. View the CSVloading work form:
   
   CSVloading work form with upload file
8. Run the case and specify the .FilePath file, as shown in this example for the Tomcat application server: file://web:/StaticContent/global/ServiceExport/CSVSampleData.csv
   Run CSVloading and submit the sample CSV file
9. Click Submit.
   The CSV file is sent to the PRPC folder. Use your operating system file manager to search for the file: (pxRequestor.pyFileUploadPath file://web:/StaticContent/global/ServiceExport/ . . .)

Create an activity to read each line of the CSV file into a Clipboard property

1. Specify the following Java source code for the activity to read the CSV file to the FileData ValueList:

   //Load a file into a String Value List

   try 

       // get path of the file

       java.util.HashMap hmFileInfo = pega_rules_utilities.uploadFile(true);

       String fileName = (String)hmFileInfo.get("FileName");

       tools.getStepPage().putString("pyFileName",fileName);

       String FileData = (String) hmFileInfo.get("FileData");

       byte[] bytes = Base64Util.decodeToByteArray(FileData);

      // tools.getStepPage().putString("ExcelFile", new String(bytes));

       String csvData = new String(bytes);

       java.io.BufferedReader reader = new java.io.BufferedReader(new java.io.StringReader(csvData));

       ClipboardProperty fileData = tools.getStepPage().getProperty("FileData");

       String thisLine = null;

       while((thisLine = reader.readLine()) != null) 

           fileData.add(thisLine );

       

       reader.close();

    

    catch (Exception e) 

       oLog.error("Exception in the UploadCSVFile activity:",e);

       throw new PRRuntimeException("Error during file upload",e);

2. Create the activity for reading the lines of the CSV file.
   This activity is called as a postprocessing step in the MappingCSV flow action.
   The example activity shown here is called LoadingFile.
   
   Create activity called LoadingFile
3. Edit the flow action MappingCSV to specify the activity LoadingFile as the postprocessing action.
   Select the Look for an assignment to perform? check box.
   
   Edit the flow action MappingCSV

Parse the data from each line of the CSV file to the repeating grid layout

To populate the reading area of the repeating grid with the CSV data, follow these steps:

1. For the case type CSVloading, use the Form wizard to edit the fields that you want to display on the ParseCSV form:

   For .ReadCSVData, specify the list of objects, for example:

   Edit display fields of ParseCSV form

   CSVPrefecture	Text	Optional
   CSVCityName	Text	Optional
   CSVCityID	Text	Optional
   CSVRegistDate	Text	Optional

   
   Create the PageList form for the case type CSVloading
2. For instances of CSVloading, click Integration Mapping > Parse Delimited to display the Parse Delimited Record Configuration form.
   1. Specify a label for the identifier.
   2. Specify the record type as CSV.
      
      Create the Parse Delimited record for type CSV
3. Edit the Parse Delimited record to append new rows and specify the parsing details.​​
   
   Specify the parsing details
   1. For Field Format, select Comma-Separated Values (CSV).
   2. For Processing Method, select use parse details.
   3. Specify the parsing details for each row of the repeating grid as shown in the example:
      1. Select Required, if needed.
      2. View the Description, which is the Identifier Label that you specified in Step 2a.
      3. For each row, specify the Map To value as Clipboard.
      4. For each row, specify the Map To Key, as shown in this example:
         
         CSV data mapped to Clipboard keys
4. Edit the flow action ParseCSV to specify a preprocessing action that runs the activity that calls the function Apply-Parse-Delimit, for example, MappingCSVCityData.
   Edit flow action ParseCSV to add preprocessing action
5. ​Create the activity that you specified as the preprocessing action in the previous step, for example, MappingCSVCityData.
   ​
   Create activity to map CSV data
6. Edit the activity for mapping CSV data to set the first step loop criteria (.FileData as Value List) for each element in the Value List.
   
   Edit activity step to loop through each element of the Value List
7. Edit the activity for mapping CSV data, the loop step, to call the method Apply-Parse-Delimit and specify the following parameters for this method:
   • Namespace CSVCityParseDelimit
   • RecordType CSV
   • SourceProperty<CURRENT>
     
     Specify parameters for the Apply-Parse-Delimit method in the activity loop step
8. Save all records and run the case.
   The data of the comma-separated-values file that you uploaded and parsed is displayed in a repeating grid layout of your application.