Exporting mysql db(data with numbers) to csv

Question

im trying to write a coed where i have to export the contents of my database into a csv file

the exporting already works however my exported csv is having problems with numbers

for example my sql database looks like this

telephone number
+639063073755

however in the exported csv the data from the telephone number becomes like this

telephone number
6.39063E+11

the telephone number becomes an exponent

is there a way to fix this

here is my code

$sql = DB::query("SELECT * FROM table 1 order by id ASC");
$col2 = DB::query("SELECT * FROM table2 order by col_id ASC");
$rp_name = DB::queryOneField('rp_name',"SELECT * FROM report_presets where rp_id=%i",$_GET['rp_id']);
$rp_name = str_replace(" ","",$rp_name);


$line1 .= "Telephone Number";


foreach($sql as $row){
$line2 .=

str_replace(",","",$row[mobile_no]). ","

."\n";
    }

$data="$line1\n$line2\n";

header("Content-type: application/x-msdownload"); 
header("Content-Disposition: attachment; filename=".$rp_name.".csv"); 
header("Pragma: no-cache"); 
header("Expires: 0"); 
print "$header\n$data";

thanks

Does it really show 6.39063E+11 in the raw CSV content (view in a plain text editor)? or is your software changing it to that (ie Excel). — MrCode
– MrCode, Commented Dec 3, 2012 at 10:34
im using excel. i guess the problem lies with excel thanks for the answer — itsover9000
– itsover9000, Commented Dec 3, 2012 at 10:39
concert the number into string before sending to CSV file and convert back to long before retrieving — Hussain Akhtar Wahid 'Ghouri'
– Hussain Akhtar Wahid 'Ghouri', Commented Dec 3, 2012 at 11:12

Arun Aravind · Accepted Answer · 2012-12-03 10:35:02Z

2

If you are using excel to view the csv this problem may occur. Thats not the problem of your code. try formatting the column as number

answered Dec 3, 2012 at 10:35

Arun Aravind

1011 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

itsover9000 Over a year ago

i guess excel is the one that automatically converts it into an exponent. now my only worry is if someone who does not know how to use excel uses the code

Arun Aravind Over a year ago

The solution is to force excel to treat the number as a string itself and not a number. For that put a $ or any alphabet or something like that at the end.

Arun Aravind Over a year ago

Well I guess putting two or more tabs at the end will do.

Arun Aravind Over a year ago

So that your display won't be affected

heikkim · Accepted Answer · 2012-12-03 10:38:15Z

2

The problem is in how Excel handles the contents of the CSV. Follow these instructions in order to get the desired result.

answered Dec 3, 2012 at 10:38

heikkim

2,9852 gold badges25 silver badges35 bronze badges

Comments

Ravi Maniyar · Accepted Answer · 2012-12-03 10:38:52Z

1

Thats because of excel cell width . can u add any character before the telephone number like " call:989898989898". Then there wont be problem.

answered Dec 3, 2012 at 10:38

Ravi Maniyar

6672 gold badges8 silver badges23 bronze badges

1 Comment

itsover9000 Over a year ago

this method worked but it just doesnt feel right when there is a word right next to it. thanks for the answer

Collectives™ on Stack Overflow

Exporting mysql db(data with numbers) to csv

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related