How to get the reorder the column with csv input fixed column in pentaho

Question

Scenario:

I have created transformation to load data into table from csv file and I have following columns in csv file:

1. Customer_Id
2. Company_Id
3. Employee_Name

But user may give input file with column ordering (random order) as

1. Employee_Name
2. Company_Id
3. Customer_Id

so, if I try to load file which has random column ordering, will kettle load correct column values as per column names ... ?

Answer 1

Using ETL Metadata Injection you can use a transformation like this, to either normalize the data, or to store it to your database:

Then you just need to send the correct data to that transformation. You can read the header line from the CSV, and use Row Normaliser to convert to the format used by ETL Metadata Injection.

I have included a quick example here: csv_inject on Dropbox, if you make something like this and run it from something that runs it per csv file it should work.

Answer 2

Ooh, thats some nasty javascript!

The way to do this is with metadata injection. Look at the samples, but basically you need a template which reads the file, and writes it back out. you then use another parent transformation to figure out the headings, configure that template and then execute it.

There are samples in the PDI samples folder, and also take a look at the "figuring out file format" example in matt casters blueprints project on github.