Featured Post

Applying Email Validation to a JavaFX TextField Using Binding

This example uses the same controller as in a previous post but adds a use case to support email validation.  A Commons Validator object is ...

Saturday, December 18, 2010

Splitting an Excel File with Pervasive Data Integrator

Use Process Variables, the Change Target Action, and a Before Put Record event to split an Excel file into several Excel files based on the data

To take a single Excel file and break it up into several Excel files based on the contents, build a simple process to iterate and run a repeated transformation. The transformation will make use of the Change Target Action which will produce the several target Excel files. A Before Put Record event will issue a Discard command that uses the data as a condition.

This example splits out a set of hotel rows grouped under a brand into individual hotel files.

In this process, there are several Process Variables used in the different steps including the Transformation Step (the map). The process also uses a macro "DATA_DIR".

  1. fileName. Combined with DATA_DIR and brand to form a target file name
  2. brands. A list of brands (data elements) that will drive the fileName and brandFilter
  3. numBrands. The number of brands
  4. i. The current brand index
  5. brandFilter. The current brand
The Initialize Array, Increment i, and i = numBrands steps control the basic loop for the process. After setting up an array of brands (the first column in the spreadsheet), iterate through the array using the brand. The iteration is controled by a counter (i) under numBands.

Set File name and Filter set up two Process Variables, fileName and brandFilter, to be used in the Transformation step. The fileName will be used in a Change Target action called in the Before Transformation event. The Change Target action will use the following connection string

"Database='" & fileName & "';Table=Sheet1;Encoding=OEM;HeaderRecordRow=1"

The brandFilter defines the set of data to put in a particular file. This is implemented using an Execute action on a Before Put Record target event. As the record is about to be outputted, a condition is consulted. If the condition holds the record is skipped via the Discard() function.

If Fields("Brand") <> brandFilter then
   LogMessage("INFO", "Discarding brand=" & brandFilter)
End if

Using a simple Process Designer loop and a few well-placed action, it's easy to create a process that repeatedly calls a map. The map is re-used and the Process Variables (with the validation facility) help reduce errors.

This post is replicated from http://my.opera.com/walkerca/blog/2010/12/02/split-excel. 


  1. Hi Sir,
    I have one scenario where source is text file and target is excel with four different sheets.I have to load the data into multiple sheets in the same excel .
    ex: Source data:
    Here if 101 record should go into sheet1 in excel and 102 record should go into sheet in same excel file.
    Could you please help me on this .

    1. Hi,

      I haven't worked with Pervasive for several years, but this is what I'd try if I were running version 9.

      In Process Designer, get a list of the unique ids that will become individual Excel sheets. Iterate over this list with the unique id providing a target sheet name and a filter criteria in Map Designer. Load each file with the same schema as defined in Map Designer.

  2. Thank you so much for your reply....i got the solution in map designer