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

PERL

    Next

  • 1. use hash of arrays to sort
    Hi, I'm attempting to use a hash of arrays to sort columns of data with the option of sorting by ascending and descending order. Does this seem like a valid approach? How could I add "$b[0] <=> $a[0]" to do descending sorts as well. Thanks. Here is my code so far. #!/usr/bin/perl use strict; use warnings; my %results_h; my @results_b; while (<DATA>) { next unless s/^(.*?):\s*//; push @results_b, [split]; } foreach (@results_b) { $results_h{$_->[0]} = $_; #sort by this column } foreach (sort keys(%results_h)) { print join(" ",@{$results_h{$_}}),"\n"; } __DATA__ 1:frodo 00002 2:bob 00001 3:eddie 00004 4:fred 00003
  • 2. File::NCopy question
    I'm trying to use File::NCopy::copy to preserve the modification time of a file, but I can't seem to get it to work. I've got, basically, use File::NCopy qw(copy); $fobj=File::NCopy->new( 'force_write' => 1, 'preserve' => 1, ); $fobj->copy( "file1", "file2" ); file2, however, is not keeping file1's modification time. I'm using ActiveState Perl version 5.8.7 on WinXP. Do I need to change 'set_times'? Is there some other WinDoze quirk I need to be aware of to get this to work? -- Christopher Benson-Manica | I *should* know what I'm talking about - if I ataru(at)cyberspace.org | don't, I need to know. Flames welcome.

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 22 guest