"Data import from Excel - date time conversion hell"

d1m0sd1m0s Member Posts: 17 Maven
edited May 2019 in Help
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.
Tagged:

Answers

  • JEdwardJEdward RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 578 Unicorn
    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
  • d1m0sd1m0s Member Posts: 17 Maven
    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:(
  • haddockhaddock Member Posts: 849 Maven
    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.
  • JEdwardJEdward RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 578 Unicorn
    Thanks Haddock, I'll keep that in mind. 

    Cheers,
    JEdward.
Sign In or Register to comment.