Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: import from .xls table with people calendar

  1. #1
    Join Date
    Jan 2018
    Posts
    5

    Lightbulb input from .xls table with people calendar

    Is it posible to go in Pentaho spoon through such table, get all of the data and import in some clever form?

    Is it possible to get data in separate fieds? And do the same job for all users in all days? I image it like this - for every user for every single day create 1 entry in our system:
    Field: Value
    Name: user1
    Day: 1.1.2017
    From: 18:00
    To: 06:00
    Service: NO
    Holiday: x

    Thanks for your help.
    s.
    Attached Images Attached Images  
    Last edited by BMC; 01-12-2018 at 11:54 AM.

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

    Default

    That's not a table, it's some kind of report which contains tables.
    It would be clever to access the database behind this report.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Jan 2018
    Posts
    5

    Default

    It’s manually created, no DB sorce.

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

    Default

    That's bad, but you still can process it if there is a limit to the number of blocks in each row.

    Is it a block for each day of a month?

  5. #5
    Join Date
    Jan 2018
    Posts
    5

    Default

    It is a block for each day of a year. Maybe I can discuss it with customer to split one year to 12 months and create 12 sheets if it helps.

    In excell input I can make so many columns as needed, but I don’t know how to process this table later.

    I can imagine, performance will be bad, but import of these data will be executed only few times in a year (after some update).

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

    Default

    What you need for Kettle is tabular data - user,from,to,service,holiday. If you can't have it, you'll have to transform it.

    Wouldn't it be much better for data entry and processing, if you had just a single block?


    USER FROM TO SERVICE HOLIDAY
    user1 2017-01-01 18:00 2017-01-02 06:00 NO Y
    user2 2017-01-01 08:00 2017-01-01 16:30 GL
    user1 2017-01-01 06:00 2017-01-01 18:00 F4
    user2 2017-01-01 08:00 2017-01-01 16:30 GL Y

  7. #7
    Join Date
    Jan 2018
    Posts
    5

    Default

    I work with BMC Atrium Integrator Spoon.

    This is the very first .xls file which serves as a calender with people workdays, type of service and holidays.
    Name:  Initial.jpg
Views: 18
Size:  33.3 KB

    It looks hard to import, maybe impossible, through Spoon so we have created simplified version of it. But requirment is to have this simplified version as easy to manage as possible. It means day after day and date in cell above - copy and paste times cells for repeating occurences.
    Name:  simplified.jpg
Views: 19
Size:  12.2 KB

    Maybe we can change the structure of it. Do you have any suggestion?

    If is it possible, It is sufficient to get all values for 1 row with delimeter. I can parse it in our system a split it to particular entries.

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

    Default

    Somehow you can process both versions using Kettle, I think.
    They are both not geared towards further processing, though.
    The table structure I suggested above would be ideal for storage.
    You can use a report designer to produce a calendar, then.
    Last edited by marabu; 01-12-2018 at 05:44 PM. Reason: typo

  9. #9
    Join Date
    Jan 2018
    Posts
    5

    Default

    primary purpose of this calendar is to manually manage users and their working days. It's done on management layer, so it has to be nice in excel and it has to be somehow imported to our system - only for review purpose.

    Your suggestion calculates with something like this? Or you want to split all days into new rows?

    User From To Service Holiday From To Service Holiday
    user1 1.1.2018 08:00 1.1.2018 16:30 F4
    2.1.2018 08:00 2.1.2018 16:30 F4 x
    user2 1.1.2018 08:00 1.1.2018 16:30 F4 x 2.1.2018 08:00 2.1.2018 16:30 F4

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

    Default

    As a consultant, I would like to do business with your company - endless discussions about simple things, I would become rich.

    My suggestion aims at separation of data and presentation.
    The ideal (normalized) storage structure would be (user,from,to,service,holiday) and not (user,from,to,service,holiday,from,to,service,holiday).
    You can easily produce a colored calendar using a report designer or a special calendar tool.

    After a lengthy discussion you still may end up with an excel calendar as your input.
    In that case you will have to create a field list for Microsoft-Excel-Input.
    You may want to look into ETL-Metadata-Injection for generating the field list instead of manually entering the fields.

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 - 2017 Pentaho Corporation. All Rights Reserved.