topic Re: Avoid auto convert field "date-hour" in Talend Studio

Avoid auto convert field "date-hour"

Anonymous — Thu, 25 Sep 2014 08:01:15 GMT

Hi everyone,
I come to you today because we run into a strage case using talend open studio.
While I'm creating metadata using an excel file, a field which contains two details (date and hour) is converted automatically.
The date is OK but the hour is (before importing the file) in "french" format (00-23 for the hours) and show itself as the "UK" format (13:06 become 1:06 WITHOUT am/pm details).
So it's a bit frustrating. We never had to take care about a hour field so we never gave attention about that but now... we have to look for it and we notice this point.
To sum up :
I've an excel file with a field which contains YYYY/MM/DD hh:mm:ss (french format, hour between 00 and 23)
I create metadata importing this file
when i look at the result i find the field as YYYY/MM/DD hh:mm:ss (UK format, hour between 00 and 12, WITHOUT am/pm details so we can't use them to convert again)
Does any one have an idea about how we can avoid the auto convert of the hour?
Thank you very much for looking at my topic and please forgive me for my english, long time since i last praticed it...
FA

Re: Avoid auto convert field "date-hour"

Anonymous — Thu, 25 Sep 2014 08:59:54 GMT

Hi,
Can you read it as a string from input excel as expected?
Vaibhav

Re: Avoid auto convert field "date-hour"

Anonymous — Thu, 25 Sep 2014 09:25:02 GMT

Thank you for that quick answer!
at first, the field is a custom field (cf screenshoot)
.
When i import the file, here is the proposed field (in talend) :
Second point :
we tried to convert the field as "date" (in excel) and then re-import, the result is the same.
Tell me if you need more intell (I'm being asked to not show everything but i can show a little more). And I hope I answered correctly to your ask.
FA

Re: Avoid auto convert field "date-hour"

Anonymous — Thu, 25 Sep 2014 09:33:26 GMT

I would add an other screenshoot :
as you can see, the time is allready converted when the filed has been imported (01:15:19 should be 13:15:19, we don't have any row before 8am)

Re: Avoid auto convert field "date-hour"

Anonymous — Thu, 25 Sep 2014 10:58:12 GMT

Hi,
Can you put a marker in the date format data metadata in next screen while creating metadata?

"yyyy/MM/dd hh.mm.ss aa"
and complete the metadata and read the date. If the date is read as correct am/pm, then using java you can convert it to 24 hr format later.
Please check
Vaibhav

Re: Avoid auto convert field "date-hour"

Anonymous — Thu, 25 Sep 2014 11:37:59 GMT

Hi,
It can also because of system date format.
Change the system time format to 24 hours then restart the talend and try to import excel.

Re: Avoid auto convert field "date-hour"

Anonymous — Thu, 25 Sep 2014 12:40:34 GMT

Hi again,
I'm going to try those idea in the afternoon. I come back to you when i've the result.
Thank you very much both of you.

Re: Avoid auto convert field "date-hour"

Anonymous — Thu, 25 Sep 2014 14:16:15 GMT

Hi again,
well... I tried both of your solutions.
@Vaibhav : When I added the "aa" in the end, AM/PM didn't show up
@BhanuChandar : the date format has been changed (for the all team which work on the system) but the trick didn't work
BUT !
I modified manually the excel field, and ... miracle it passed.
I changed it from custom field(DD/MM/YYYY hh:mm:ss) to an other (DD/MM/YYYY hh:mm) so... the only thing that has changed is the "ss" in the end...
What's annoying is that the excel file is sent to us automaticaly... we'll have to ask for an update if we don't succed with an otherway.
In any case, thank you (again) very much for your answers and if any other idea come up for that trouble, I'll take it
Have a nice afternoon.
FA