Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Is it a bug in Microsoft Excel Input step while importing from a Time type column?

  1. #1
    Join Date
    Dec 2011
    Posts
    15

    Default Is it a bug in Microsoft Excel Input step while importing from a Time type column?

    Hi,

    siutation:
    we have an excel spreadsheet filled by some business people and they do not agree to change the datatypes of it etc.

    In this excel there is a column of type Time (the format is HH:Mi:SS) and there are values of how long something executed. For example:
    V1
    11:00:00
    23:59:59
    24:00:00
    23:59:59
    00:20:08
    24:01:01
    25:39:51
    25:39:52


    PDI spoon shows the preview and inserts into file and database:
    V1
    1899/12/30 11:00:00.000
    1899/12/30 23:59:59.000
    1900/01/01 00:20:08.000
    1899/12/30 23:59:59.000
    1899/12/30 00:20:08.000
    1900/01/01 00:21:09.000
    1900/01/01 01:59:59.000
    1900/01/01 01:39:52.000

    As you can see the initial date for the time column is 1899-12-30. For some reason when the Time column is more than 23:59:59 then there is never the date 1899-12-31. We could get away with that, but for some reason not only one day is added, but alsi 20 minutes and 8 seconds. And this 20 minutes and 8 seconds is added to all the times until 25:39:52, after than everything is correct again.

    We can deal with 1899-12-31 not showing up, as this is probably because of excel epoch on this date.

    But the addition of 20 minutes and 8 seconds for the period 24:00:00 - 25:39:51 does not compute at all.

    Does anybody know why is that? How to still get the correct time values?

    Br,
    pxr

  2. #2
    Join Date
    May 2013
    Posts
    1

    Default

    hello,
    we have got the same problem, what's the solution for that?
    anybody?

  3. #3
    Join Date
    May 2013
    Posts
    1

    Default

    We stumbled on the same problem, but couldn't find a good solution for that, it would be nice if there was a solution

  4. #4
    Join Date
    Dec 2011
    Posts
    15

    Default

    pump, no solution yet

  5. #5
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Looks like a bug to me.
    I'd like to make sure, but I can't find the implementation of the getDate() method that's used to convert the cell value to a Date.
    So long, and thanks for all the fish.

  6. #6
    Join Date
    Nov 1999
    Posts
    459

    Default

    FYI: bug report over here: http://jira.pentaho.com/browse/PDI-10116

  7. #7
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    XLS, or XLSX?

    I can't replicate in 4.4.

    As to the 1899-12-30 -> 1900-01-01 thing... Blame Lotus.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.