Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: How-To Excel to Excel Transformation

  1. #1
    Join Date
    Jun 2009
    Posts
    28

    Default How-To Excel to Excel Transformation

    Hi all,

    i'm very new at ETL (in general) but it would be really great if i could get your help regarding strategy on this.

    i have a spreadsheet and can read it in as Excel Input with no problem.

    i want to map these fields to a different target spreadsheet.

    So i have an Excel Input and Excel Output and a hop between them.

    the problem is that i am not seeing the Target's spreadsheets fields and so i cannot do the mapping.

    i looked at some of the samples but none of them really apply. unless someone out there would like me to look at something more specific.

    Pentaho 3.2 Stable on WinXPPro, SP3.

    Please let me know, Aashish

  2. #2
    Join Date
    Sep 2007
    Posts
    834

    Default

    - Double-click the Excel output step
    - Select the "Fields" tab
    - Click on "Get fields"
    - remove the fields you don't want in the output file, reorder the fields, apply some formats, etc.
    and you're done.

  3. #3
    Join Date
    Jun 2009
    Posts
    28

    Default

    Hi Maria,
    When i click on the Get fields on the fields tab of Excel Output, i don't get the column headings from the second spreadsheet. i get the columns from the first spreadsheet.

    Excel Input one has 55 columns in it. Excel Output has over 148. Both have header columns. Let's say that there are no data cleansing. it's text to text transfer. i am trying to map columns from one spreadsheet into another spreadsheet. How do i do this?

  4. #4
    Join Date
    Apr 2010
    Posts
    127

    Default

    It is my understanding that Excel output creates a new excel file containing the selected fields/formats/etc.
    You cannot get 148 fields out of 55, and you'll have the original headers -- that you may rename afterwards of course.
    This said, there should be a transformation(?) step to change field names; as for adding new fields, I'm not sure.

  5. #5
    Join Date
    Sep 2007
    Posts
    834

    Default

    Quote Originally Posted by rathodsa View Post
    i am trying to map columns from one spreadsheet into another spreadsheet. How do i do this?
    Well, you cannot do this. You have to know the map in advance. And you'll need a Select values step to select / reorder / rename the fields before sending them to the output step.
    As to the new fields, you'll have to add them to your stream before reaching the Excel output step. If the fields are just constant values, add them with an Add constant step,

  6. #6
    Join Date
    Jun 2009
    Posts
    28

    Default Eureka

    Hi Maria,

    i think i got it...

    Excel Input of original spreadsheet
    Access Output - i imported the spreadsheet into Access as a table.
    Create hop between two
    Double-click on Access Output to specify database file and table
    Right click on Access Output to generate a mapping between the two.

    At least now i have source and target!

    From there, i expect i can export the data as a spreadsheet.

    Please let me know if you would have done it differently.

  7. #7
    Join Date
    Sep 2007
    Posts
    834

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.