This content has been archived.

Importing data from a CSV file to a repeating grid layout by using FilePath and Parse-Delimited (Internet Explorer)

This article applies to Pega 7.1.6. For a similar procedure using PRPC 5.5.x, see How to parse a Comma-Separated-Values (CSV) file using a file service.

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 flow action 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.
      Section pzUploadFilePath applied to CSVLoadExample, 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.

      Create text property .FilePath
      Property Record Configuration for text property .FilePath
      Edit value list property .FileDatafile
      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 data transform .SetUploadedFileName
    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 CSVLoading for assignment action
    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
    Delete the run activity UploadFile for flow action pzUploadFilePath
    Specify data transform for flow action post-processing
    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.
    Specify .FilePath text field for MappingCSV form
    MappingCSV form displays the .FilePath text field
  7. View the CSVloading work form:
    View 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 295801_csvloading_mappingcsv_submit.png
    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 an activity 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
    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 PageList form
    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.
      Specify Integration Mapping as Parse Delimited
      Create the Parse Delimited record for type CSV
  3. Edit the Parse Delimited record to append new rows and specify the parsing details.​​
    Edit the Parse Delimited record to add rows
    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
        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 pre-processing action
    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
    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
    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>
      Edit activity loop step to call Apply-Parse-Delimit
      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.

80% found this useful


Related Content

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.