One Star

scientific notation being written out

I am having issues with data been shown in scientific notation, I am reading in data from a SQL Server database, the field in question is as follows

Round(lunt.AverageNetRent*ua.Area,0)

the field is defined as a bigDecimal in Talend on the input schema.

Then I use tConvertType to convert to a String before I map it and write out to a CSV. The funny thing is that if there a value it displays correctly only when the calculation is 0 does it switch to scientific notation. Any ideas? Here is the data, 3 rows, first two have the issue in the first column (0E-8), the third row is displays a value of (64562.00000000)


0E-8,2001-08-01,
0E-8,2006-08-01,
64562.00000000,2006-08-01,

Thanks
Derek
5 REPLIES
Seventeen Stars

Re: scientific notation being written out

One reason could be the value is not exactly zero! This happens often while working or gathering values with Double or Float data type.
One Star

Re: scientific notation being written out

jlolling, can you explain when you say the value maybe not be exactly zero, what you mean.
.
So I ran a simple test, where I use the following select statement

select 0.00000000, 0.0000000, 0.000000, 0.00000, 0.0000, 0.000, 0.00, 0

and a schema that matches, ie.
field1 BigDecimal 28 8
field2 BigDecimal 28 7
field3 BigDecimal 28 6
field4 BigDecimal 28 5
field5 BigDecimal 28 4
field6 BigDecimal 28 3
field7 BigDecimal 28 2
field8 BigDecimal 28 0


As you can see from the results below it looks like any field with a scale of more than 6 with 0 results in scientific notation representation when you convert to string.
If you have data it does not use scientific notation? see second result set below.

Can anyone confirm that this is expected behavior for Talend or Java?
And can anyone comeup with away around this?

.------+------+--------+-------+------+------+------+------.
| tLogRow_4 |
|=-----+------+--------+-------+------+------+------+-----=|
|field1|field2|field3 |field4 |field5|field6|field7|field8|
|=-----+------+--------+-------+------+------+------+-----=|
|0E-8 |0E-7 |0.000000|0.00000|0.0000|0.000 |0.00 |0 |
'------+------+--------+-------+------+------+------+------'

Second result set
----------+------+--------+-------+------+------+------+------.
| tLogRow_4 |
|=---------+------+--------+-------+------+------+------+-----=|
|field1 |field2|field3 |field4 |field5|field6|field7|field8|
|=---------+------+--------+-------+------+------+------+-----=|
|0.12345678|0E-7 |0.000000|0.00000|0.0000|0.000 |0.00 |0 |
'----------+------+--------+-------+------+------+------+------'
One Star

Re: scientific notation being written out

ok. I wish I was more proficient in Java, then it won't take so long to find a solution. :-), maybe a 2014 goal.
.
Found a method that I believe I need to use when converting a bid decimal to string. The method that needs to be applied is

BigDecimal toPlainString() method

In general, Java BigDecimal value scale and rounding mode can be set by setScale(scale, roundingMode) during a BigDecimal operation. To display a BigDecimal value, user can use either the toString() or toPlainString() methods. The toString() method may use scientific notation while toPlainString() never will.

Anyone with other ideas, I would love to hear.

Thanks
Derek
One Star

Re: scientific notation being written out

of course I now find a previous post on the forum addressing this issue. Here is a post that can be referenced.

http://www.talendforge.org/forum/viewtopic.php?id=31437
Seventeen Stars

Re: scientific notation being written out

You could also use the Java build in class NumberFormat with its various formatting methods.
http://docs.oracle.com/javase/7/docs/api/java/text/NumberFormat.html