Excel number formatting in PHP CSV Import (Number with exponential sign)

computerzworld

Hello,
I am having one csv which I am importing. In this csv one column contains numbers. I have one number 123456789011 entered in that column but excel is converting it to 1.23E+011 and as soon as I import the csv it is considering the number as 1.23E+011. I am unable to get original number using PHP scripting. I have tried by type casting the number but it is rounding off the number. So is there any way so that I can get the whole number as number? Please help me. Thanks in advance.

bpat1434

I'm just going to venture a guess, but have you tried expanding the column once in Excel so that there's more room for the number?

If the above doesn't work, then there's nothing you can really do except to reformat the column as "text" instead of a number (scientific).

I get upset at Excel that when I export a specific date format (Jan 1, 2009 10:00 am) it will reformat it to 1/1/09 10:00 am instead of showing what I want. No way I can get around that other than to change the format of that particular column.

I wish there was better news, but there isn't. The only other thing you could try, is writing directly to a document written in Excel 2007 XML format. Not sure how feasible that is though.

computerzworld

Thanks for the reply. So we can not format & get value in PHP right?

Weedpacket

If it's Excel mangling the value (have you checked that yet?) then I guess I need to revert my signature.

computerzworld

excel itself doesn't preserves the value as soon as I save & close it and reopening again. When I am reopening it after closing it , it is rounding off the value.

bpat1434

It sounds to me like you're exporting data from a database via PHP to a CSV script, opening that in Excel, changing some value(s) and saving. THen when you re-open the file, Excel does its own formatting.

That sounds like an Excel problem to me, not a PHP problem.

Weedpacket

So what you're saying is that you haven't actually looked at the CSV file itself to see how the value is stored there, just trusting Excel's interpretation of it?