Search code examples
excelpentahopentaho-data-integrationpdi

pentaho data integration: How to dynamically split excel file into multiple sheets based on a columns value?

I have an excel file that I want to break up the data by locations and add them to a single excel file as multiple sheets based on their location. I was able to do this by creating multiple filters, one for each location: PDI Sample Image

But what I really need is to dynamically split the data into sheets by location since I will not always know what locations will be included in the next file. the data looks like this
Location | name
BJCC      | Tom
BJCC      | Bill
BJCC      | Ted
CDCC      | Hank
CDCC      | Perl
CS11A      | Everett
CSD2      | Paul

I need to results to appear for each excel sheet by location. I'm a total novice so any suggestions would be helpful. Thanks in advance.

Solution

  • You could use Metadata Injection. So you build a transformation similar to the one on your screenshot, but only with the BJCC filter, and you use another transformation to read the diferent locations from your excel file, injecting the metadata of those locations into the transformation with the BJCC filter, changing the value of the filter and the name of the sheet to write using metadata injection: https://help.hitachivantara.com/Documentation/Pentaho/9.3/Products/ETL_metadata_injection