Categories

A sample text widget

Etiam pulvinar consectetur dolor sed malesuada. Ut convallis euismod dolor nec pretium. Nunc ut tristique massa.

Nam sodales mi vitae dolor ullamcorper et vulputate enim accumsan. Morbi orci magna, tincidunt vitae molestie nec, molestie at mi. Nulla nulla lorem, suscipit in posuere in, interdum non magna.

Converting ISO-8601 date strings to Date in Pentaho Kettle

So I was working on a conversion from a CSV dump from a student system to our internal label database. New school year, new students. I like to use the time format yyyy-MM-ddTHH:mmZ (ISO-8601, a standard format) for strings, but Pentaho Kettle has no explicit conversion from an ISO-8601 string date to a date type variable.

You would think this would be easy. Not really. Java Date conversion is simple, so could I use the Java Class transformation? Javascript? I Googled a lot, and considered writing a custom conversion plugin — after seeing the API, not so much – a multichannel conversion is a LOT more complex than a simple string parser. Then I read ISO-8601 date format support and Dealing with Dates in Pentaho and found hints. Buried in there, the conversion.

Use a “Select/Rename Values” Kettle transformation. “Fieldname” gets the ISO-8601 value from the input stream, “Rename to” is the output stream field (I used “date”), Type is “Date”, and “Format” is “yyyy-MM-dd’T’HH:mm’Z'”. Note the ticks. Very strangely escaped.

Works like a charm.

Leave a Reply