String field to more specific type: suggestion?

[nico.ben]

Hi,
I have an excel sheet containing columns with a defined type, but it occurs that some values are string ("N.A." = not available).
For example:

I have a date columns with these values:
20-06-2010 , 21-04-2010, N.A., 01-05-2009

The same for number type:
34.5, 12.4, N.A., 6.7

My problem is that I can workaround using Javascript and Select steps, but it seems to me a not practical method, because it must be done for each field (I have 73 of them!).

Is there a way to instruct PDI so that: "This column has a date field and the format is ....., if you find something different, put a null value in it".

I tried to set a type in the excel step and set ignore errors, but it didn't work.

I use 3.2.0.GA

Any suggestions, please?

Thank you
nicola

[slawomir.chodnicki]

Hi there,

The excel input step in Kettle 3.2 will do what you want *if* you give it a writable directory where it can log its conversion errors to. I've attached an example. After you adjust your error directory in the "Error Handling" tab of the step settings, you should be able to preview the step and it should read in the example excel file properly.

Cheers

Slawo

[nico.ben]

That's what I need.
Thank you very much!
Best regards,
Nic