Mohammad Masoudian
Mohammad Masoudian

Reputation: 3491

numbers issue after export mysql data to CSV

when i get data from database and export it as a csv file i have following issues :

how to force MS Excel to show all of my data as string by PHP

Upvotes: 4

Views: 7009

Answers (5)

Bill Parnell
Bill Parnell

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

Ari
Ari

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

Arshad Ali
Arshad Ali

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

Kai
Kai

Reputation: 342

You can use double quotes contain the numbers, like this:

if(is_numeric($column)) $column = '"'.$number.'"';

Upvotes: 4

SubSevn
SubSevn

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

Related Questions