[HELP] convert string to bigdecimal

[Anonymous]

good morning. i hava a problem. i have an exel input file with 4 column.
cod | data | day | value
value is set like a general format cell but in my file rapresent a decimal value but used like string.
i have to insert this data in a oracle table in which this column is defined like number(20,6)
my string text is like 0,25487.
i use a t_map where in input i take string text (e.g. 0,25487 not 0.25487 beacuse in italian language option decimal is rapresented by ,) and input t_map is defined like string
in output i have tried a lot of combinations but unseccesfully like for example write in t_map output expression new bigdecimal(inputstring) (column t_map outpit is defined like bigdecimal with lenght =20 and precision =6) but give this error:

Exception in component tMap_1
java.lang.NumberFormatException
at java.math.BigDecimal.<init>(Unknown Source)
at java.math.BigDecimal.<init>(Unknown Source)
at testmdb.insertgas_0_1.insertGas.tFileInputExcel_1Process(insertGas.java:1065)
at testmdb.insertgas_0_1.insertGas.runJobInTOS(insertGas.java:1443)
at testmdb.insertgas_0_1.insertGas.main(insertGas.java:1317)
in which way i can insert my strind data in a bigdecimal type?
thanks in advance

[Anonymous]

new BigDecimal(string) expect a dot as decimal separator
So you can do a new BigDecimal(inputstring.replaceAll(",",".")
Other options : use a tConvertType

[Anonymous]

thanks, works fine but there is another problem. my input string is like 0,25007401 but using new BigDecimal in oracle db is saved not all number but is truncated at first two decimal and so i obtain 0,25 not 0,25007401 and this is wrong. in which way i can insert decimal number to save?

[Anonymous]

Hi,
Using a tJava component at the start of the job, define a decimal formatter with the BigDecimal option set

DecimalFormat fmt = new DecimalFormat();
fmt.setParseBigDecimal(true);
globalMap.put("bdfmt", fmt); // corrected

Also import java.text.DecimalFormat and java.math.BigDecimal in the advanced section
In the tMap, refer to this as

(BigDecimal)((DecimalFormat)globalMap.get("bdfmt")).parse(row1.stringNumberValue); // corrected

If you're having problems with the Italian decimal setting, verify the Locale by printing out "Locale.getDefault()". If it's not Italian, add this line and import to the tJava component

import java.util.Locale;
Locale.setDefault(Locale.ITALIAN);

[Anonymous]

sorry but i have never used t_java and so i have not understood completaly.
i have to link t_java with other components or stay alone?
have you some image to help me?
and where i set decimal number(6 number)?

[Anonymous]

The image at this blog post shows a tJava http://bekwam.blogspot.com/2011/02/shorthand-globalmap-for-talend-open.html
Put the tJava at the start of the job and use a SubJob OK trigger to continue with your processing
To add an import to a tJava, select the component and press "Advanced settings". Add the following lines

import java.text.DecimalFormat;
import java.math.BigDecimal;
// if locale isn't set to ITALIAN
import java.util.Locale;

To create a global variable with the tJava, select "Basic settings" and add the following line.

DecimalFormat fmt = new DecimalFormat();
fmt.setParseBigDecimal(true);
globalMap.put("bdfmt", fmt); // corrected
// if locale isn't set to ITALIAN
Locale.setDefault(Locale.ITALIAN);

You can now refer to the global variable "bdfmt" throughout the job. In the tMap expression -- where the fields are mapped -- use the following which retrieves the DecimalFormat object (using get()) andcalls parse() function on one of the input fields. Substitute "row1.stringNumberValue" for your row and field name.

(BigDecimal) ( (DecimalFormat)globalMap.get("bdfmt") ).parse( row1.stringNumberValue ); // corrected

[Anonymous]

i have tried to make your suggested steps but gives me 2 error:
1 error in t_java
1 error in t_map
in problems-> errors there is written:
method parse(string) is not defined for type Object
method Set(String,Decimal Format) is not define for type Map<String, Object>
and then i have not yet understood where i set my decimal number because i don't find in your code something like set... =6 (decimal number that i want to see)
and in your last code there is no more replace.all, is yet necessary or isn't needed??

[Anonymous]

You won't need replaceAll() if you use the correct locale.
My code example referencing globalMap should use "put" instead of "set". Also, there needs to be a cast on the DecimalFormat object to use the parse() correctly.

(BigDecimal) ( (DecimalFormat)globalMap.get("bdfmt") ).parse( row1.stringNumberValue );

I'll edit the previous posts.

[Anonymous]

sorry now i have seen and with this your suggest it doesn't give me error but output is ever wrong because my output is 0,25 nut i need to 0,250074

[Anonymous]

Where do you see the 0,25? Are you running a query on the database?