CODEWITHSUNDEEP
pentahopentaho-spoonpentaho-data-integration
Advait
Advait

Reputation: 74

Import Date column in a particular format

I have an excel file which has a date column with different datatypes. For eg: few values have Date data-type while others have string format.

I tried to import the data and change the column metadata type to string but it changes the date values completely.

I have attached a few screenshots of the data, and I very new to Pentaho, so can anybody help me understand how to tackle this problem.

I tried changing the metadata type or using str2date function in js step but still, no use as data imported is different from the data from the file

enter image description here enter image description here

Upvotes: 1

Views: 635

Answers (2)

Advait
Advait

Reputation: 74

After changing the data type and importing the date values as string. Using SQL to store the date in string format and then formatting it while retrieving solved the issue.

Upvotes: 0

Cyrus
Cyrus

Reputation: 2195

When importing from an Excel sheet with some invalid dates, you can import as string format, then use a Select Values step.

Specify the date field on the Meta-data tab with the correct format (dd/MM/yyyy) and Date format Lenient? set to Y. This should change the 29/02/2017 to 01/03/2017, which is a decent option.

Also, don't use Excel to inspect the results, because it might be screwing up the conversion on re-import. Look at the preview data in Spoon or export to csv and look with a text editor to see if the format is correct first.

Upvotes: 1

Related Questions