Reading CSV input file by field name instead of position in Pentaho Data Integration

Question

I have a CSV file with four fields:

FIELD_A, FIELD_B, FIELD_C, FIELD_D
1, 2, 3, 4
5, 6, 7, 8

I also created a transformation in PDI that has a Read CSV Input file step and a following step that inserts the values in a database table.

This works fine, however I may get other combinations in the input file (without notice), such as:

FIELD_A, FIELD_B, FIELD_D
1, 2, 4
5, 6, 8

I was assuming that PDI was going to be intelligent enough to map the fields by name, and send to the next step FIELD_A, FIELD_B, and so on, depending on the position of the column header. Instead, it takes the first three fields in the file and maps them to the first three fields in the step. As a result, I get in FIELD_C the values of FIELD_D.

Is there a way to have the transformation map by field name instead of position? I cannot change the transformation every time an input field is received.

Answer 1

There have many way to do this. Use 'Select Values' step before insert to DB is the simple way. You can find the sample transformation from Here. Where in the 'CSV file input', you can first call file with 4 field and after that you can call file with 3 field for testing. Let me know if its ok or not.

Answer 2

You have also the option of injecting metadata to the transformation to read the CSV file and insert in the table. You first create a transformation to read which columns are present in the CSV file (reading only the first line of the CSV with the column headers) and then injecting that information to the Read CSV step and Output table step in your transformation.