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

PERL

    Next

  • 1. Perl on Windows question
    I have ActiveState perl installed on my PC and have discovered an odd behavior when executing a "type" command, e.g., system "type c:\\somefile"; This comes back with the error c:\somefile not found However, when I use this form system "cmd /c type c:\\somefile"; it works as expected. I've only seen problem happen with the type command. Other built in commands appear to work properly. Other users in the comany do not see this error either, even with the type command. What would be causing this in my environment?
  • 2. Using fork() on XP to run processes in parallel
    On XP: I'd like to fork off multiple processes which later return data (let's a single hash reference) to the parent and terminate. The reason I'd like to do it this way rather than sequentially is that each process takes some time to complete and I'd like to save time by running them in parallel. I've looked closely at perlipc but really can't make heads or tails of how to apply it to what I'm doing. Where else to get started?
  • 3. [OT] PerlTidy option to close parens on function params *only*
    Since I don't consider this question strictly Perl related, I've marked as [OT]. Yet at the same time, I'm not sure where else to ask it since no one other than Perl users would conceiveably use PerlTidy. That said, I want to provide an option or options to PerlTidy that format parenthesis in this fashion: for (1..10) { do_something( $_, "Dude" ); } -or- if ($a < 10) { do_something( $a, "Dude" ); } In other words, parens after keywords like "while," "for," and "if" etc. stay tight (eg. no whitespace), whilst parameters passed to subroutines are formated with one space as with "do_somthing()" above. The PerlTidy man page provides no options that I can see other than tight parens EVERYWHERE. Also the PerlTidy home page offers nothing revealing either and there is no list associated with this product that I can see. I was hoping I might trip across someone here that is experienced enough with PerlTidy to offer a solution to the above. Right now, I'm living with tight parens globally. But I'd rather have white space around my parameters to subroutines. Thanks! -ceo

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

Postby mike yue » Thu, 05 Aug 2010 08:47:58 GMT

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.

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

Postby mike yue » Fri, 06 Aug 2010 02:26:15 GMT

I didn't use either write or write_string, I used write_col() instead.
    $worksheet->write_col( 14, 1, \@start_addrs, $RIGHT_ALIGN );
So the data comes from an array.
Do I need to set the format RIGHT_ALIGN to a particular string or
something? how?

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

Postby Jim Gibson » Fri, 06 Aug 2010 03:07:17 GMT

In article
< XXXX@XXXXX.COM >,




$RIGHT_ALIGN should be a valid Format object reference as returned by
the add_format() method called on a workbook object:

  my $RIGHT_ALIGN = $workbook->add_format();

The format should not change the interpretation of the object type as
determined by the write_col method.

Try using write_string() to store the data instead of write_col(). You
will have to store one cell at a time in a loop.

-- 
Jim Gibson

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

Postby mike yue » Fri, 06 Aug 2010 05:00:45 GMT



> > $workshee>->write_col( 14, 1, \@start_addrs, $RIGHT_ALIGN );>
> > So the data comes from an array.>
> > Do I need to set the format RIGHT_ALIGN to a particular string or>
> > something? how?>
>>
> $RIGHT_ALIGN should be a valid Format object reference as returned by>
> the add_format() method called on a workbook object:>
>>
> my $RIGHT_ALIGN = $workbo>k->add_format()>
> The format should not change the interpretation of the object type a>
> determined by the write_col method>
> Try using write_string() to store the data instead of write_col(). Yo>
> will have to store one cell at a time in a loop>
> ->
> Jim Gibson

I've done that but don't feel good with it - Still wonder if there is
a better solution.
Thanks Jim.

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

Postby mike yue » Fri, 06 Aug 2010 05:01:54 GMT

Also Thanks to Sherm and Peter!

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.Convert Scientific Notation to decimal equivalent

How can I detect this, I have been running some code for a few days to develop some files and ran into the situation where I am getting the following data for input:

14.95313 14.45312 0
14.95313 1.570813E-015 0
14.95313 -14.45313 0
-14.95313 -28.90625 0
-14.95313 -14.45313 0
-14.95313 1.570813E-015 0
-14.95313 14.45312 0
14.95313 -28.90625 0
0 -28.90625 0
-14.95313 28.90625 0
0 28.90625 0
14.95313 28.90625 0

And my code is skipping some lines as it checks for any erroneous data:
next if grep (/[^0-9.-]/, @data);
But that thinks the scientific notation is bad. I searched the net and didn't find anything. How can I match this specific pattern and convert it?

Thanks!
jlc



5.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 

6. Newbie quesion: Scientific notation

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

8. reguarding scientific notation



Return to PERL

 

Who is online

Users browsing this forum: No registered users and 10 guest