String to Double conversion and validation

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.
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

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog