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".
- fileName. Combined with DATA_DIR and brand to form a target file name
- brands. A list of brands (data elements) that will drive the fileName and brandFilter
- numBrands. The number of brands
- i. The current brand index
- brandFilter. The current brand
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)
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.