Kettle (spoon) - get filename for excel output from field in the excel field in input

Question

I'm trying to process an excel , I need to generate una excel file for each row and as filename I need to use one of the fields in the row.

The excel output hasn't the option "Accept filename from field" and I can't figure out how to achieve it.

thanks

So all the data of an individual spreadsheet is contained in one row? Are you planning to create spreadsheets with only one row of data? — matthiash, Sep 23 '16 at 13:58

score 0 · Answer 1 · answered Sep 22 '16 at 15:56

The issue is that the step is mostly made for outputting the rows to a single file, not making a file for each row.

This isn't the most elegant solution but I do think it will work. From your transformation you can call a sub-transformation (Mapping) and send a variable to it containing the filename. The sub-transformation can simply do one thing: write the file, and it should work fine. Make sense?

score 0 · Accepted Answer · edited May 23 '17 at 12:19

You need to copy the rows into memory and then loop it across the excel file to generate multiple files. You need to break your solution to 2 parts. First of all, read all the rows from Excel Input step into "Copy rows to Result" step as a variable. In the next transformation, use the same variable to use it as a file parameter.

Please check the two links:

SO Similar Question: Pentaho : How to split single Excel file to multiple excel sheet output
Blog : https://anotherreeshu.wordpress.com/2014/12/23/using-copy-rows-to-result-in-pentaho-data-integration/

Hope this helps :)

Kettle (spoon) - get filename for excel output from field in the excel field in input

2 Answers2