CODEWITHSUNDEEP
phpexcelleading-zero
Grégory Santana
Grégory Santana

Reputation: 65

How to keep "zeros" in cells when exporting from PHP to EXCEL?

I'm using this simple script to export from PHP to EXCEL file :

<?php 
header('Content-Type: text/csv;');
header('Content-Disposition: attachment; filename="TDO-2017-'. $_GET['lieu'].'.csv');

require('../scripts/lib.php');
?>
"NOM";"PRENOM";"E-MAIL";"TELEPHONE";"ADRESSE";"CODE POSTAL";"VILLE"
<?php
echo "\n";
$rq = mysqli_query($connexion, 'SELECT * FROM tui_inscription WHERE lieu = "'. $_GET['lieu'] .'" AND valid = 1');
while($d = mysqli_fetch_assoc($rq)) {
    echo '"' . $d['nom'] . '";"' . $d['prenom'] . '";"' . $d['email'] . '";"' . str_replace("+33","0", $d['telephone']) . '";"' . $d['adresse'] . '";"' . $d['cpostal'] . '";"' . $d['ville'] . '"'."\n";
}
?>

It does the job perfectly, the only problem is the leading zero in phone numbers disappears. I've seen many explanations on how to use other libraries in order to determine the cell's type but as I do not know PDO and the script already works, I first wanted to know if there was a way I could change my code to do the trick.

Upvotes: 0

Views: 10091

Answers (3)

suraj kumar
suraj kumar

Reputation: 35

Well Adding a simple &nbsp; in the table <td> tag can convert the number into a text.

Upvotes: 2

fiduce
fiduce

Reputation: 101

This is a severe issue. If a text delimiter is no more making a field be considered as text, then something is going wrong. Text delimiter's purpose should not be limited to escape general field delimiter. I append ASCII code 160 to any text field. Its the french financial numbers group spacer. It looks like a space, but it is not trimmed by the data import process. With that, even quotes are useless : the field will be considered as text anyway ! With that trailing char, I get text cells even if value is 000234. Any other solution I've tried missed something.

Upvotes: 0

chris85
chris85

Reputation: 23892

Per the commenting the issue is not in the CSV construction but Excel's data formatting conversion. Excel auto sets the field to number and numbers don't have leading zeros. Per the Microsoft doc, https://support.office.com/en-us/article/Keeping-leading-zeros-and-large-numbers-1bf7b935-36e1-4985-842f-5dfa51f85fe7?ui=en-US&rs=en-US&ad=US, you can get a column to text be prefacing it with a quote.

You can type an apostrophe (') in front of the number, and Excel will treat it as text.

So:

echo '"' . $d['nom'] . '";"' . $d['prenom'] . '";"' . $d['email'] . '";"\'' . str_replace("+33","0", $d['telephone']) . '";"' . $d['adresse'] . '";"' . $d['cpostal'] . '";"' . $d['ville'] . '"'."\n";

should work for you.

Additionally you should parameterize your query so you aren't exposed to SQL injections:

  1. http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

and you should use the PHP built in CSV generator:

  1. http://php.net/manual/en/function.fputcsv.php

Upvotes: 0

Related Questions