One Star

String to Double conversion and validation

I am having trouble determining the correct functions and syntax for tMap Expressions to convert flat file String data types to Decimal data types.
I am reading delimited files with Strings values. These are being inserted into a database with columns defined as Decimal (12,2).
I need to develop a common tMap expression that I can use to cleanse the data, and convert the Strings into Decimals.
This is a JAVA project.

Examples of INPUT data:
"" (empty string)
null
"$12"
"$12.34"
" $12.34 "
"12"
"12.34"
" 12.34 "
"1,234.56"
"$1,234.56"
These need to cleansed and converted to Decimal(12,2) values.
Please assist me with a tMap Expression that will take any of the above examples and properly convert it to a Decimal value.
4 REPLIES
One Star

Re: String to Double conversion and validation

try with

Double.parseDouble(name_of_your_input_field)
or
new BigDecimal(name_of_your_input_field).doubleValue()

Add round if it needs to be rounded

It worked for me
One Star

Re: String to Double conversion and validation

I'm getting java.lang.Error: Unresolved compilation problems when trying this Expression.
Source column: row.Net_Sales, type String
Target column: Net_Sales, type Decimal(10,2)
The tMap expression is:
Double.parseDouble(row.Net_Sales)
I've also tried
new BigDecimal(row.Net_Sales).doubleValue()
and that errors with: java.lang.Error: Unresolved compilation problems:

Also, Please note the list of possible SOURCE data value examples. I need an Expression that will handle the full list, including NULL, EMPTY STRING, and others listed in my earlier post.
Seventeen Stars

Re: String to Double conversion and validation

hi ,
please avoid double post Smiley Wink
First, before convert your value, you have to clean your date.
In a metadata delimited file create schema and escape double quote .
In regex replace all non numeric value.
Like you have to only keep numeric delimiter caracter (only decimal one) , your regex coulb be something like :

now your have white space into your code , so trim them
Now you can convert result into Double ...
but you'll got an error due to empty string , so you have to filter those "pollution" Smiley Happy
hopefully you can update your metadata and check "ignore empty string" !
you'll may be have also to parse null value (real null value and not field with a string null).
so into your tMap parse string with :
Double.parseDouble(row1.escapedQuote.replaceAll("", "").trim())

and filter in dataflow with filter :
 !row1.escapedQuote.equals("null")

conclusion : split your problem into atomics ones to resolve them Smiley Happy
regards
laurent
One Star

Re: String to Double conversion and validation

Hi! all
how to split the column numeric value and string value in talend.
i have an input example wat652son. but i have output is name=watson and number=652 anyone help me