Spreadsheet shows scientific notation occasionally with some cells, but it is wrong

Similar Threads:

1.Spreadsheet shows scientific notation occasionally with some cells, but it is wrong

2.Spreadsheet shows scientific notation occasionally with some cells, but it is wrong

I am not sure if this is related to the perl script(uses WriteExcel
module), or related to my MS office excel settings.

e.g. Here are six cells showing in a spreadsheet:

10b1e4	       146914
10b818	       14db80
8.054E+33	808c940

The "8.054E+33" is actually from a string "8054e30" representing a
memory address, which is definitely not 8.054E+33.
But all other cells are show correct - that is weird.

My perl script uses WriteExcel, and the cells are with format which
only set_align('right').

Anyone got ideas?

Thank you guys for your attention.

3.Newbie quesion: Scientific notation

Anno Siegel wrote:
> < XXXX@XXXXX.COM > wrote in comp.lang.perl.misc:
> > Hi,
> >
> > I'm trying to upload some data into a MySQL database.
> >
> > Could someone please advise me how I handle scientific notation?
> >
> > What I have sofar:
> >
> > -------
> > The data (pt, x)
> >
> > P1   4.50015068000000D-004
> > -------
> >
> > My regex expressions, I've been experimenting with:
> >
> > regex1: P\d+\s+(-?([0-9]+(\.[0-9]*)?|\.[0-9]+))/)
>           ^                                      ^^
> Are these supposed to be regex delimiters?
>
> > does not catch the scientific notation
>
> Of course not.  What makes you think it would?
>
> > regex2: P\d+\s+([+-]?(\d+(\.\d*)?|\.\d+)([eEdD][+-]?\d+))/)
> > does catch the scientific notation.  I then say,
> > $number = $1;
>
> For matching numbers with regular expressions, see perldoc -q "is a
number".
> Follow the pointers you find there.
Ah, this helps alot thanks!  I just didn't find that before.
>
> > -------
> > # Insert data
> > $dbh->do("INSERT INTO data(x) VALUES (?)",undef, $number);
> > -------
> >
> > How do I upload the number to the database?  If I do it as above it
is
> > incorrect.
>
> Incorrect how?  Is the data rejected by the database?  Is it accepted
> but not the values you expect it to be?  Be specific.
Well the number it uploads is not the float, in this case double, that
I want it to be.  I'll need to do a strtod.
> 
> Anno
Martin.

4.print flips to scientific notation when integer exceeds 15 decimal digits

Philip Potter wrote:

> I would guess that these numbers are being stored in floats, and that
> these floats are 64-bit double precision, with 53 bits of mantissa.
> That means that there are just under 16 decimal digits of precision in
> these numbers. print and friends seem to automatically print no more
> than 15 digits of precision:
> [...]
> You probably want to use bignums instead. If you are dealing with
> large integers, where precision down to the units place is important,
> you definitely want bignums and not floats.
> 
> H:\>perl -e "use bignum; my $i = 999999999999997; print $i, ' ',$i+4,qq[\n];"
> 999999999999997 1000000000000001
> 
> the bignum pragma makes $i into a Math::BigInt object rather than a
> floating-point value.
> 
> Your solution (a non-obvious printf format) is a bad one because while
> it solves the problem of output, it doesn't solve the problem of
> representation. As soon as you try to store an integer bigger than
> 2^53 (approximately 9e15) you will lose significance:
> [...]
> In answer to your question, the behaviour of print is probably the
> correct behaviour, since there's no point printing more precision than
> you store. So it's a self bug.

Many thanks for the reply.

Following the initial surprise, my main concern was that attempts to 
unearth a description or explanation (i.e. documentation) for the 
observed behaviour was so tricky.  For instance, there was nothing 
obvious in the relevant parts of "Programming Perl".

So I'm happy to categorise it as "self bug", although I'd like to 
include an element of "documentation weakness" in there and I'd be happy 
to assist someone in trying to improve the latter.

Anyway, your explanation was useful and gives us sufficient to decide 
how to address our local use of these numbers.  (In our case, they are 
human-oriented accumulated byte-counts, for which we don't actually need 
that significance/precision.)

Thanks.

-- 
: David Lee
: ECMWF (Data Handling System)
: Shinfield Park
: Reading  RG2 9AX
: Berkshire
:
: tel:    +44-118-9499 362
: email:   XXXX@XXXXX.COM 

5.print flips to scientific notation when integer exceeds 15 decimal digits

Although I've used perl for many years, I've just been surprised (in the 
unpleasant sense) by a recent event.  Given a variable, say "$int", 
which is a growing integer, I would expect "print $int" to print it as a 
simple integer; indeed it usually does so.  But when its size takes it 
from 15 decimal digits to 16 decimal digits, that "print" flips the 
output into scientific notation:
    999999999999997
    999999999999998
    999999999999999
    1e+15
    1e+15

Ouch.  The surprise is especially nasty when this output data is then 
used as input data for another program that expects an integer.

This is consistent across a variety of OSes (although all perl 5.8.8).

I eventually managed to track down a way to achieve the desired result 
with a non-obvious "printf" format.  (I leave that, and its clear 
user-oriented explanation, as an exercise for the reader!)

I'm not aware of any documentation about this surprise. Is this a 
program bug or a documentation bug?  (Or a self bug?)

Is there a more appropriate forum than "beginners@..." to raise this?

-- 
: David Lee
: ECMWF (Data Handling System)
: Shinfield Park
: Reading  RG2 9AX
: Berkshire
:
: tel:    +44-118-9499 362
: email:   XXXX@XXXXX.COM 

6. Convert Scientific Notation to decimal equivalent

7. Newbie quesion: Scientific notation

8. Scientific Notation