Re: Data Export to Excel, Reals or now Strings?
- To: mathgroup at smc.vnet.net
- Subject: [mg78266] Re: Data Export to Excel, Reals or now Strings?
- From: Jean-Marc Gulliet <jeanmarc.gulliet at gmail.com>
- Date: Wed, 27 Jun 2007 05:27:10 -0400 (EDT)
- Organization: The Open University, Milton Keynes, UK
- References: <f5qmhq$47p$1@smc.vnet.net>
kristoph wrote:
> Hi,
>
> I exported simulated data to manipulate it in excel. The problem that
> I have is that sometimes not always a data point is exported to excel
> like the following:
> 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
> 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
> 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
> 00000000000000000000000000000000000000000000000000000000000010923543112535720334421513
> (this is suppose to be one number)
>
> instead of
> 1.09235431125357203344215132`15.954589770191042*^-349
> which is the result calculated by Mathematica 5.2.
>
> I would like to import the data back to Mathematica after the
> manipulations which result in errors during calculations since some of
> the data point, like the one above are now stings and not reals.
>
> Thanks for help
> Kristoph
Hi Kristoph,
If I am not mistaken, the issue is that you are using numbers that are
outside the range of the machine arithmetic (see the exponents +/- 308).
This is not an issue for Mathematica, since Mathematica will switch
automatically to arbitrary precision (see the exponents +646456887).
Now I have no idea what Excel (what version do you use?)is supposed to
do with numbers that are beyond the range of machine arithmetic. It
seems that Excel treats such large, or small, numbers as character strings.
In[1]:=
{$MaxMachineNumber, $MinMachineNumber}
Out[1]=
{1.7976931348623157*^308, 2.2250738585072014*^-308}
In[2]:=
{$MaxNumber, $MinNumber}
Out[2]=
{
1.9202246726923565854204690606`15.954589770191005*^\
646456887, Underflow[]}
In[3]:=
$Version
Out[3]=
"5.2 for Microsoft Windows (June 20, 2005)"
Now, assuming I have correctly understood what you want to do, the
following expression should be a good start to develop your own that
fits exactly your needs. (Note that I have not tested it thoroughly. It
might break down or yield unexpected results. This is just a template.)
In[1]:=
numberexcel = "0000000000000000000000000000000000000000\
0000000000000000000000000000000000000000000000000000000\
0 \
0000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000 \
0000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000 \
0000000000000000000000000000000000000000000000000000000\
0000010923543112535720334421513";
In[2]:=
myConv[stringnumber_String] := Module[{pos, num, dec},
pos = StringPosition[stringnumber,
Characters["123456789"], 1][[1,1]];
num = StringDrop[stringnumber, pos - 1];
dec = StringLength[num];
SetPrecision[ToExpression[num], dec]*10^(-dec + 1)*
10^(-pos + 3)]
In[3]:=
myConv[numberexcel]
Out[3]=
1.0923543112535720334421513`26.*^-349
Regards,
Jean-Marc