"Data import from Excel - date time conversion hell"

d1m0s

i've got dates in Excel that got wrong conversion in RM.

Example: In Excel I have 14.07.2007 23:59 (formatted as dd.mm.yyyy h:mm), when I load it into RM it turns into Sun Jul 15 02:59:59 EEST 2007. WTF? Time zone conversion? I'm lost. Please help.


P.S. I've got 3 date/time variables within one Excel example set...after loading into RM 2 got +3 hours, 1 got +2 hours...again WTF?

I've tried another spreadsheet with the same variables and got different time shifts for the same variables.

JEdward

Hi d1m0s,

I had a similar issue with a file until it was pointed out to me the 14/07/2007 is formatted as dd/MM/yyyy and not dd/mm/yyyy as I was entering. 
Could this be similar to your problem? 

Regards,
JEdward

d1m0s

JEdward wrote:
Hi d1m0s, I had a similar issue with a file until it was pointed out to me the 14/07/2007 is formatted as dd/MM/yyyy and not dd/mm/yyyy as I was entering.  Could this be similar to your problem?  Regards, JEdward

Thanks for your reply JEdward, I tried dd.MM.yyyy h:mm in date format field of Read Excel operator...unfortunately same result...time shift added to my original Excel data...had to use Talend to do the ETL:(

haddock

Hi Folks!

Sometimes you need to consider the milliseconds - yes really! This issue popped up last March, here's a reference to the thread...

http://rapid-i.com/rapidforum/index.php/topic,3489.0.html

Hope that helps.

JEdward

Thanks Haddock, I'll keep that in mind. 

Cheers,
JEdward.