Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Advice on best transformation steps

  1. #1
    Join Date
    Jun 2014

    Default Advice on best transformation steps

    Hello everybody,

    I'm beginning with Kettle and have several doubts about the best way of organizing my transformation. I don't want to make anything absurd, so I ask for help.

    This is the transformation I need:

    -Reading rows from an Excel (yes, there is no way we can escape this )
    -Validating certain values from the Excel (mainly strings which contain numbers before the string and I need to get rid of them as well as trimming whitespaces left and right)
    -Calculate new fields for each row, using values already present in the same row and/or database tables
    -Insert/Update these rows in a given table, where the new fields created in the previous step hace already been accounted for (they exist in the table).

    Any advice on which steps I need and how to achieve this will be greatly appreciated. Hints, opinions, anything will be very helpful.

    Thanks in advance!

  2. #2
    Join Date
    Jun 2012


    Obviously, you will use a Microsoft Excel Input step for reading your data rows.
    You can use Replace In String to remove a numeric prefix from a string.
    Whitespace removal is a configuration option of the field entries in the Input step.
    Of course you can trim later, too, using String Operations for example.
    Deriving field values from other fields can be done using Calculator or User Defined Java Expression.
    If you must consult database tables to do that, you'll find Database Lookup helpful.
    Finally, the step to Upsert a DB table is named Insert / Update.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Dec 2011


    On top of what Marabu said:

    - Deriving field values from other fields can also be done using Modified Java Script Value, the language is really similar to C.
    - If you only need to insert in a table I recommend using Table Output.

    Good luck!

  4. #4
    Join Date
    Jun 2014


    Thanks for the tips! I'm going to test them ASAP!

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.