Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Change the column name of Excel Input and output it to SQL Output

  1. #1
    Join Date
    Jul 2012
    Posts
    8

    Default Change the column name of Excel Input and output it to SQL Output

    Hello,

    So I have an excel file with a column in it called 'State Value' I also have a database with a column called 'ext_attr_value'

    What I am looking to do is take the values in 'State Value' and put it under 'ext_attr_value' and output it on an SQL File....which step would I need to do such a thing?

    I am a newbie.

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

    Default

    Use step Microsoft Excel Input to read Excel file.
    Use Select Value step to select only the field that you need to load into your table.
    Use Table Output or Insert/Update to load the data into Sql table.

    For future reference: http://wiki.pentaho.com/display/EAI/...egration+Steps

    Mick

  3. #3
    Join Date
    Nov 2008
    Posts
    777

    Default

    Use the Select Values step to rename a field.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  4. #4
    Join Date
    Jul 2012
    Posts
    8

    Default

    Thanks you two for your suggestions and that works great....maybe I should put more details as in what I am trying to do and I would like your option for what would work best

    - I have an excel file with columns 'State Value', 'Country Value'
    - I have a database (no step, just a database connection) with columns 'id', 'ext_attr_name', 'ext_attr_value', 'selection_type_id', 'selection_value_id', text_type_id', 'created by', 'create_date', 'modified_by', 'modify_date'
    - I want to take the columns from the excel file and insert them all unders 'ext_attr_name' on different lines....I was able to do this with 2 Select value steps coming from the excel file (if there is a better way to do, I would like to hear it.)
    - But I also want to take the other columns from the database and insert them with the 'ext_attr_name' from the excel file.
    - The way I am currently doing it the only columns that get inserted is 'ext_attr_name' I want to manually enter values for the rest of the columns in the database and output into an SQL file.

    Which step am I missing for this?

    Thanks

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

    Default

    What I understand from your description:

    (1) You got input rows
    Code:
    Country | State
    C1      | S1
    C1      | S2
    (2) You want to have output rows
    Code:
    ID | ext_attr_name | ext_attr_value | selection_type_id | selection_value_id | text_type_id | created by | create_date | modified_by | modify_date
    1  | Country       | C1             |
    2  | State         | S1             |
    3  | State         | S2             |
    This doesn't really make sense, because you can't tear apart country and state that way without loosing information, but maybe you only picked a bad example.
    The above described transformation involves normalization and deletion of duplicates.
    Attached Files Attached Files
    So long, and thanks for all the fish.

Tags for this Thread

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.