Links may not function; however, this content may be relevant to outdated versions of the product.
How to parse a comma-separated values (CSV) file by using a file service
Text files that contain sets of values separated by commas are known as comma-separated values (CSV), files. CSV files can be created from spreadsheets in Microsoft Excel. The following example is of a CSV file exported from Microsoft Excel:
123456789,Clifford,Musinger,9/13/1984,[email protected],02144,"A history of flood, fire, and burglary damage"
738293783,Frida,Marwick,5/3/1969,[email protected],28290,"This customer has a history of late payment, and fraud"
In this example, the first and third entries contain commas in the last field. As a result, Microsoft Excel placed double quote characters. The Parse Delimited rule type in Process Commander can parse these rules.
In this example, an insurance agent wants to automatically create several policies for customers. The customer information is contained in the CSV content above. After the CSV file is imported to Process Commander, a new work object is created for each customer. The new work objects are placed in a workbasket that is specific to imported customers. A Parse Delimited rule, Service Package, File Service, and File Listener will all be used.
Update: This example uses V5.5 features. For a V5.3 approach using function rules, see How to parse Comma-Separated-Value (CSV) files.
The following steps are needed to complete this example:
- Create the Parse Delimited Rule
- Create the service activity
- Create the file service components
- Service Package
- File Service
- File Listener
Create the Parse Delimited Rule
The Parse Delimited rule is used to indicate which properties each field of the CSV file should be mapped to. To create a new Parse Delimited rule:
- In the toolbar, navigate to Application > New > Rule > Integration-Mapping > Parse Delimited.
- Complete the New Instance of a Rule dialog box. The Namespace and Record Type fields define the second and third key parts of the rule.
- Click Create.
- Complete the Parse Delimited rule form.
The Parsing Details area contains the fields that define which properties the contents of the CSV file are mapped to. Parsing is done from left-to-right. Each field should be listed accordingly. Save the Parse Details rule form when you are finished.
Create the Service Activity
When a file service processes a file, it can be configured to perform an action on each record of the file, one record at a time. In addition to parsing the CSV file, the file service must also create a work object for each record.
To create the service activity:
- Define a new activity rule by using the toolbar to navigate toApplication > New > Rule > Technical > Activity.
- Complete the New Rule Instance dialog box. Specify your work class as the Applies To class.
- Complete the Activity rule form.
In the Method field, enter Call svcAddWorkObject to call the svcAddWorkObject function. This function creates a work object using the Flow rule passed in the FlowType field. In this example, the FlowType is set to BatchNewPolicy, which is the Flow rule to add the created work objects to a workbasket specifically for new policies (NewPolicies in this example) created by using this method. Set the workPage to pyWorkPage. Save the Activity rule form.
Create the File Service Components
Several components are necessary to enable Process Commander to automatically find and process a CSV file:
- A Service Package defines both the second key part of service rules and access to the service.
- A File Service processes the file.
- A File Listener monitors a specific folder within the file system and initiates the File Service when a matching file is found.
Create a Service Package
To create a Service Package data instance:
- Locate and expand the Integration-Recources group in the Rules by Type explorer. Right-click Service Packages and select New.
- Define a Service Package Name on the New Rule Instance dialog box.
- On the Context tab of the Service Package form, enter your Access Group in the Service Access Group field.
- Save the Service Package form.
Create the File Service Rule
To create a File Service Rule:
- In the toolbar, navigate to Application > New > Rule > Integration-Services > File Service.
- Complete the New Rule Instance form.
Specify the Service Package you created in the previous step in the Customer Package Name field. Complete the Customer Class Name and Customer Method Name fields to set the second and third key parts of the rule.
- Click Create.
- Complete the Service tab by entering the work class in the Page Class field.
- Complete the Method tab. Specify \r\n in the Record Terminator field. Microsoft Excel inserts a carriage return and new line at the end of each record in a CSV file. Combined with the "record at a time" Processing Method, this record terminator instructs the File Service to begin processing a new record when a new line is reached.
- Complete the Request tab. The Parse Segments Section defines how each record of the file is processed. To parse the fields in each record according to the Parse Delimited rule defined earlier, select Delimited ParseRule in the Map To field and Parse.NewPolicy in the Map To Key field. After each field is mapped to a property, a new work object should be created. Specify the BatchPolicyCreate activity in the Activity field. Save the File Service rule form.
Create the File Listener
The file listener will monitor a defined folder within the file system of the server for files of a specified type. Upon locating a new file, the listener initiates a file service.
To create a new File Listener:
- Navigate to Application > New > Rule > Integration-Resources > File Listener.
- Complete the Listener Name field, and click Create.
- Complete the Properties tab of the File Listener form. Complete Source Location and Source Name Mask fields. These fields define which folder is monitored and the file type to be watched for. (Note: use the asterisk (*) wildcard to monitor all files of a specific type). The Concurrent Threads field can be left with the default value. Specify the File Service rule that you created by selecting the first, second, and third key parts (Service Package, Service Class, and Service Method, respectively).
- Save the File Listener form.
- Click the Source Location exists and can be found.
- Optional. Complete the Process tab. Change the Polling Interval to define how often the file listener checks the Source Location. (Note: Lower numbers might negatively affect performance.)
Test the System
To test this configuration, start the listener, then add a .csv file to the specified location while monitoring the file service rule with the Tracer utility. After the file service has successfully completed, examine the contents of the NewPolicies WorkBasket.
- Start the file listener.
- Access the System Management Application by navigating to Tools > System Management Application.
- Click the Node your system is running on. In this example, the Node name is Potomac.
- Select Listener Management from the left-side navigation panel.
- Select your file listener from the Available Listeners dropdown box, and click Start.
- Your listener will appear in the Running Listeners table if it has been successfully started.
- Trace the file service.
- Open the file service created earlier.
- From the toolbar, click Run > Trace Open Rule to open the Tracer.
- Place a .csv file in the source location.
- After some time, there will be activity within the Tracer. Three entries with two steps each correspond to each record in the .csv file. Click an entry in the Step Page column to see the contents of the corresponding record parsed into properties.
- Access the WorkManager portal and view the contents of the NewPolicies WorkBasket. These policy applications can be opened and completed by the agent.