[HELP] convert string to bigdecimal

One Star

[HELP] convert string to bigdecimal

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
Employee

Re: [HELP] convert string to bigdecimal

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

Re: [HELP] convert string to bigdecimal

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?
One Star

Re: [HELP] convert string to bigdecimal

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);
One Star

Re: [HELP] convert string to bigdecimal

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)?
One Star

Re: [HELP] convert string to bigdecimal

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
One Star

Re: [HELP] convert string to bigdecimal

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??
One Star

Re: [HELP] convert string to bigdecimal

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

Re: [HELP] convert string to bigdecimal

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
One Star

Re: [HELP] convert string to bigdecimal

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

Re: [HELP] convert string to bigdecimal

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
One Star

Re: [HELP] convert string to bigdecimal

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)
One Star

Re: [HELP] convert string to bigdecimal

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?
One Star

Re: [HELP] convert string to bigdecimal

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

Re: [HELP] convert string to bigdecimal

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 Smiley Sad
One Star

Re: [HELP] convert string to bigdecimal

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)
One Star

Re: [HELP] convert string to bigdecimal

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
One Star

Re: [HELP] convert string to bigdecimal

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
Employee

Re: [HELP] convert string to bigdecimal

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