Hitachi Vantara Pentaho Community Forums
Results 1 to 11 of 11

Thread: Problem with microsoft excel input

  1. #1

    Default Problem with microsoft excel input

    Hi,

    I have an excel input and it has four sheets within. In the sheet tab of "Microsoft Excel Input", I am giving names of all sheets and their start row and column. But clicking on "feilds" tab and clicking on "preview rows" its showing data only from the 1st sheet..or the sheet whose name is first in the list..but its showing headers of all the sheets. Any suggestion as how can I get all the values together from each sheet??

    Thanks in advance.

    Poulomi

  2. #2
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    I would create 4 Excel Input steps and use a different sheet for each one of them.
    I think that your method could work only if all 4 sheets have the same fields names and format.
    -- Mick --

  3. #3

    Default

    Quote Originally Posted by Mick_data View Post
    I would create 4 Excel Input steps and use a different sheet for each one of them.
    I think that your method could work only if all 4 sheets have the same fields names and format.
    Thanks for your suggestion. But I dont want to use like this, because I would like to dump all the data from each sheet into a single table output. Using four different excel input step won't allow to use the same table output. So any other way to get this done??
    Thanks in advance..

  4. #4
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    You can create a Hop from the 4 Excel Input steps into a Dummy step, then create a hop from there to a Table Output step.
    You MUST have same fields name, number and format for each Excel Input steps.
    Last edited by Mick_data; 10-31-2013 at 07:49 AM. Reason: added line about format
    -- Mick --

  5. #5

    Default

    Quote Originally Posted by Mick_data View Post
    You can create a Hop from the 4 Excel Input steps into a Dummy step, then create a hop from there to a Table Output step.
    You MUST have same fields name, number and format for each Excel Input steps.
    But my input excel has different fields in each sheet. And I want to dump them together in a table ouput with all different headers. If the "Microsoft Excel Input" step has the option to choose name of sheets and list of sheets in the "Sheet" tab, why is it not taking data from all the sheets???

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

    Default

    To make a long story short: Attach an Excel Workbook with two sheets and a minimum of rows each.
    Better yet, attach a demo transformation.
    So long, and thanks for all the fish.

  7. #7

    Default Attaching sample files

    Hi,

    This is the sample transformation(tra1.ktr). Input file is exampl1.xls. I am getting file.txt as output. But I want file_i_want.txt as output. Hope u will b able to understand now. Thanks in advance.

    Regards,
    Poulomi
    Attached Files Attached Files

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

    Default

    Here's what Mick told you in post #2 - and it works
    Mind the input sort order, add Sort Rows if necessary.
    Attached Files Attached Files
    So long, and thanks for all the fish.

  9. #9

    Default

    Quote Originally Posted by marabu View Post
    Here's what Mick told you in post #2 - and it works
    Mind the input sort order, add Sort Rows if necessary.
    What u told is absolutely perfect. But this "merge join" step can be used only for two sheets. But my input excel file for production has four sheets. So which step should I use for merging??? Thanks in advance.

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

    Default

    Either you cascade multiple Merge Join steps, or you try out Multi Merge Join (experimental in my Kettle version).
    So long, and thanks for all the fish.

  11. #11

    Default

    Quote Originally Posted by marabu View Post
    Either you cascade multiple Merge Join steps, or you try out Multi Merge Join (experimental in my Kettle version).
    Thanks. I am using "Multiway Merge Join" and its working absolutely fine. Thanks a lot.

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.