CODEWITHSUNDEEP
kettle
xiaoaowanghu
xiaoaowanghu

Reputation: 37

Kettle: How to insert the a specific date string from csv file to date field of a table

I'm using Kettle to import data from csv file to oracle database. Now I face a date string in the csv file as below:

01-SEP-18 11.47.18.032000 AM ASIA/SHANGHAI

And the corresponding field type of the table is Date or Timestamp.

How could I use Kettle to handle this？

Upvotes: 1

Views: 446

Answers (1)

AlainD
AlainD

Reputation: 6356

Use a time format with time zone :

In the CSV Input step select the date format to be yyyy/MM/dd HH:mm:ss.SSS XXX.

If it does not work (maybe it does not undestand AM/PM) , read the date as a String in CSV Input step. Then use a Select value to change the type from String to Date in the Meta-Data tab. You have plenty of option and selecting the one you need may request some trials.

If it still does not work (because it does not understand ASIA/Shangai or because it changes), then read the String in a Javascript step in where you can use the str2date function [open the Transform function / Date function, right click on the st2date(var,var,var,var) and select Sample to get the function description].

If it still does not work, then use the Javascript step to parse the string and compute the correct result. Quite frankly, you will probably not need to use this last resort.

Upvotes: 2

Related Questions