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)
   Discard()
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.