Reading a CSV File in Pega 7 and Populating a Data Table

This post shows how a Service and a File Listener rule can be used to automatically populate a data table based on an input text file (comma-separated values). During processing, the delimited text file is parsed and for each row, a record is created in the target data table. This setup saves manual labor when large input files are used to populate data tables.

• Creating an Auto-Complete Control Backed by a Data Table
• Using a Data Page for a Dropdown and Sorting the Options with a Data Transform in Pega 7.1.9
• Source Dropdown Options from a Data Page and Group by a Property in Pega 7.1.9
• Example of Cascading Dropdowns with Linked Class Key Property in Pega 7.1.6

1. Setup of Data Table in Pega 7 and Description of CSV File Format
2. Creating the Service Rule using the Pega 7 Service Accelerator
3. Implementing the Service Rule to Parse and Process CSV Files in Pega 7
4. File Listener Configuration in Pega 7
5. Running the File Listener
6. Move CSV Files to Source Location and Run File Listener and Service in Pega 7

In this example, an existing data table for a class named ABC-Insurance-Data-Adjuster is populated automatically. The data table serves as a reference for claims adjusters that are contracted for investigating insurance claims. This class has the following properties:

• The screen shot below shows the configuration page when this data table was created using the Data Table Wizard.
• Note that the check box CREATE DEDICATED DATABASE TABLE? is checked so that this static reference data can be migrated with the application.

• Initially, there is no data in the data table and the row count shows 0 in the data table view.

• The gear icon in the Edit column can be used to manually insert instances.
• However, in this example, a Service and File Listener rule are used to automatically insert instances from a sample CSV text file shown below.

• Each delimited row in the file represents a claims adjuster (a person that reviews an insurance claim).
• The structure of the rows must match the properties defined in the data table into which instances will be inserted.
• In addition, the input file has a header row where the fields match the property names of the class of the target data table.

A Service rule needs to be created. This rule will read and parse the input CSV file(s) and then use an activity to insert instances into the specified data table for each input row.

• Navigate to Designer Studio > Integration > Services > Service Wizard to open the Service Accelerator tool.

• Select Process input or output data for the Service purpose and File for the service type, then click the Next button.

• This step requires the selection of the appropriate data class, i.e. the class name of the data table into which instances will be inserted.
• In this case, the data class is ABC-Insurance-Data-Adjuster. The service name in this example is InsertAdjusters and the wizard will use this name for the generated Service.
• Click on the Next button to continue.

• This step is used to define the primary input and output properties. Click on the two check boxes labeled Select FIELD NAME in the Primary Page Input Properties and Primary Page Output Properties sections.
• The property pyLabel is not listed here since it is inherited. This omission will NOT be a problem later on. Click on Next to continue.

• Specify a ruleset and ruleset version for the auto-generated service rules. In this case, the ruleset name is Insurance and the ruleset version is 01-01-01.
• Use the default options Configure a new service package and Configure a new service listener and click on Next to continue.

• The next screen is used to configure the new service listener. The listener needs to be configured to monitor a specified file system directory for CSV input files. In this case, the directory on the Pega (7.1.6) instance, where input files will be located is:

/usr/share/tomcat7/webapps/prweb/data

• This folder may be different for Pega versions other than 7.1.6.
• The Source Name Mask is *.csv and specifies that only csv files will be picked up by the File Listener for processing.

• Review the configuration of the Service and File Listener rules. Individual sections can be expanded by clicking on the plus (+) signs next to the labels.
• In this example, the service type is Rule-Service-File (see Rule-Service-File at Pega Help).
• The service package is AdjusterDataService and the final activity that will be called from the service implementation is called InsertAdjusters (…to insert a record into the data table).

• Scroll down in the dialog to view details of the Final Activity as shown below.
• Note that the Access Group specifies what privileges the generated Services will have.
• The processing mode is specified as stateless, which means that each transaction (i.e. each record read from the file and inserted into the DB table) is isolated and not related to any previous one.

• Since the option Configure a new service listener was selected earlier, the Service Accelerator will also generate a service listener rule. See screen shot below for details.
• The File Listener will monitor a specified folder on the Pega host’s file system:

/usr/share/tomcat7/webapps/prweb/data

• …and automatically read files that match the specified pattern, here: *.csv.
• In this example, the listener is named AdjusterDataListener.
• The boolean Disable Startup? parameter specifies whether the listener will be started automatically when PRPC is started or whether it has to be started manually through the System Management Application (SMA).

• After reviewing the configuration, click on the Finish button to complete the configuration process and to run the Service Accelerator. At this point, the Service and all associated rules will be generated.

• A final confirmation screen, indicating the successful execution of the Service Accelerator, will be shown. Note that Pega 7.1.6 was used for this example and it seems to still use the Pega 6 UI theme for this confirmation screen.

Open the Service rule by entering its name in the search field in the Designer Studio. In this case, the name InsertAdjusters was used during configuration.

• The PRIMARY PAGE section shows the page class and the page name.
• The PROCESSING OPTIONS section shows the execution mode. It is set to Execute synchronously, meaning that the service processes one file at a time.
• The other execution modes allow asynchronous processing of input files (e.g.: placing files into a queue for background processing).

• This tab used to configure how PRPC detects and processes records in the input file.
• Processing Method: is set to record at a time, which specifies that each row in an input file is parsed and then inserted into the data table immediately.
• Record Terminator: a row in the CSV file is delimited by a carriage return and new line character, so \r\n is used here.
• Character Encoding: UTF-8 is selected here since this is the character encoding of the input text file. During processing, PRPC converts the input characters from the specified encoding to Java String encoding (16-bit Unicode).

• This tab is used to specify the Parse Segments. By default, the properties of the page class are listed here and a Final Activity is listed.
• The final activity will be called each time a record from the input file has been parsed.
• In this case, it will be used to insert the record into a data table.

• Important: In this example, a Delimited ParseRule, instead of the default parse segment settings, will be used for parsing.
• Manually remove the parse segments: Click on one of the text fields in a parse segment and then click on the X icon that appears to the far right of the segment row.
• Also remove the entry in the Final Activity field.

• In the Map To auto-complete field of the empty parse segment row, select Delimited ParseRule.
• The Map To Key text field is used to specify the name of the delimited parse rule.
• The naming convention of the delimited parse rule is <namespace>.<file-extension> (case sensitive)
• It will be applied for each row in the input file. The namespace has to match the last part of the page class, e.g.: ABC-Insurance-Data-Adjuster.
• The file extension will be CSV and in this case, the Map To Key value will be Adjuster.CSV.
• In the Activity text field, identify the activity that will be called after the current row is parsed and the data is available on the clipboard.
• Here, the activity InsertAdjusters, which was created by the service accelerator, is called.
• Click on the Save button to save the current settings.

• The next step requires the configuration of the Delimited ParseRule.
• Click on the magnifying glass icon next to the text field in the Map To Key column. This will open a new dialog window to create the rule.

• The label, identifier and record type are pre-filled based on the Map To Key value.
• Click on Create and open to continue.

• The screen shot below shows the configuration of the parse rule. Select Comma-Separated Values (CSV) for the Field Format.
• In the Parsing Details section, create a row for each field in the input file. The order of the parsing rows has to match the order of the fields in the input file.

• So, in the file below, the column ID will be mapped in the first row, the column Firstname will be mapped in the second row and so on.

• The description does not have to match the header values in the input file.
• Select Clipboard in the Map To auto-complete field and then select the property in the page class rule to which the current field will be mapped.
• Make sure the checkbox in the Required column is checked. Complete this activity for each field in the input file as shown below.
• Note that the Lastname field from the input file is mapped to the inherited property pyLabel. This field can be used instead of creating a separate property for the last name.
• Click on Save to save the Parse Delimited rule and then close the dialog.

• It is also necessary to implement the insert activity rule that was specified earlier on the Request tab of the Service rule.
• Again, the activity is named InsertAdjusters. The Service > Request screen is shown below again. Click on the magnifying glass to open the activity rule.
• Note that this activity will be called for each row in the input file, i.e. it will read one row from the file and then insert one record into the data table.

• Open the activity and navigate to the Pages & Classes tab. A page of the class for which instances will be inserted into the data table needs to be declared here.
• In this case, instances of the class ABC-Insurance-Data-Adjuster are inserted.

• Next, navigate to Steps and use the Page-New method in the first step to create a new clipboard page of this class.
• In the second step, select the Property-Set method and enter the name of the page (Step Page) that was created in row 1.
• Under Method Parameters, link the properties of the step page to the properties on the primary clipboard page
• E.g.: .ID refers to the property of the step page and Primary refers to the clipboard page that will be populated by the parse delimited rule for each record in the input file.

• In the third step, the Obj-Save method is used to save the step page, here the ABC-Insurance-Data-Adjuster, into the data table.
• The parameter WriteNow will cause the system to immediately commit the insert operation. Make sure to save the activity rule.

The last part of the setup concerns the configuration of the File Listener rule that was created earlier, when the Service Accelerator was run. The File Listener will be configured to automatically monitor a specified directory on the PRPC file system for input files, in this case files that match the pattern .CSV.

• If a new file is found, the File Listener will automatically invoke the Service file rule to process the input CSV file.
• The screen shot below shows the File Listener details from the earlier Service Accelerator execution.

• Open the File Listener by searching for the listener name, in this case, the Service Accelerator created a File Listener rule called AdjusterDataListener.
• The properties tab shows the listener properties and is pre-filled based on the selections made when the Service Accelerator was run.
• Make sure that the Source Location directory is set properly. This is the directory, the File Listener will monitor for input files.
• In this example, PRPC is running as a Personal Virtual Server, utilizing the VMWare Player (Pega 7.1.6 CSA Exercise System from Pega Academy).
• A FTP client can be used to load input files to the file system of the virtual server.
• For an example on how to setup FTP access to a Pega 7.1.6 CSA Exercise System running on VMWare, see: Example of FTP Connection to Pega 7 PRPC for Private Virtual Server Installations.
• Click on the Test Connectivity button to confirm that the listener is configured properly and that the file source location is accessible.

• A confirmation dialog as shown below should appear and display the message Connectivity test successful.

• If the connectivity test fails: For Private Virtual Server installations: Use the VMWare player to log into the virtual machine (architect, password or root, install) and create the input file directory and set appropriate access privileges using the commands:

sudo mkdir /usr/share/tomcat7/webapps/prweb/data

sudo chmod 777 /usr/share/tomcat7/webapps/prweb/data

• In the below example, the data directory is now accessible by owner, group and everybody for reading and writing.

• The File Listener will start monitoring the source location as soon as it is started (…when running the Service Accelerator, there is an option to specify whether the listener will start automatically when PRPC is started. In this case this option was set to false.).
• To start the File Listener, open the System Management Application (SMA) by navigating to Designer Studio > System > Tools > System Management Application

• In the list of Nodes, click on a node name to load the configuration for that node. In this case, there is only one node called localhost.
• If the list of nodes is empty, the Pega node under which the Service and File Listener rules were created needs to be added.
• This can be done by clicking on the plus (+) icon in the upper left hand corner of the SMA window.
• In order to start the File Listener, click on Listener Management in the navigation menu that appears once a node is selected.
• Then select the listener from the Available Listeners drop-down control and click on the Start button in the section above it.
• Once the listener is running, it will be listed in the Running Listeners section as shown below.

• Note: The File Listener can be configured further: the Process tab allows to specify the polling interval, i.e. how often the listener will check the source location for new input files.
• The Generate report file? check box can be checked to have the listener create an XML based report file (in the specified source location). The report shows details regarding input file processing such as parsing errors.
• The File Operation parameter in the CLEANUP section can be used to specify what the listener does with input files that have been processed. In the below screen shot Delete is selected so that the listener will remove the input files from the system.

• If changes are made while the listener is running, it is required to restart the listener in order for the changes to take effect.

At this point, there are no input files in the directory the File Listener is monitoring. A FTP client such as FileZilla or the command line can be used to copy input files to the directory. In this case, PRPC is installed as a Personal Virtual Server, so the PRPC instance is running in a virtual machine with its own IP address, here 192.168.241.128.

• By default, the virtual machine is running a FTP server and the default architect, password or root, install credentials can be used to connect to it via FTP.
• The screen shot below shows the FileZilla configuration to connect via FTP to the virtual machine.
• For an example on how to setup FTP access to a Pega 7.1.6 CSA Exercise System running on VMWare, see: Example of FTP Connection to Pega 7 PRPC for Private Virtual Server Installations.

• Copy the input CSV file(s) to the source location that was specified earlier. Here it is:

/usr/share/tomcat7/webapps/prweb/data

• The listener will pick up the file(s) and invoke the Service to parse and process it.

• After processing has been completed, the same directory may contain XML files (in the case of processing errors). These files can be used to debug parsing errors etc.
• In the System Management Application, the Request Count in the Running Listeners section will show how many files the listener has processed since it was started.

• Finally, when the Data Tables view is reloaded, the Row Count for the target data table will reflect the records that were inserted.

• Clicking on the gear icon to the right of the row count will open the data table and show the inserted records.

• These match the 4 records from the CSV input file.
• The data table can be used to populate UI controls such as auto-complete and drop down fields. For an example see: Creating an Auto-Complete Control Backed by a Data Table.

• Note: If the listener was configured to generate a report file (see Process tab > Generate report file?), it will be available in the same input file directory in a subfolder called report.
• The report file will have the name of the input file with the file extension .rpt.
• A sample report file is shown in the screen shot below. The report contains data such as the number of rows that were inserted into the data table, the exact file size in byte and the listener name that processed the input file.

• Note: If needed, an activity can also be created to purge existing data in the data table before new data from a CSV file is imported.
• This cleanup activity can be called using the Initial Activity field in the PROCESSING PROLOG section of the Service rule as shown below.