Pentaho excel input from multiple sheets to textfile

Question

I have an Excel workbook containing departmental figures on various sheets. I want to read all the data from every sheet and send it to rows in a text file with the first column being the sheet name (dept name). Can this be done? I only get one of the specified sheets when I set up an Excel Input and add them to the import, and I'm not sure how to add the sheet name as a row in the output text file. The only difference between any two sheets is the sheet name.

The output text file should be like this:

Sheetname1;Sheet1:A1;Sheet1:B1

Sheetname1;Sheet1:A2;Sheet1:B2

Sheetname2;Sheet1:A1;Sheet1:B1

Sheetname2;Sheet1:A2;Sheet1:B2

etc.

Answer 1

It's possible to use numerous Excel inputs with essentially the same settings, with the exception of the sheet name and data start.

You can adjust the metadata and column order so they exactly align with one another once each input phase is completed. Then, simply perform a union with any step. I like to accomplish it with a Dummy.

Example: