Pentaho : How to split single Excel file to multiple excel sheet output

Question

I have a list of employee details. I want to split each employee detail in separate Excel sheet. I tried a lot but I get only separate Excel files alone, not all the sheets in a single file by using Pentaho kettle.

Eg:

Raja    22 developer  25000
ravi    23 tester      2000
karthik 24 designer    4000 
Mani    28 developer  45000

In that each employee details will need separate sheets in a single excel file. Already, I worked with "MS excel writer" but it did not work.

EDIT

Thanks for your valuable reply,its really clear and more useful. :-) But i need one more detail from you,if i added age,skill ,salary columns into the get variables, after run the job ,i didn't get the values of these three fields only their column names shown in every sheet. I need to include their column values in each sheet

example : sheet 1 : (raja)

Name age skill salary Raja 22 developer 25000

sheet 2 :(ravi) Name age skill salary ravi 23 tester 2000

Like that i need to generate each sheets,I hope you get my point. Can you please help me how to i generate that.

I removed your plea to send answers to your mail address since the point of SO is to have the answers here on the site. If you like you can still provide your mail address in your SO profile. — Marcus Rickert

Rishu Shrivastava Rishu Shrivastava · Accepted Answer · 2014-12-04T20:53:03

You need to copy the rows (employee names) into memory and then loop it across the excel file to generate multiple sheets with employee names. I have uploaded the codes in this location. You can view it.

First of all, i took an excel input and used "copy rows to result" step to load the data to the memory.

enter image description here

In the Second Step, loop all the data in the memory and write it across the excel file. You can loop the file by enabling the 'copy previous results to parameter' and 'execute for every row' will run for every single row. Check the image below:

enter image description here

Finally when writing the files in an excel file (using Excel Writer Step) make sure that every time a row is coming from prev. step is getting inserted into the same file. Check the below image for this.

enter image description here

I have uploaded a sample code in drive. Hope it helps :)

Pentaho : How to split single Excel file to multiple excel sheet output

1 Answers