Exporter ses données au format CSV
Il est parfois nécessaire de devoir exporter certaines données provenant du système d’informations. Généralement ce type de données peut être présenté sous forme de données tabulées, TXT, JSON, XML, ODS, XLS ou CSV. Au cours de cet article nous allons explorer l’exportation de données au format CSV (Coma Separated Value), qui est un format standard et non propriétaire.
Afin de pouvoir travailler plus en avant, nous allons avoir besoin de travailler d’une part sur un contenu déjà mis en place et d’autre part sur des modèles, ou des formats, d’extraction avec des entêtes qui soient appropriés. Commençons donc par mettre en place le système d’informations, et pour ce créer une base de données puis y importer des données provenant d’un générateur automatique, ou en vous appuyant sur le fichier SQL fourni pour illustrer cet article. Vous pouvez également vous rapprocher de l’article Base de données fictive pour tester son développement.
Téléchargez les fichiers sources illustrant cet articles.
Description de la base de données
Afin de voir plus clair dans l’exemple employé, décryptons le contenu de la base de données. Le plus simple reste de regarder quelques enregistrements afin d’en comprendre le sens et l’organisation.
Chaque enregistrement, il y en a 100 au total, contient les informations usivantes:
- ch_per_id :Un identifiant servant de clé unique
- ch_per_nom :Le nom de la personne
- ch_per_ref :Une référence purement aléatoire sous la forme XXXXXXXX-XXXX
- ch_per_office :Une valuer arbitraire parmi la liste suivante; Coders Associates, Design and Arts, Foo Commerce, SaaS Online Trader et Training GmbH
- ch_per_mail :L’e-mail de la personne
- ch_per_phone :Un numéro de téléphone au format français
- ch_per_adresse :Le nom d’une rue, précédé ou pas d’un numéro
- ch_per_cp :Un code postal de différente forme, français, canadien, américain…
- ch_per_ville :Un nom de ville
Au delà de l’exportation, choisir un modèle d’exportation
Nous allons voir qu’avec simplement quelques lignes de codes PHP il est très simple d’exporter, tout ou partie, de nos bases de données au format CSV. Alors, profitons en pour aller un tantinetplus loin, et, explorer la possibilité de pouvoir modéliser les données que nous allons exporter. À y regarder de plus près la table, utilisée dans l’exemple d’illustration, présente un certain nombre de colones, et il peut être intéressant de préparer à l’avance divers modèles d’exportation permettant de puiser certaines d’entre elles en fonction de nos besoins.
Nous pourrions prévoir un modèle exportant l’ensemble des données et un autre ne se focalisant par exemple que sur les emails utilisateurs. De plus, il pourrait être intéressant d’envisager l’ajout d’un filtre pour ne cibler uniquement que certains enregistrements qui répondraient à certains critères.
La complexité ne va pas être directement liée au code PHP à mettre en place, mais plutôt à la logique et à l’algorythmie que nous allons employée. Il existe autant d’approches possibles que nous sommes tous différents. Pour ma part, je pense que l’emploi d’un tableau (ARRAY) qui va contenir chacun des modèles peut être suffisament souple à manipuler et surtout à faire évoluer.
Ce tableau devra contenir et refleter divers types de données;
- (« rs_label ») Un identifiant permettant de nommer le modèle. À ce stade, nous pourrions également envisager une autre propriété, telle que « rs_description » qui permettrait quant à elle de donner quelques éléments de description du modèle.
- (« db_fields ») La liste des champs à exporter pour chacun des enregistrements. Une simple chaine de caractères employant un point virgule comme séparateur.
- (« db_headers ») Et enfin, le label, ou l’étiquette, à employer pour chacune des colonnes correspondantes. Là encore une chaine de caractères employant un point virgule comme séparateur peut être utilisée.
Exemple de tableau pouvant être employé;
<?php
define(
"HEADERS" , array (
"default" => array(
"db_fields" => 'ch_per_id;ch_per_nom;ch_per_ref;ch_per_office;ch_per_mail;ch_per_phone;ch_per_adresse;ch_per_cp;ch_per_ville',
"db_headers" => 'ID;Nom;Référence;Office.;E-mail;Tèl;Adresse;CP.;Ville',
"rs_label" => 'Default'
),
"mails" => array(
"db_fields" => 'ch_per_id;ch_per_nom;ch_per_mail',
"db_headers" => 'ID;Nom;E-mail',
"rs_label" => 'Mails'
)
)
);
?>
Le tableau précédent est stocké dans une variable HEADERS et contient lui même deux modèles d’exportation, « default » et « mails ». Chacun de ces modèles propose une liste de champs à exporter, ainsi que les lables correspondant. Leurs propriété « rs_label » servira uniquement à les distinguer et à les nommer.
Organisation des fichiers employés
Afin de pouvoir clarifier et préserver dans le temps l’évolution de cette fonctionalité, nous allons également employer une architecture ouverte sur la répartition des tâches en utilisant divers fichiers PHP;
- __connect.php: le fichier de connexion à la base de données. Nous employerons un classique pilote PDO.
- __variables.php: la possibilité d’externaliser la déclaration des diverses variables employées par l’application, dans notre cas cela va se résumer à la définition des divers modèles au travers du tableau (ARRAY) vu précédemment.
- export-CSV.php: le fichier principal contenant l’interface proposant la sélection du modèle à exporter. Nous allons simplement y ajouter un menu déroulant proposant le modèle, et un ensemble de case à cocher permettant d’affiner la sélection au travers de la valeur du champs « ch_per_office ».
- __export-CSV.php: le fichier qui permettra l’exportation au format CSV
L’usage des underscores devant chaque nom de fichier est purement arbitraire. Cela permet une meilleure différenciation dans leur utilité en tant que fichiers applicatifs en comparaison avec des fichiers de contenus.
Détails des fichiers d’exportation, in et out
Nous allons résumer l’exportation des données à deux mouvements, le mouvement d’entrée qui va permettre le paramétrage et la modélisation de l’extraction des données, et le mouvement de sortie qui va mouliner afin de fabriquer le fichier tabulé.
Le mouvement d’entrée, export-CSV.php
Nous avons vu dans les précédents chapitres que nous devons mettre en place d’une part un sélecteur de modèles et d’autre part un filtre par type d’office.
Les données du sélecteur sont puisées dans la variable HEADERS qui les contient. Une simple boucle de parsage suffit pour en extraire les diverses valuers que l’on peut placer dans un couple d’élément SELECT / OPTIONS. La clé d’identification sera utilisée comme valeur d’option renvoyée par le formulaire et la propriété ‘rs_label’ servira d’étiquette aux labels de sélecteur.
<select name="modele" required="required" id="modele">
<option value="">Selectionnez un modèle</option>
<?php
foreach (HEADERS as $key => $value){
echo '<option value="'.$key.'">';
echo $value['rs_label'];
echo '</option>';
}
?>
</select>
Les données qui vont alimenter le filtre sont directement récupérées dans la base de données. Nous allons dans un premier temps devoir nous connecter à la base de données et récupérer l’ensemble des enregistrements. Un simple GROUP BY `ch_per_office` nous permet d’en isoler les divers types d’organisme.
$sql ="SELECT `ch_per_office` FROM `tab_personnes` GROUP BY `ch_per_office` ASC"; $lists = $dbh->prepare($sql); $lists->execute();
Une fois les enregistrements correspondants sont récupérés, il suffit de faire une boucle pour créer autant de case à cocher que de type d’office correspondants. Du fait que les types d’offices sont directement strockés sur leur forme littérale, et donc n’utilisent pas de valeur d’index ou d’identifiant, nous pouvons utiliser cette valeur aussi bien en temps que valeur renvoyée par le formulaire que label affiché par la case à cocher.
Afin d’envoyer l’ensemble des éléments sélectionnés au sein d’un tableau, nous assignerons un recptacle de récupération fromOFFICE[].
Un dernier élément, un compteur représenté par la variable $cpt, reste nécessaire afin de pouvoir plus facilement distinguer chaque balise INPUT.
<?php
$cpt = 0;
while($list = $lists->fetch(PDO::FETCH_OBJ)){
echo '<label for="modele'.$cpt.'">'.$list->ch_per_office.'</label>';
echo '<input type="checkbox" name="fromOFFICE[]" value="'.$list->ch_per_office.'" id="modele'.$cpt.'">';
$cpt++;
}
?>
Le mouvement de sortie, __export-CSV.php
Une fois l’inclusion des fichiers nécessaires, à savoir __connect.php et __variables.php, nous allons pouvoir vérifier et formatter les variables reçues depuis le formulaire. La première indiquera le modèle d’exportation et sera contenu dans le champs modele et la seconde contiendra l’ensemble des sélections de filtre dans le tableau de données fromOFFICE.
Dans les deux cas il est bon d’anticiper une erreur de transmition et de prévoir une valeur par défaut.
$modele = (isset($_POST['modele']))?$_POST['modele']:'default';
$filtre = (isset($_POST['fromOFFICE']))?"'".implode("','",$_POST['fromOFFICE'])."'":"'Design and Arts'";
Nous pouvons également récupérer la liste des champs à récupérer ainsi que celle des entêtes qui leurs sont associés. Ces informations sont contenues dans les propriétés db_fields et db_headers définies pour chaque modèle d’exportation. Profitons en au passage pour convertir ces chaines de caractères en tableaux PHP en employant la fonction explode.
$fields = explode(';',HEADERS[$modele]['db_fields']);
$entete = explode(';',HEADERS[$modele]['db_headers']);
Une simple requête SQL comportant d’une part le filtre réduisant le champ d’action aux valeurs de ch_per_office va permettre d’extraire les divers enregistrements concernés par l’exportation.
$sql ="SELECT * FROM `tab_personnes` WHERE `ch_per_office` IN (".$filtre.") ORDER BY `ch_per_office` ASC,`ch_per_id` ASC";
$items = $dbh->prepare($sql);
$items->execute();
Les résultats d’extraction de la base obtenus, nous allons pouvoir boucler sur ceux ci pour en rédiger le document CSV. Avant toutes choses créons un tableau pour y stocker toutes ces valeurs et commençons par lui ajouter la ligne d’éléments d’entête.
$CSV = array(); $CSV[] = $entete;
Un tableau temporaire permet de collecter les divers champs de chacun des enregsitrements avant de les ajouter au tableau principal.
while($item = $items->fetch(PDO::FETCH_OBJ)){
$CSVtemp = array();
foreach ($fields as $field) {
$CSVtemp[] = $item->{$field};
}
$CSV[] = $CSVtemp;
}
La collecte terminée, il ne nous reste plus qu’à préparer un fichier texte qui va recevoir toutes ces informations. La première ligne de code va mettre en place un nom de fichier. Utilisons une date accompagnée d’un horodatage afin d’éviter d’écraser un fichier qui aurait pu porter le même nom.
En seconde ligne complétons le chemin d’accès en y ajoutant un dossier d’exportation. Ici optons pour une banalité du type EXPORTS, suffisament explicite pour y retrouver les divers fichiers exportés.
Et enfin en troisième ligne, ouvrons ce fichier qui pour l’instant est toujours virtuel en mémoire. En précisant le paramètre w, nous nous assurons que le fichier est en mode écriture seule et que le curseur se trouve bien au début du fichier.
$filename = 'export_'.date('Y-m-d__h-i-s').'.csv';
$fichier = 'EXPORTS/'.$filename;
$fp = fopen($fichier, 'w');
Si le fichier CSV final est destiné à être exploité par Excel, il se peut que l’encodage UTF-8 cause soucis lors de l’ouverture. Pour cela, il est important d’ajouter un marqueur BOM en début de fichier. Ce marqueur d’ordre d’octet est composé de trois premiers octects ayant pour valeurs hexadécimales 0xEF, 0xBB et 0xBF.
fputs($fp, $bom =( chr(0xEF) . chr(0xBB) . chr(0xBF) ));
Le fichier est prêt, nous pouvons alors le remplir avec l’esemble des enregistrements collectés. Là encore une simple boucle alimentant la fonction fputcsv() permet de rédiger le fichier CSV final. Deux paramètres sont cependant intéressant, le délimiter ; permettant de séparer les valeurs (CSV), et le caractères d’encadrement (dans notre cas « ). Si besoin un troisième paramètre, celui d’échapement, peut être employé.
foreach ($CSV as $line) {
fputcsv($fp, $line, ";",'"');
}
Il ne reste plus qu’à clore l’instance du fichier et d’en afficher son nom pour le retrouver plus facilement dans le dossier EXPORTS.
fclose($fp); echo $filename;
Bien sur, l’ensemble de cet algorythme peut être amélioré ou affiné, mais déjà les bases d’une exportation en fonction de différents modèles et filtres peut déjà être mis en application.