pyExcelerator big integer values

G

Gacha

I use pyExcelerator to import some data from xml file. One column
contains integer values like:
4750456000708
4750456000715
4750456000333
....
But when I do import the pyExcelerator converts them to something like
this:
4.7504560002e+12
4.7504560007e+12
4.7504560007e+12
4.7504560003e+12

How I understand it's because the integer value is too big. If the type
of the items was string, then all would be fine, but I can't control
the file content.

The question is, how can I import the integers in normal format.
 
J

John Machin

> I use pyExcelerator to import some data from xml file. One column
> contains integer values like:
> 4750456000708
> 4750456000715
> 4750456000333

I think you must mean "xls", not "xml".

Those are integers only in the mathematical sense. The value of each
number cell in an XLS file is treated as a 64-bit floating point number.
How do you know that they are whole numbers? What you see on-screen with
Excel etc is not necessarily what you've got. If you format your column
with 0 decimal places, 4750456000708.123 will show as 4750456000708

However, whether they are whole numbers or not, you still have a problem
> ...
> But when I do import the pyExcelerator converts them to something like
> this:
> 4.7504560002e+12
> 4.7504560007e+12
> 4.7504560007e+12
> 4.7504560003e+12

No it doesn't. It converts the XLS file data to Python float type --
64-bit floating point numbers. There is no loss of precision.

What you are seeing are different visual presentations of the *same*
object. Perhaps this will explain:

| >>> for x in (4750456000708.0, 4750456000708.123):
| ... print x, str(x), repr(x)
| ...
| 4.75045600071e+012 4.75045600071e+012 4750456000708.0
| 4.75045600071e+012 4.75045600071e+012 4750456000708.123
| >>>
>
> How I understand it's because the integer value is too big.

A 12-digit integer is too big for what?
> If the type
> of the items was string, then all would be fine, but I can't control
> the file content.
>
> The question is, how can I import the integers in normal format.

The answer is, there is no such thing as "normal format". Normality,
like beauty, is in the eye of the beholder. You have a value, how you
format it for display depends on your purpose. If what you want is to
see the most precise representation of what you've got, then use repr().

HTH,
John
 
G

Gacha

Thank you, the repr() function helped me a lot.

v = unicode(values[(row_idx, col_idx)])
if v.endswith('e+12'):
v = repr(values[(row_idx, col_idx)])
 
J

John Machin

Gacha said:
Thank you, the repr() function helped me a lot.

v = unicode(values[(row_idx, col_idx)])
if v.endswith('e+12'):
v = repr(values[(row_idx, col_idx)])

That endswith() looks rather suspicious ... what if it's +11 or +13,
and shouldn't it have a zero in it, like "+012" ??

Here's a possible replacement -- I say possible because you have been
rather coy about what you are actually trying to do.

value = values[(row_idx, col_idx)])
if isinstance(value, float):
v = repr(value)
else:
v = unicode(value)

HTH
John
 
G

Gacha

John said:
Here's a possible replacement -- I say possible because you have been
rather coy about what you are actually trying to do.

value = values[(row_idx, col_idx)])
if isinstance(value, float):
v = repr(value)
else:
v = unicode(value)

HTH
John

My final result:

value = values[(row_idx, col_idx)]
if isinstance(value, float) and
re.match('^\d+\.\d+e\+\d+$',unicode(value)):
v = repr(value)
else:
v = unicode(value)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top