topic Re: I need convert date in same format in Talend Studio

I need convert date in same format

RAJ6 — Mon, 23 Oct 2017 13:37:08 GMT

Hi Talend Folks,

I have a input excel source like below screenshot format

I need convert all date in same format using talend tool
I need convert the date column like below screenshot format

Could you plz tell me how to convert date in same format

Re: I need convert date in same format

Anonymous — Mon, 23 Oct 2017 14:44:00 GMT

This could be a complex problem. Do you know of all of the different types of date format being supplied? Can they be identified easily? For example, if you have the date 01/02/2017 is it 1st Feb 2017 or 2nd Jan 2017? Can you guarantee that a date received like that will be Feb or Jan? If you cannot, then this is not going to be easy. If you can, then you need a list of all of the date formats expected, work out how to identify which is which and write a routine to convert it for you using those rules.

You can use built in Talend routines to carry out the conversion to a Date type. For example, 01/02/2017 (assuming this is Feb) can be translated to a Date using the following code....

routines.TalendDate.parseDate("dd/MM/yyyy", "01/02/2017")

Given your example of dates received and assuming a particular date component order (dd/MM/yyyy rather than MM/dd/yyyy) you could identify the type the of date by the presence of a "/" or "-" in the String date.

Re: I need convert date in same format

Anonymous — Mon, 23 Oct 2017 17:56:05 GMT

Hi there,

What format (in Excel cell formatting terms) is the data in the "date" column?

Are these values raw strings, maybe imported from a CSV file, or are they dates with different cell formats?

Is the data being manually entered directly into Excel by users, or exported automatically from a system of some sort?

Are you merging several different sources of data into this one sheet, or is being it generated exactly like this as part of an export?

If it's an export, what options, if any, are you able to customise?

In cases like this, it would be useful if you could attach a small sample file to your posts, rather than just screen grabs of such a file, so we can see things like formatting, and also to provide a file for testing any example jobs which we may create to illustrate a solution. If I were to create a test file, there's no guarantee that the data would match what you have.

Regards,

Chris

Re: I need convert date in same format

RAJ6 — Tue, 24 Oct 2017 07:01:55 GMT

Thanks for your reply
yes, The file exported automatically from a system from multiple sources data merged into single excel file
Date column data type is string format.

Re: I need convert date in same format

Anonymous — Tue, 24 Oct 2017 11:35:26 GMT

In which case, as @rhall suggests, you can use Talend's date handling functions to convert the string into a date, and then just specify the date format in your output schema.

As also highlighted, you must be able to guarantee that there are only a limited number of date formats in this column, and that there are none which cannot be uniquely identified.

If your dates could be in "dd-mm-yyyy" OR "mm-dd-yyyy" format, then there is literally no way to determine from just the data in this field what the date actually is, and you'd need to make changes on the system(s) which are producing this file.

If the only two formats are "M-d-yyyy" and "dd/MM/yyyy", then you could use a ternary expression in a tMap component:

I've simulated your date stings, with a tFixedFlowInput:

And a tMap component does the work of identifying the format of the string, and converting it to a date:

(StringHandling.INDEX(row1.date_string,"/") > 0)
? TalendDate.parseDate("dd/MM/yyyy", row1.date_string)
: TalendDate.parseDate("M-d-yyyy", row1.date_string)

Note, the field in the input schema is a string, and the one in the output schema is a date, which has the required date format specified, and we get the following output: