Choses à faire

24h dans une journée, et tant de choses à faire !

Générer un CSV compatible Excel avec PHP

publié le 13 octobre 2009 par Pierre Quillery

Passage quasi-incontournable pour tout développeur web : l’exportation de données au « format » CSV. Pourquoi ces guillemets ? Le CSV n’est malheureusement pas un format à part entière, car il n’existe aucune règle définitive pour le générer et le lire. Ce qui risque de vous jouer des tours suivant les paramètres que vous utiliserez, car l’importation dans le tableur de votre client sera alors plus ou moins aisée avec les réglages par défaut.

Excel utilise des règles particulières pour interpréter les fichiers CSV, nous allons les détailler rapidement et voir quels sont les moyens de résoudre facilement ces problèmes dans un environnement unix/php.

Bien entendu, si l’utilisateur visé est déjà familier des tableurs, il saura comment régler son logiciel pour importer le fichier comme il faut – mais si vous avez affaire à quelqu’un qui n’y connaît rien, vous gagnerez du temps à vous adapter à se façon de faire plutôt que de chercher à lui expliquer comment s’adapter à la vôtre. Ainsi, il semble préférable de convenir à l’avance avec votre client du logiciel qu’il souhaite pouvoir utiliser, ça vous évitera les mauvaises surprises et les pertes de temps.

Règle n°1 : On oublie l’utf8 !

Si vous travaillez sous Linux, vous avez sûrement oublié que si vous, vous travaillez nativement en UTF8, ce n’est pas le cas des autres OS – ne perdez pas de vue que les versions d’Excel pour Mac et PC utilisent chacune l’encodage par défaut du système, c’est à dire respectivement macintosh et windows-1252 dans un environnement francophone (ces deux encodages sont heureusement très proches du standard iso-8859-15 donc vous pouvez vous reposer sur ce dernier).

Pour cela vous avez à votre disposition plusieurs fonctions intégrées à PHP, suivant votre configuration : utf8_decode(), un grand classique, mais également iconv() et mb_convert_encoding(). La première, qui est aussi la plus simple à utiliser, devrait faire votre affaire dans la majorité des cas 😉 ! Attention toutefois à ne pas mélanger les encodages avant d’utiliser ces fonctions : elles ne fonctionneront tout simplement pas ! Encodez-les au fur et à mesure.

Règle n°2 : Attention aux retours à la ligne !

Un peu dans la veine de la règle numéro 1, faites attention à bien remplacer les fins de ligne de votre fichier par des \r\n si vous ciblez un système Windows. Pour cela, il est assez efficace de générer simplement votre fichier à l’aide de php puis d’utiliser sed (pour Stream Editor), programme installé sur quasi tous les serveurs *nix. Il s’agit en fait d’un puissant éditeur de texte en ligne de commande. Voilà par exemple ce qu’il vous faut pour remplacer tous les retours à la ligne d’un fichier donné :

# Le "-i" signifie in-place, ce qui vous permet d'éditer directement le fichier visé
# Le "s" signifie substitute, suivent deux expressions régulières placées entre deux /
# Enfin, le g signifie que la substitution est globale, on peut également s'arrêter à la Xième occurrence.
sed -i 's/\n/\r\n/g' "chemin/vers/le_fichier.csv"

Règle n°3 : Le séparateur n’est *pas* une virgule !

Si vous avez fait un peu d’Anglais, (et que vous écoutiez pendant les cours) vous savez que Comma Separated Values signifie littéralement « Valeurs Séparées par des Virgules » dans la langue de Molière – or en réalité ce n’est pas si simple. En effet, les concepteurs d’Excel ont choisi de faire varier le séparateur par défaut suivant la locale de l’environnement.

Ainsi, en Anglais, c’est bien une virgule qui sépare les valeurs ; mais en Français, c’est un point-virgule.

Pourquoi ce choix me direz-vous ? L’explication est simple : en Anglais, le séparateur des chiffres est un point, et non une virgule – ce dernier caractère est donc assez discriminant pour séparer des valeurs. En Français toutefois, l’usage veut que nous utilisions une virgule pour séparer les chiffres ; il fallait donc trouver un autre caractère, et le choix s’est naturellement porté sur le point-virgule.

Heureusement pour nous, ainsi que nous le verrons un peu plus loin, la fonction native de php que nous utiliserons pour générer notre fichier CSV permet de changer le séparateur des valeurs par défaut qui est bien sûr … Une virgule.

Règle n°4 : Attention à la mémoire !

Le scripts PHP qui s’exécutent votre le serveur n’ont pas une quantité de mémoire infinie, aussi il est préférable d’user de cette dernière avec parcimonie lorsque vous manipulez beaucoup de données : par exemple si vous faites une requête SQL qui retourne 10000 enregistrements avec beaucoup de données, vous risquez fort de faire planter votre script soit au moment du chargement, soit au moment de l’écriture dans un fichier. À ce moment là deux solutions s’offrent à vous : 1) programmer en tenant compte de la quantité de données à traiter et/ou 2) augmenter la mémoire disponible pour le script.

Augmenter la mémoire

Cette deuxième possibilité est la plus facile à mettre en place, mais il s’agit là d’un bricolage (que vous n’avez peut-être même pas la possibilité d’utiliser si vous êtes sur un hébergement dédié) :

ini_set('memory_limit', '16M');

Attention toutefois car même si votre script est maintenant traité sans problème, il suffira que la quantité de données augmente un peu pour que vous soyez obligé de le refaire – et là vous ne vous rendrez peut-être pas compte à temps que votre script ne fonctionne plus ou que vous n’avez plus de mémoire à allouer : c’est jouer avec le feu !

Adapter votre requête au chargement massif de données

Pour cela il va falloir écrire un peu de code et jouer avec le paramètre LIMIT de SQL pour lire les données progressivement, lot par lot en quelque sorte :

<?php

// Notre premier lot sera le numéro 0
$lot_courant = 0;
// Nombre d'éléments par lot à faire varier selon vos besoins
// et votre serveur
$nombre_elements_lot = 50;

// La boucle en do-while va nous permettre de traiter lot par lot
do {
  // Création de la requête
  $requete = 'SELECT nom,prenom,presentation FROM table ';
  $requete .= "LIMIT ${lot_courant}, ${nombre_elements_lot}";
 
  // On exécute la requête, ça dépend de votre ORM
  $resultats = SQL::execution_requete($requete);
 
  // Comptons le nombre de résultats obtenus
  $nombre_resultats = sizeof($resultats);
 
  // À ce moment-là on peut écrire les données dans le fichier
  // de notre choix via fputcsv(). Voir plus bas.
 
  // On traitera éventuellement le lot suivant à la prochaine itération
  $lot_courant++;

  // On déréférence manuellement les résultats
  unset($resultats);
}
// On relance la requête si le lot est complet
while($nombre_resultats == $nombre_elements_lot);

Bien sûr, ce code est à adapter à vos besoin, mais globalement cela vous permettra de traiter les données au fur et à mesure sans craindre de sortir du cadre de la mémoire de PHP.

Règle n°5 : Comment générer mon fichier CSV ?

Programmer une fonction qui va générer du CSV à votre place peut être tentant car facile en apparence ; sachez toutefois que la tâche peut se révéler assez ardue et que vous risquez de tomber sur certains cas critiques, même si vous prenez toutes vos précautions, et qu’il vous faudra faire de nombreux tests pour être certain que rien ne brise vos fichiers : ce n’est pas une aussi mince affaire que cela peut paraître.Utiliser une classe toute faite peut être une alternative tentante si vous travaillez dans un contexte entièrement objet, par exemple. Là aussi, une certaine méfiance est de mise avec ce que vous trouverez sur le net : générer un fichier CSV est très simple, s’assurer de son intégrité après coup l’est beaucoup moins.

PHP dispose depuis la version 5.1.0 de la fonction fputscsv() qui vous permettra d’écrire directement dans un fichier le contenu d’un Array converti au format CSV. C’est cette solution que j’ai adopté, et c’est celle-ci que je recommande pour éviter les surprises : j’ai réussi à exporter une quantité importante (près de 10 Mo) de données assez complexes (contenant notamment du HTML avec des commentaires spécifiques à Word dedans) sans pour autant mettre cette fonction en défaut donc elle me paraît digne de confiance.

Je vous livre ci-dessous le code d’une fonction de conversion qui vous permettra en plus d’ajouter automatiquement des libellés de colonne à votre fichier :

<?php
/**
  Fonction d'exportation d'un tableau de données vers un fichier donné.
  On peut l'appeler plusieurs fois pour ajouter des données au fur et à mesure
  @param $chemin_fichier Chemin du fichier dans lequel on place les données - 
  il doit être vide ou inexistant lors du premier appel à la fonction
  @param $donnees Tableau associatif de données au format 
  array( 
    array( 'cle' => 'valeur','cle2' => 'valeur' ), 
    array( 'cle' => 'deuxième valeur ...', 'cle2' => 'valeur' ) 
    // et ainsi de suite ...
  ) 
*/
function conversion_vers_csv($chemin_fichier, array $donnees) {
  
  // On cherche des infos sur le fichier à ouvrir
  $infos_fichier = stat($chemin_fichier);
  
  // Si le fichier est inexistant ou vide, on va le créer et y ajouter les 
  // libellés de colonne.
  if(!file_exists($chemin_fichier) || $infos_fichier['size'] == 0) {
  
    // On ouvre le fichier en écriture seule et on le vide de son contenu
    $fp = @fopen($chemin_fichier, 'w');
    if($fp === false) 
      throw new Exception("Le fichier ${chemin_fichier} n'a pas pu être créé.");
    
    // Les entêtes sont les clés du tableau associatif
    $entetes = array_keys($donnees[0]);
    
    // Décodage des entêtes qui sont en UTF8 à la base
    foreach($entetes as &$entete) { 
      // Notez l'utilisation de iconv pour changer l'encodage.
      $entete = (is_string($entete)) ? 
        iconv("UTF-8", "Windows-1252//TRANSLIT", $entete) : $entete; 
    }
    
    // On utilise le troisième paramètre de fputcsv pour changer le séparateur 
    // par défaut de php.
    fputcsv($fp, $entetes, ';');
  }
  
  // On ouvre le handler en écriture pour écrire le fichier
  // s'il ne l'est pas déjà.
  $fp = ($fp) ? $fp : fopen($chemin_fichier, 'a');
  
  // Écriture des données
  foreach ($donnees as $donnee) {
    foreach($donnee as &$champ) { 
      $champ = (is_string($champ)) ? 
        iconv("UTF-8", "Windows-1252//TRANSLIT", $champ) : $champ; 
    }
    fputcsv($fp, $donnee, ';');
  }
  
  fclose($fp);
}

Voilà, c’est tout pour aujourd’hui, j’espère que cet article vous aura été utile !

Update : Quelques données intéressantes

Killian demandait avec raison si déréférencer manuellement le tableau à la fin du traitement par lot n’impactait pas les performances, et je n’ai pas su trop quoi répondre sur le moment. J’ai donc procédé à quelques petits tests que je trouve significatifs, et que je me propose d’expliquer ici : il s’agit simplement d’une liste de 60 000 utilisateurs avec pas mal d’informations et de traitements derrière, voilà les chiffres obtenus.

Avec unset

Temps d’exécution du script : 15 secondes
Consommation de mémoire entre 5 394 416 et 5 505 024 octets.

Sans unset

Temps d’exécution : 15 secondes
Consommation de mémoire entre 8 325 644 et 8 388 608 octets.

D’après ces tests il semble que l’utilisation d’unset soit tout à fait justifiée par la consommation de mémoire et le fait que cela n’impacte pas le temps. Cela étant dit, sur le serveur est installée la version 5.2.10, donc pas nécessairement la toute dernière mouture, ce qui explique peut-être la nécessité de recourir à une déréférenciation (bon courage pour dire ça rapidement une dizaine de fois ;)) manuelle (j’ai obtenu la consommation de mémoire à l’aide de la fonction memory_get_peak_usage() en essayant ses deux modes de fonctionnement).

Les textes, illustrations et démonstrations présents sur ce site sont la propriété de leurs auteurs respectifs, sauf mention contraire (photo de la bannière).
Chosesafaire.fr, un site propulsé par Wordpress, vous est proposé par Pierre Quillery & Killian Ebel.

Valid XHTML 1.0 Strict