One Star

Avoid auto convert field "date-hour"

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... Smiley Happy
FA
7 REPLIES
Four Stars

Re: Avoid auto convert field "date-hour"

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

Re: Avoid auto convert field "date-hour"

Thank you for that quick answer! Smiley Happy
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
One Star

Re: Avoid auto convert field "date-hour"

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)
Four Stars

Re: Avoid auto convert field "date-hour"

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
One Star

Re: Avoid auto convert field "date-hour"

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.
One Star

Re: Avoid auto convert field "date-hour"

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.
One Star

Re: Avoid auto convert field "date-hour"

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 Smiley Sad
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 Smiley Happy
Have a nice afternoon.
FA