0

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.

chris85
  • 23,846
  • 7
  • 34
  • 51
Grégory Santana
  • 61
  • 1
  • 10
  • what if you save your file as .txt extention ?? – lotfio Aug 01 '17 at 12:04
  • they're stored correctly in the file, but you're viewing it using MS Excel; and MS Excel treats numbers and numbers (always a failing, I know), and numbers don't have leading zeroes.... try prefixing with `=` so that MS Excel will treat it as a formula – Mark Baker Aug 01 '17 at 12:05
  • 1
    PS, you do know that PHP has this built-in function called [fputcsv()](http://www.php.net/manual/en/function.fputcsv.php) that will write a csv file cleanly that ugly code of yours, and will handle quotes inside strings correctly – Mark Baker Aug 01 '17 at 12:07
  • @chris85 > no, phone numbers are stored as varchars as some number use foreign countries indicatives. – Grégory Santana Aug 01 '17 at 12:13
  • @Timino : Don't really need a .txt file, but if I do the zeros appear. – Grégory Santana Aug 01 '17 at 12:13
  • @MarkBaker : no change when adding '=' I know there are better options to do that, but I'm kind of in a hurry right now :( As long as I get the Excel file, I'm fine with that. – Grégory Santana Aug 01 '17 at 12:14
  • @chris85 : Good point. It appears when opened with Notepad+, it only disappears in Excel. – Grégory Santana Aug 01 '17 at 12:17
  • use `fputcsv()` it should solve the problem otherwise allow zero fill on your excel – lotfio Aug 01 '17 at 12:18
  • @chris85 : You're right. Adding something in front will force MS Excel to show it as a text. Not the cleanest way to do it but that'll do the trick for what I need it for. Thanks for all your answers ! – Grégory Santana Aug 01 '17 at 12:29
  • Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use prepared statements with bound parameters, via either the [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) drivers. [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples. – Alex Howansky Aug 01 '17 at 14:07

3 Answers3

1

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

suraj kumar
  • 25
  • 5
0

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
chris85
  • 23,846
  • 7
  • 34
  • 51
0

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.

fiduce
  • 1
  • 1