topic Re: String to Double conversion and validation in Talend Studio

String to Double conversion and validation

Anonymous — Sat, 16 Nov 2024 13:09:15 GMT

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.

Re: String to Double conversion and validation

veryimportantdude — Mon, 10 Jan 2011 13:33:43 GMT

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

Re: String to Double conversion and validation

Anonymous — Mon, 10 Jan 2011 15:53:35 GMT

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.

Re: String to Double conversion and validation

Anonymous — Mon, 10 Jan 2011 22:09:31 GMT

hi ,
please avoid https://community.talend.com/t5/Design-and-Development/resolved-String-to-Double-conversion-failing/td-p/78424
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"
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
regards
laurent

Re: String to Double conversion and validation

Anonymous — Thu, 24 Sep 2015 12:16:09 GMT

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