topic Re: How to Convert String to Decimal format in Talend Studio

How to Convert String to Decimal format

SMahadevan1608028474 — Tue, 19 Jan 2021 16:17:58 GMT

My source is csv file and I have one column say (Eg: Product Price) It has 1456.45667 as value in csv file. While reading the file in Talend this column treat as String. But I want to check the format as decimal.

I am expecting the output as 1456.45 (Not even round value). expecting it should accept only two digits after decimal. Even I have tried to change the Datatype and it shows the error as Cannot Convert String to Decimal.

Re: How to Convert String to Decimal format

CLi1594691515 — Wed, 20 Jan 2021 02:09:29 GMT

Hi,

If you are using tMap, input String and output is float, you need to perform parsing dataType to do so.

In your case, you may use Double.parseDouble(String) whichever data type you need to convert to.

If you want to read it as float/double, you can set it in schema in input component.

Re: How to Convert String to Decimal format

SMahadevan1608028474 — Wed, 20 Jan 2021 16:51:42 GMT

Thanks for your reply. But Still am facing this issue.

tMap

*******

<Product Price> - 14.5674 (It is reading as String) -Based on my source data

Output table

****************

I am using this below syntax in output table expression Builder,

Double.parseDouble(Product Price) - I have changed this column datatype as "Double" and I have set precision as 2. It is not throwing the error after run job. But the value appear in table as 14.5674 instead of 14.56.

Re: How to Convert String to Decimal format

CLi1594691515 — Thu, 21 Jan 2021 02:41:44 GMT

I got your point.

We can make some work around.

In tMap.

Create a Var in the mid section (int) (Double.parseDouble(row8.product_price)*100 ) which result should be 1456.

In the output, Double.parseDouble(String.valueOf(Var.var1))/100f which result would be 14.56

The concept is whenever the decimal place you need to cutoff instead of rounding, you just need to multiply by 100 (depends on how many decimal place you wants) and convert to Integer, and divide by the number multiply by before to return them to your desired value.

Re: How to Convert String to Decimal format

SMahadevan1608028474 — Thu, 21 Jan 2021 16:31:42 GMT

Hi,

Thanks for your response, I have tried the way you explained but still am not getting the expected output.

Source

**********

Product Price - 14.6783 (String Datatype)

I have created the Var as below,

Double.parseDouble(row4.Transaction_LINE_Quantity)*100

(Choose the datatype as Int)

Output table

**************

Double.parseDouble(String.valueOf(Var.var1))/100

(Set datatype as Double)

After run the job the below error am getting

Type Mismatch: "Cannot convert from double to int"

Re: How to Convert String to Decimal format

CLi1594691515 — Thu, 21 Jan 2021 16:43:11 GMT

Hi,

here is the example.

tMap

Result

Re: How to Convert String to Decimal format

SMahadevan1608028474 — Thu, 21 Jan 2021 17:32:00 GMT

THANKS IT'S WORKING FINE AS EXPECTED. I have listed out the below.

Product Price - 14.6783 (String Datatype)

I have created the var as below,

(int)(Double.parseDouble(row4.Product price)*100) (Set datatype as Int)

Output table

**************

Double.parseDouble(String.valueOf(Var.var1))/100 (Set datatype as Double and precision 2)

I am getting the expected output as 14.67

Re: How to Convert String to Decimal format

SMahadevan1608028474 — Thu, 21 Jan 2021 17:32:34 GMT

But the same scenario I want another field accept only 4 decmial place and I don't want to round up the values.

Product Price - 0.91275 (String Datatype)

I have created the var as below,

(int)(Double.parseDouble(row4.Product price)*100) (Set datatype as Int)

Output table

**************

Double.parseDouble(String.valueOf(Var.var1))/100 (Set datatype as Double and precision 4)

I am expecting the value in csv file as (0.9127) instead of 0.91. It is getting roundup the value.

Re: How to Convert String to Decimal format

CLi1594691515 — Thu, 21 Jan 2021 17:37:00 GMT

for example:

2 decimal place 100

4 decimal place 10000

the control of dp of the value itself is not depends on the precision setting

(int)(Double.parseDouble(row4.Product price)*10000)

Double.parseDouble(String.valueOf(Var.var1))/10000

Re: How to Convert String to Decimal format

SMahadevan1608028474 — Thu, 21 Jan 2021 18:35:28 GMT

Thanks for your help.

The 4 decimal places it's getting displayed (0.91275) - 0.9127

for 2 decimal places it's getting appear as (30.7474) - 30.74

Re: How to Convert String to Decimal format

SMahadevan1608028474 — Thu, 28 Jan 2021 17:16:33 GMT

Source

-------

Product price

This is column is not mandatory and it have null values also in source data and it's reading the data as String.

I have tried the below ways, but still am facing the Empty String issue.

(row4.Unit_Selling_Price.equals(null)?null:(int)(Double.parseDouble(row4.Unit_Selling_Price)*10000))

(row4.Unit_Selling_Price!=(null)?(int)(Double.parseDouble(row4.Unit_Selling_Price)*10000):null)

(row4.Unit_Selling_Price.equals(null)||row4.Unit_Selling_Price!=(null)?(int)(Double.parseDouble(row4.Unit_Selling_Price)*10000):null)

Re: How to Convert String to Decimal format

CLi1594691515 — Thu, 28 Jan 2021 17:27:41 GMT

".equals()" doesn't apply to null value. you can use row4.Unit_Selling_Price == null instead

Re: How to Convert String to Decimal format

SMahadevan1608028474 — Fri, 29 Jan 2021 09:56:42 GMT

I tried with this statement,

((row4.Unit_Selling_Price==null)?null:(int)(Double.parseDouble(row4.Unit_Selling_Price)*10000))

But the exception is occur,

Exception in component tMap_1 (Datavalidation_Filter_V1_3_tconvertype)

java.lang.NumberFormatException: empty String

at sun.misc.FloatingDecimal.readJavaFormatString(Unknown Source)

at sun.misc.FloatingDecimal.parseDouble(Unknown Source)

at java.lang.Double.parseDouble(Unknown Source)

Re: How to Convert String to Decimal format

Prakhar1 — Fri, 29 Jan 2021 11:45:04 GMT

--For Empty use below condition , for NULL use above

and for both use both the condition as "OR"

(((row4.Unit_Selling_Price)isEmpty())?null:(int)(Double.parseDouble(row4.Unit_Selling_Price)*10000))

Re: How to Convert String to Decimal format

SMahadevan1608028474 — Fri, 29 Jan 2021 11:59:39 GMT

my source csv file has blank value (i.e. Empty), So I have tried the below statements,

(((row4.Unit_Selling_Price.isEmpty())?null:(int)(Double.parseDouble(row4.Unit_Selling_Price)*10000)))

am getting the below error,

Exception in component tMap_1 (Datavalidation_Filter_V1_3_tconvertype)

java.lang.NullPointerException

at datavalidation_jan11.datavalidation_filter_v1_3_tconvertype_0_1.Datavalidation_Filter_V1_3_tconvertype.tFileInputDelimited_1Process(Datavalidation_Filter_V1_3_tconvertype.java:52769)

Even I have tried with both ,

(((row4.Unit_Selling_Price.isEmpty()||row4.Unit_Selling_Price==null)?null:(int)(Double.parseDouble(row4.Unit_Selling_Price)*10000)))

But still facing the same issues.

Re: How to Convert String to Decimal format

Prakhar1 — Fri, 29 Jan 2021 12:06:34 GMT

Why you are making the true value as "NULL" ?

Can you try to make it like 0 or something else

(((row4.Unit_Selling_Price.isEmpty()||row4.Unit_Selling_Price==null)?0:(int)(Double.parseDouble(row4.Unit_Selling_Price)*10000)))

Re: How to Convert String to Decimal format

SMahadevan1608028474 — Fri, 29 Jan 2021 13:30:32 GMT

I have tried with this statement and it's working fine. Thanks for your response on this Empty String.

I have one more clarification,

I have the source column as Product_Amount as Float Datatype (Eg: 23.6161)

I am expecting this should load only the 2 decimal values (Eg: 23.61).

In Var

******

(int)(Float.parseFloat(row4.Product_Amount)*100) - Datatype is Int

Here I need to cut off the amount and need to multiply by 100 to convert to integer.

In output table

****************

Float.parseFloat(String.valueOf(Var.var9))/100 - Type is Float

While running the program I got the error as attached.

Re: How to Convert String to Decimal format

Prakhar1 — Fri, 29 Jan 2021 13:46:00 GMT

It means you are passing a Float value in the parseFloat() function.

you should pass a string value in it or other data type value

(int)(Float.parseFloat(row4.Product_Amount)*100)

In the above statement as you mentioned row4.Product_Amount is of type Float, so it is wrong statement.

Re: How to Convert String to Decimal format

SMahadevan1608028474 — Fri, 29 Jan 2021 16:15:06 GMT

Source:

**********

row4.Product_Amount -String Data type.

I have changed the datatype in source from Float to String.

In Var

*******

(int)(Float.parseFloat(row4.Transaction_LINE_Amount)*100))) - Datatype int

Output table

**************

Float.parseFloat(String.valueOf(Var.var10))/100

still the same issue occurs... Can you please let me know where am doing the mistake...