View Only

 Excel file transformation: columns to rows

  • Pentaho
  • Kettle
  • Pentaho
  • Pentaho Data Integration PDI
nico dado's profile image
nico dado posted 05-28-2021 22:08

Hi all,

I need any tips on this specific use case. 

I have an excel file with this structure:



the goal is to load data into this table:



where each table row should contain part of excel row values (for "Department ID", "Annotation 1", "Annotation 2", "Type") and part of excel column values ("date" with dd/mm/yyyy and "qta" with corresponding value).


The number of "dd/mm/yyyy" columns is undefined and it is not known the value.

Any idea? 

Thank you in advance,


Ana Gonzalez's profile image
Ana Gonzalez

You'll have to work with Metadata Injection, first to read the excel file and pass to the excel input step all the date columns to read and then to the row normaliser step the names of the columns to normalise as date (and the values, that would be the same as the column name)

nico dado's profile image
nico dado

Thank you Ana!

I'm a little bit confused about when and how to use metadata injection in my transformation flow.

I suppose before to read excel file, since the "date" columns are variables. It's so?

Do you have any examples that I can follow?


Ana Gonzalez's profile image
Ana Gonzalez

You have a samples folder in your installation. Inside the transformations folder, you have the meta-inject folder with an example of how metadata injection works, precisely to read a csv file. Read the documentation about metadata injection also.

When creating a new transformation with metadata injection, I like to check the box to generate a transformation with the metadata injected to debug what I'm doing.

nico dado's profile image
nico dado


Thanks Ana for your suggestions.