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.
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.
hi , please avoid double post 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 :