CODEWITHSUNDEEP
pentahopentaho-data-integration
Akshay
Akshay

Reputation: 359

Pentaho date format issue

my input excel sheet has the field with two different types of values column in the format YYYY/MM/DD

enter image description here

Now, when I have added the excel sheet into Pentaho the columns along with datatype I got which shows string datatype in the date formats column. which you can see below

enter image description here

After this, I tried to integrate with postgres but I am unable to find the result the error which I got attached below

enter image description here

Updated

I tried with the given timestamp format yyyy/MM/dd HH:mm:ss this works fine for me but this format yyyy/MM/dd hh.00.00 is not present in the format column.

enter image description here

Upvotes: 2

Views: 17623

Answers (2)

Nehemie KOFFI
Nehemie KOFFI

Reputation: 1395

Select or put the corresponding format in [Format] column on [Fields] Tab.

Upvotes: 0

AlainD
AlainD

Reputation: 6356

You have a column named Date in the field definition tab. Choose Date [data type] from the Dropdown box in the row date [column name] and timestamp [column name].

Try to get the data in the Excel Input step. If it does not work, try to write yyyy/MM/dd in the Format column for the date field and yyyy/MM/dd hh.00.00 for timestamp field. Note that the format is most probably unnecessary for the Excel Input.

Once you can get all the row in the Excel Input with a preview limit 0, and not before, try to put the data in the Postgres database.

Normally it should work. If not, use a Select Step which has a tab Meta-data, to change among other the format of the dates. Chose a format accepted by Postgres. Again, this change of format is most probably unnecessary.

To explain what happens under the wood, remember each field in the PDI has a type. You define time and timestamp as string. It is not an issue by itself, until you try to put those string in the database, which no not accept such date formats. The best way is to use the date type (which DO NOT have a format until you want to read or write them).

Upvotes: 3

Related Questions