Reputation: 3491
when i get data from database and export it as a csv file i have following issues :
1E+12
how to force MS Excel to show all of my data as string by PHP
Upvotes: 4
Views: 7009
Reputation: 19
Kai's answer didn't quite work for me. I ended up CONCATing an equals sign (still with the double quotes on either end) to the front and got the desired result. Our users wanted to be able to copy and paste the number straight out of Excel into our UI.
CONCAT('="',too_long_number_field,'"')
Upvotes: 0
Reputation: 4969
I format it as a string by concatenate it with spaces at its start and end.
CONCAT(" ", database_number, " ") AS "Number For Excel"
.
Upvotes: 0
Reputation: 63
You can use number_formate() function to convert ...
View link
Function : number_format() Syntax : number_format ( float $number , int $decimals = 0 , string $dec_point = ‘.’ , string $thousands_sep = ‘,’ ) Example to convert 6.90743E+11 to number use below code
number_format(6.90743E+11,0,'','') // outputs 690743000000
Upvotes: -1
Reputation: 342
You can use double quotes contain the numbers, like this:
if(is_numeric($column)) $column = '"'.$number.'"';
Upvotes: 4
Reputation: 1028
This isn't a MySQL issue, it's an Excel thing.
This... might help: http://answers.microsoft.com/en-us/office/forum/office_2007-excel/disabling-scientific-notation/943b8103-8c50-451d-8037-c697422e2307
But this question is more MS Office related than programming.
(or as comments suggested, use a file format that carries display formatting info, such as .xls)
Upvotes: 0