Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Converting Currency Number from "," to "."

  1. #1
    Join Date
    Dec 2012
    Posts
    13

    Default Converting Currency Number from "," to "."

    Hows it going everyone!

    I am having difficulty converting a number that has decimals into periods.

    For example: "123,44" when in reality it should be "123.44".

    What type data and format do i need to put in the text input file in order to convert this correctly?

    I got it to read 123.00 put i need to include the decimal places at the end of it.

    Using:
    Type: Number
    Format:0.00

    Thanks alot guys!

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

    Default

    I would read the field as STRING, then use "Replace in string" to replace commas with dots.
    Then add a "Slect Value" step and change the format from STRING to NUMERIC.

    Mick

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

    Default

    You can do that, Mick, but you don't have to.

    Text File Input expects numbers in the format specified by your current locale.
    When your locale specifies a decimal point and your data fields come with a decimal comma, you can simply overwrite the meta characters "Decimal" and "Group" of the field descriptor in the Text File Input step.
    So long, and thanks for all the fish.

  4. #4
    Join Date
    Dec 2012
    Posts
    13

    Default

    Marabu,

    I still can't figure out the decimal field and group field that you are talking about to solve this problem .

    I tried every type of combination to get it to read 123.44 but i just can't figure it out.

    Can you please help me with this?

    I'm using these filters:
    Type: Number
    Decimal: "."
    Group: "."

    Im getting 123.00

    Does it have to do with the format field? Precision? length? Position?

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

    Default

    You never said which Kettle version you use.
    Attached Files Attached Files
    So long, and thanks for all the fish.

  6. #6
    Join Date
    Dec 2012
    Posts
    13

    Default

    Wait i didn't explain myself on this one.
    I currently have a csv file that has 123,44 and I need it to be converted to 123.44 but i keep getting 123.00

    Sorry about that Marabu, i am using Kettle - Spoon - Stable Release 4.4.0

    Thank you so much!! You are the best!
    Last edited by simonlelo; 09-06-2013 at 05:33 PM.

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

    Default

    Quote Originally Posted by simonlelo View Post
    I currently have a csv file that has 123,44 and I need it to be converted to 123.44 but i keep getting 123.00
    Just replace the decimal point by a comma, both in my input file and in the field metadata, and it should work.

    BTW: Conversion is only necessary in Text File Input and Output.
    Numbers are happy in their internal binary representation most of the time.
    It's just because you want to look at them some format is applied.

    Quote Originally Posted by simonlelo View Post
    You are the best!
    I'm not - and I know that, but I still try hard to get better - just like you.
    So long, and thanks for all the fish.

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.