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.

This entry was posted in Computers, Software and tagged , , , , , , , , , , . Bookmark the permalink.

Leave a Reply