[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]

i have an excel input file in which my values are like this 0,25007401. in my db after insert this data (using t_java ecc and suggested step) if i look my data db is not 0,25007401 but 0,25.
i have done also a test not using oracle database but an output excel file and all data value are truncated

[Anonymous]

Try to break this down. See if you can get my test code to work in a different job. My test job prints out 0.25007401 in an Excel file.
Here's what I'm doing.
1. Create a tJava for the DecimalFormat object
2. Route the SubJob OK trigger to a tRowGenerator
3. Send the Main to a tMap
4. Send the tMap to a tFileOutputExcel
Edit the tRowGenerator to use a single column "newColumn" (press the green plus button). Edit the tFileOutputExcel to use a Built-in schema with a single BigDecimal field
In the tMap
1. Define a variable 'var1' of type String and expression "0.25007401"
2. Create the expression in the mapping for the output field

(BigDecimal)((DecimalFormat)globalMap.get("bdfmt")).parse(Var.var1)

3. Completely ignore the input fields (they only provide the flow)

[Anonymous]

i have tried and my result output is insert in trowgenerator a string like "0.0325698" my outpue excel is 325698
i have tried to insert a string like "0,0325698" and my output is ok -> 0,0325698
in which way can i obtain tha same thing?

[Anonymous]

For the variable, take a look at the "AS A VAR" section in http://bekwam.blogspot.com/2011/01/using-expressions-and-variables-in.html.
For the tRowGenerator
1. Drag a tRowGenerator on to the canvas
2. Select the tRowGenerator
3. In the Component View, press the RowGenerator button
4. Press the green plus button
A column called "newColumn" will be created. Accept all default types and expressions and press Ok. The tRowGenerator is simulating a data flow. The particular data isn't important.

[Anonymous]

sorry i have edit my prev post
if i type "0,0255" works fine but with "0.0025" doesn't work.
my excel input string are like "0,0255" but doesn't work

[Anonymous]

Are you accepting both 0,0255 and 0.0255 in your spreadsheet? If so, then you can apply different Locale-specific formatters to your data. Instead of using a default Locale for all numbers, you'll do something like

NumberFormat fmt = DecimalFormat.getNumberInstance(Locale.ITALIAN);
((DecimalFormat)fmt).setParseBigDecimal(true);
globalMap.put("it_bdfmt", fmt);
NumberFormat fmt2 = DecimalFormat.getNumberInstance(Locale.US);
((DecimalFormat)fmt2).setParseBigDecimal(true);
globalMap.put("us_bdfmt", fmt);

To select between the two formats, you'll need to either use a variable 'COUNTRY_CODE' like this in the tMap

((BigDecimal)((DecimalFormat)globalMap.get( COUNTRY_CODE )).parse(row1.stringNumber)

Or use logic with a tJavaRow (if/else)

[Anonymous]

i can only write comma. Dot is automatic replaced by comma in my excel file.
i have tried to use your last suggest but gives me a lot of error
impossible resolve cast from decimalformat to bigdecimal
impossible resolve COUNTRY_CODE (probably because i have only written country_code in t_map but i have never define it because i don't know where i have to declare it and in which way)
method parseString is not definined for Bigdecimal type

[Anonymous]

See if this blog post helps. It's using a Double rather than a BigDecimal to simplify the Java.
http://bekwam.blogspot.com/2011/05/different-locales-us-europe-in-talend.html

[Anonymous]

Hello walkerca,
Once again, thank you for posting your great new blog post!
Best,
Pcoffre.