Converting Excel into CSV using Python: Issue with Numbers

[zulfi123786]

Hi,


I am designing Python code that would dump the excel contents into CSV file on Unix server and have a problem with numbers


When the values are dumped into CSV, I want to have the values that appears in the cell to be dumped rather than the real values of double precession's which is actually stored.


Suppose an user types 4.31 in the cell formatted as General, it would get stored as double precision whose real values is '4.3098989898989801' and if '9.3478' is entered it would be stored as '9.3477999999999994'.


My Python code when run against the above excel containing 4.31 and 9.3478 fetches the below values
4.3098989898989801
9.3477999999999994


The excel files are user created and I don't have any control over them, the users would run this Python code when they want to convert them into CSV files (this is on Unix environment)


From 4.3098989898989801,9.3477999999999994 I have to get back '4.31' and '9.3478' which is exactly what the user entered any other values if fetched would be called as a bug by the user.


Why Rounding would not help:


4.31 has two digits of precision
9.3478 has 4 digits of precision and if rounded to 2 digits of precision it would end up as 9.35 which is not what the user entered.


Since the users can enter data with varying digits of precision, I can't apply the round function.


Having said this, If '4.31' is entered and its stored as '4.3098989898989801', if the excel is closed and reopened and it displays '4.31' it means that excel is applying some formula or has remembered the exact precision of the user entered value so it was able to get back 4.31 from 4.3098989898989801 and 9.3478 from 9.3477999999999994


If I enter 4.3098989898989801 in an excel cell (format as General) it shows me 4.31 instead of 4.3098989898989801 which means it has rounded to two decimal digits and when I enter 9.3477999999999994 it shows 9.3478 and this time it has rounded to 4 digits of precision.


This means that excel dealing with the inputs 4.3098989898989801,9.3477999999999994 in some fashion to get 4.31 and 9.3478 ? I need to do the same in my Python code to get back what the user actually entered.


Could some one help me solve the mystery.


Thanks

 

[John_w]

Don't know about Python, but if I was doing this in VBA I would use the Text property of the cell instead of the Value property. Text returns the displayed value.

    MsgBox Range("A1").Text & " " & Range("A1").Value