Dynamic naming of excel sheets using pentaho kettle

Question

I have a transformation with sequential steps of writing data from a table input step to Excel sheets using Excel writer step.

The sheet names are basically provided in the sheet name box in content tab which was reflecting in the spreadsheet.

Instead of per-defining the sheet name, is there any possibility that the sheet names can be dynamically taken from column value of the table.

Ex: say there is a table section and columns section_name , stud_name so i need to show section names as excel sheet name and student names in that section for that particular sheet.

so each sheet represents different section names with student data of that class.

Can anyone help me how to do this in kettle?

Thanks in advance

Answer 1

In order to dynamically define the Sheet Name of an "Excel Output / Writer Step", you can do it by assigning a variable to the "Sheet Name".

In your case, you first create a Kettle Job with two transformation step. In the first transformation, use the code to define a variable (say : COLUMN_NAME). The value in this variable will be used as the sheet name.

In the second transformation, use the variable defined in the first ktr (i.e. COLUMN_NAME) as the sheet Name. check the image below for the Excel Writer Step:

This will give a dynamic sheet name in pentaho. Hope this is what you are looking for :)