Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Excel input formatting question

  1. #1
    Join Date
    Jan 2006
    Posts
    11

    Default Excel input formatting question

    I have an Excel sheet as input. One of the columns is a 3-digit number that I would like to save as a string. On the Excel step, I choose the type of the field as string with length as 3.


    I am outputing to a text file. I am finding that it is still taken as a number and the output shows a decimal point. For example, 101 as input gets stored as 101.0 in the output file.



    Changing the format on the Excel side also has not worked.



    What do I need to fix this ?

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

    Default RE: Excel input formatting question

    Hi,

    Excel doesn't know about integers, just number (floating point)
    So if you convert it to String in the Excel Input step, you get the .0 appended.
    Better to convert it to Integer using a "Select Values" step and by changing the meta-data for the field.
    That way, the Number field from Excel gets rounded to an integer (losing the .0)

    Even easier, you can specify the Number field in TextFileOutput as well, set the format the "0".

    Hope this helps,

    Matt

  3. #3
    Join Date
    Jan 2006
    Posts
    11

    Default RE: Excel input formatting question

    Hi Matt,

    That did work. Thank you.

  4. #4

    Default

    WhaT If the column consists on number data and text data (don't ask why), then you can't change the metadata to integer. How then do you prevent the numbers from outputting as 101.0

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.