By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,852 Members | 1,426 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,852 IT Pros & Developers. It's quick & easy.

Exce to CSV conversion........

P: 36
Hello All,

I have one script it converts from Excel to CSV file.
Here I am reading excel file contents cell by cell. Here I have one problem here if I find values like 66666666666666666666666666 will be appear as 666666666666+E something will appear. But its exact value I need here.
Please help me in completing this code.
Jan 15 '08 #1
Share this Question
Share on Google+
3 Replies


prn
Expert 100+
P: 254
prn
The problem here is that you cannot get an "exact" value. I'm not going to turn this into a course in numerical analysis, but there are at least two classes of rational number values that simply cannot be represented exactly. Sometimes a decimal value does not terminate, e.g., 1/3, 1/7, etc. These values cannot be represented by a finite-length decimal number. The number you have in your post appears to be related to this. Another case is where a nice "clean" decimal number cannot be represented as a finite string in binary. It happens when you have different number bases involved. The reasons are the same as the better-known decimal numbers, but from your point of view you have at least two classes of non-terminating numbers.

You are seeing one of those here. I'm not claiming to be an expert in how Excel represents such numbers internally, but the interface you are using to read the cells gives you "scientific notation" as its output.

Does that specific value show up differently inside Excel? What happens if you export from Excel to CSV? What method are you using to read the cell contents? Have you checked out any of the *CSV* modules on CPAN?

HTH,
Paul
Jan 15 '08 #2

P: 36
Hello I am not using any such modules but what i am doing here is just saving .xls to .csv files.

there I have some trouble.

shafi


The problem here is that you cannot get an "exact" value. I'm not going to turn this into a course in numerical analysis, but there are at least two classes of rational number values that simply cannot be represented exactly. Sometimes a decimal value does not terminate, e.g., 1/3, 1/7, etc. These values cannot be represented by a finite-length decimal number. The number you have in your post appears to be related to this. Another case is where a nice "clean" decimal number cannot be represented as a finite string in binary. It happens when you have different number bases involved. The reasons are the same as the better-known decimal numbers, but from your point of view you have at least two classes of non-terminating numbers.

You are seeing one of those here. I'm not claiming to be an expert in how Excel represents such numbers internally, but the interface you are using to read the cells gives you "scientific notation" as its output.

Does that specific value show up differently inside Excel? What happens if you export from Excel to CSV? What method are you using to read the cell contents? Have you checked out any of the *CSV* modules on CPAN?

HTH,
Paul
Jan 17 '08 #3

prn
Expert 100+
P: 254
prn
Hello I am not using any such modules but what i am doing here is just saving .xls to .csv files.
Hi Shafi,

I suspected that you were not using any pre-written modules -- that's why I suggested them.

If the problem occurs when you save your spreadsheets to .csv in Excel, then your problem is definitely not a Perl problem. If you are simply (as a human) reading the .csv file and finding numbers formatted in ways you do not want, that is certainly not a Perl problem.

Is the problem that you would rather format these floating point numbers in a different way as your Perl routine reads and processes the CSV file, then that's no problem. Perl can read numbers in that format and can save them in whatever format you choose. The easiest thing for you to do probably involves some version of printf (or sprintf). Check out those functions.

Best Regards,
Paul
Jan 17 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.