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