How to load multiple excel sheets into different tables using pentaho metadata injection

0
votes

I have one excel file which is having 5 different sheets, i want to load all 5 sheets into different table using pentaho meta-data injection.

Note: I have implemented normal approach of repeating flow 5 times.

What i have tried

  • 1) I have created another excel sheet with meta-data of all 5 sheets

    2) I am able to pass sheet-name as a run time variable and can able to replace it into sheets property

    3) I am stuck @ how to read corresponding meta-data file and replace into template.

Any solution is appreciated.

1
pentahopentaho-spoonpentaho-data-integration
Do these 5 sheets always have the same name ? - Cristian Curti
Yes, it will have same name. - Helping Hand..
If they are fixed names, i don't see why you'd need to make the entire Metadata Injection templates and workflows. Read the file 5 times, one for each Sheet, with Metadata Injection the execution will be the same. If it's simple read and output to table, then it's much easier to read it 5 fixed times, it also makes it easier for maintenance in case you need to change the name of a sheet or layout. - Cristian Curti
I have already mentioned that i have that solution with me, i just want to create meta approach to make a generalised solution in-case sheet-name became dynamic and number of sheet increases. - Helping Hand..

1 Answers

0
votes

Have you followed Pentaho's example on how to use Metadata Injection? It also uses excel sheets to store metadata, and the name of the sheet is passed at runtime and applied by the use of a join.

In my experience, I've stored the metadata in a MySQL table and selected the corresponding one by the use of a Variable set in a previous transformation, depending on the format of the input file.