Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Excel Input - ¿Why the component modify the input itself?

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Join Date
    Oct 2011
    Posts
    10

    Default Excel Input - ¿Why the component modify the input itself?

    Hi, I have an excel worksheet with a structure like that:

    Column A (TV CHANNELS) || Column B (DATA)
    CNN || 0,45
    BBC || 0,23
    CHANNEL 5 || 0,65
    7 STARS || 1,76
    33 || 6,8
    300 || 2,9
    TEL6 || 1,13

    My problem is that I want to store both channel and data in a table, but the excel input catches the channel 33 and 300 as " 33,0" and " 300,0", notice the space before number, and when pentaho inserts it into my table it appears like " 33,0" and " 300,0", and I need it as 33 and 300 in my table.

    I have the Column A field as String with no other option and field Column B as number.

    I tried to use select&rename values and use the metadata option and change it as number but it doesn't work because the field in my table is declared as varchar2 and the other channels cannot be converted as numbers.

    The only way I found is to use Value Mapper component but it is only a patch.

    Some way to solve it?

    Thanks.

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Did you try specifying a mask like #?

  3. #3
    Join Date
    Oct 2011
    Posts
    10

    Default

    I tried, but it didn't work

  4. #4
    Join Date
    Jul 2010
    Posts
    23

    Default

    You try with Format in the Field "#"

  5. #5
    Join Date
    Feb 2009
    Posts
    321

    Default

    did you try specifing a mask in excel input? or select values?

  6. #6
    Join Date
    Oct 2011
    Posts
    10

    Default

    Quote Originally Posted by hernanthiebaut View Post
    did you try specifing a mask in excel input? or select values?
    I tried in the select values and it didn't work, but then I tried in the excel input and worked fine! thanks!!

  7. #7
    Join Date
    Feb 2009
    Posts
    321

    Default

    your welcome

  8. #8
    Join Date
    Nov 2008
    Posts
    777

    Default

    Set the format of the column to "Text" in Excel. You may also have to edit (F2 + Enter) the cells with numbers in them to get Excel to actually change the format. You aren't there yet until Excel puts a little green triangle in the upper left corner of the cell. That informs you that you have a number stored as text.
    Last edited by darrell.nelson; 10-25-2011 at 12:56 PM.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

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.