Export / import de fichier CSV vers une base SQL : eternel soucis du caractère délimitant un texte

La vie d'un fichier CSV serait si simple, si les colonnes ne pouvaient pas contenir du texte libre. Pour rappel, la structure d'un fichier CSV est simple

• Une ligne correspond à un enregistrement
  (cad une ligne d'une table de la base de donnée)
• Les lignes sont délimitées par un ou 2 caractères de fin de ligne
  (au choix parmi CR et LF - code ASCCI 13 et 10).
• Les colonnes sont délimités par un caractère spéciale
  (traditionnellement ; espace tabulation ou virgule).

Si aucune de vos colonnes (cad les champs d'un enregistrement) ne peuvent contenir l'un de ces 3 caractères, aucun soucis. Cette structure de fichier CSV serait indestructible. Malheureusement, rien n’empêche un champ numérique de contenir des virgules, et pour un champs texte de contenir des CR, LF ou autre point-virgule. Dans ces conditions, le formats CSV devient un enfer pavé de bonnes intentions.

Pour résoudre ce problème des caractères parasites à l'intérieur d'une colonne (cad d'un champ d'une ligne/enregistrement), la plupart des fichiers CSV encapsule les colonnes par des guillemets (ou des simples quottes). Ce caractère devient alors une barrière contre le problème des caractères parasites (fin de ligne, délimiteur de colonne). Encore faut-il pouvoir également s'affranchir du caractère guillemet parasite, c'est à dire s'affranchir du problème des caractères guillemets présent dans des colonnes du CSV. A ce niveau, seul l'outil d'export de fichiers CSV peut gérer le problème des caractères parasites. Pour résoudre ce soucis, les outils d'export de CSV (Excel par exemple) échappent les guillemets (le caractère choisi pour encapsuler les colonnes du CSV) avec un caractère spécial. Dans Excel par exemple, le caractère d'échappement du guillemet est le guillemet lui-même. Un guillemet présent dans une colonne sera alors remplacé par un double guillemet.C'est ainsi qu'un double guillemet dans un CSV, ne sera pas considéré comme un caractère encapsulant une colonne.

Pour décrire cette mécanique, voici un exemple d'un ligne d'un fichier CSV à 2 colonnes, avec comme délimiteur de colonne le point-virgule:
CODEnnn;texte libre  .
Ce premier exemple est présenté sans aucun caractère encapsulant les colonnes. Si une des colonnes contenait un point virgule, il deviendrait impossible de déterminer les limites d'une colonne comme par exemple:
CODEnnn;texte libre avec un ; (point-virgule) ...
Pour cette raison, les colonnes sont encapsulées par un caractère (dans notre exemple, le guillemet) signifiant le début et la fin d'une colonne de type texte, et qui permet d'annuler le ; inclus dans la colonne:
"CODEnnn";"texte libre avec un ; (point-virgule) ..."
Dans ce cas tout se passe bien, sauf si un guillemet est lui même présent dans la colonne:
"CODEnnn";"texte libre avec un ";" (point-virgule) ..."
Dans ce cas, c'est à l'outil d'export de CSV de faire le travail d'inclure un caractère d'échappement du guillemet. Pour Excel, le caractère d'échappement par défaut est ". Notre exemple devient alors:
"CODEnnn";"texte libre avec un "";"" (point-virgule) ..."

A partir de ces deux caractères (celui d'échappement et celui d'encapsulation d'une colonne), la structure d'un CSV devient robuste, et résiste même aux caractères CR et LF parasites.

Encore faut-il vérifier ou paramétrer votre outil d'export de fichiers CSV pour que ce mécanisme s'active convenablement. Si vous maitrisez votre outil d'export pour appliquer ce mécanisme, vous pourrez alors paramétrer votre outils d'import de fichier CSV. Dans le cas d'Excel (en tant que import et export), pas de soucis, le " est le caractère d'échappement et encapsulation de colonne. Mais dans le cas d'une base de données SQL, cela peut jouer des tours. Si par exemple vous passez par un outil client SQL (par exemple l'excellent HEIDI), pour réaliser des imports, 2 choix s'offrent à vous: un import coté client (en exécutant des UPDATE généré par la lecture local du fichier CSV) ou un import déporté (en utilisant l'utilitaire d'import native de la base de données). Vous serez étonné de découvrir que le même fichier CSV pourra être en échec dans un cas et OK dans l'autre. D'où la nécessité de réaliser de nombreux test.

Le format CSV comme format pivot pour des bases de données est intéressant pour sa simplicité, mais à la seule et unique condition de maitriser parfaitement votre chaine d'export ET d'import de ces fichiers.

PS:
Pensez au problème du charset. Problème relativement simple mais tout aussi critique que nos petits caractères parasites. Et pour ce problème, les outils sont souvent mal documentés.