Ensure

Pourquoi votre CSV s'ouvre mal dans Excel

Virgules, points-virgules et tabulations selon la locale, ainsi que les guillemets, l'échappement, le BOM UTF-8 et une méthode fiable pour déterminer quel séparateur un fichier utilise vraiment.

Excel ne regarde pas à l'intérieur d'un fichier .csv pour déterminer comment il est séparé. Quand vous double-cliquez dessus, il utilise le séparateur de listes des paramètres régionaux de votre système d'exploitation, et si celui-ci ne correspond pas au fichier, chaque ligne atterrit dans une seule colonne.

Au Royaume-Uni et aux États-Unis, le séparateur de listes est la virgule. Dans la plus grande partie de l'Europe continentale et dans une bonne part de l'Amérique latine, où la virgule sert de séparateur décimal, le séparateur de listes est le point-virgule. Un fichier valide séparé par des virgules envoyé depuis Londres s'ouvre donc en une seule colonne tassée à Amsterdam, Berlin ou Madrid, et un fichier à points-virgules fait de même en sens inverse. Rien n'est corrompu ; le fichier est lu avec la mauvaise hypothèse.

Trois façons de l'ouvrir correctement

  1. Importez au lieu d'ouvrir. Dans Excel, Données, puis Obtenir des données ou À partir d'un fichier texte/CSV, et choisissez le délimiteur et l'encodage dans la boîte de dialogue. C'est la seule voie qui vous donne un contrôle complet, et la seule qui survive à un envoi vers un autre pays.
  2. Renommez le fichier en .txt. Excel refuse de deviner pour un fichier .txt et affiche à la place l'assistant d'importation, qui vous demande quel est le séparateur.
  3. Convertissez le fichier au séparateur qu'attend votre lecteur avant de l'envoyer. Si vous savez que le destinataire travaille dans une locale à points-virgules, envoyez-lui un fichier à points-virgules.

La ligne sep=

Excel et LibreOffice comprennent tous deux une première ligne spéciale qui l'emporte sur le réglage de la locale :

sep=;
name;email;country
Ada;ada@example.com;UK

Cela fonctionne, et c'est un remède courant au « le client n'arrive pas à ouvrir mon export ». Sachez qu'il s'agit d'une convention de tableur, et non d'un élément d'une quelconque spécification CSV. Donnez le même fichier à un script ou à un import de base de données et sep=; sera lu comme une première ligne ordinaire, devenant en général un en-tête de colonne fantaisiste. Réservez-le aux fichiers destinés à un humain muni d'un tableur, pas aux échanges de machine à machine.

Guillemets et échappement

La référence informelle pour le CSV est la RFC 4180, qui décrit une pratique courante plutôt qu'elle ne la dicte. Ses règles sont brèves :

  • Un champ doit être entouré de guillemets droits s'il contient le séparateur, un guillemet droit ou un saut de ligne.
  • Un guillemet droit à l'intérieur d'un champ entre guillemets s'écrit deux fois.
id,name,note
1,"Smith, Ada","She said ""hello"" twice"
2,"Multi
line note",fine

La ligne 1 compte trois champs, pas quatre, parce que la virgule se trouve entre guillemets. Les guillemets doublés se réduisent à un seul à l'analyse. La ligne 2 contient un véritable saut de ligne à l'intérieur d'un champ entre guillemets, ce qui est autorisé et explique pourquoi on ne peut pas découper un CSV de façon fiable en comptant les lignes.

L'échappement par barre oblique inverse (\, ou \") est une autre convention, employée par certains outils de bases de données. Elle ne relève pas de la RFC 4180 et un analyseur strict ne la comprendra pas : si votre fichier utilise des barres obliques inverses, dites-le explicitement à ce qui le lit.

Le défaut le plus fréquent rencontré en pratique est un fichier écrit par concaténation de chaînes, où une valeur contenant une virgule ou un guillemet égaré n'a jamais été entourée de guillemets. Cela produit des lignes comptant plus de champs que l'en-tête, exactement le genre de chose qu'un validateur signale.

Le BOM, et pourquoi les caractères accentués deviennent du mojibake

Une marque d'ordre des octets (BOM) est un groupe de trois octets invisibles placés tout au début d'un fichier, qui le marque comme de l'UTF-8. Excel sous Windows s'y est historiquement fié : sans BOM, il peut se rabattre sur un ancien encodage régional, et Müller s'affiche alors comme Müller et café comme café.

La contrepartie est que les autres analyseurs ne le retirent pas toujours. Si votre script annonce une première colonne nommée \ufeffid plutôt que id, ou si une recherche sur "id" échoue mystérieusement sur la première colonne seulement, le BOM est le coupable. Lisez le fichier avec l'encodage utf-8-sig et il disparaît.

Règle empirique : incluez le BOM dans les fichiers destinés à être ouverts dans Excel, laissez-le de côté dans ceux destinés à être analysés par un logiciel.

Fins de ligne

Les outils Windows écrivent \r\n à la fin de chaque ligne, les outils Unix écrivent \n. La plupart des analyseurs s'accommodent des deux, mais un découpage naïf sur \n laisse un retour chariot final collé au dernier champ de chaque ligne, ce qui explique qu'une valeur qui ressemble à UK refuse d'être égale à "UK" dans les comparaisons.

Comment savoir quel séparateur un fichier utilise vraiment

Ouvrez le fichier dans un éditeur de texte brut, jamais dans un tableur, car le tableur a déjà fait son pari. Ensuite :

  1. Regardez les deux ou trois premières lignes. Quel caractère candidat (virgule, point-virgule, tabulation, barre verticale) apparaît entre ce qui est manifestement des valeurs de champ ?
  2. Comptez chaque candidat sur plusieurs lignes. Le vrai séparateur donne le même compte à chaque ligne, un de moins que le nombre de colonnes. Un caractère qui apparaît sept fois sur une ligne et deux fois sur la suivante est une donnée, pas une structure.
  3. Faites ce comptage en dehors des sections entre guillemets. Dans "Smith, Ada", la virgule est du contenu, et la compter naïvement est ce qui fait paraître les virgules incohérentes dans un fichier pourtant bel et bien séparé par des virgules.
  4. Méfiez-vous des virgules décimales. Un fichier européen comporte souvent à la fois des séparateurs point-virgule et des virgules à l'intérieur des nombres, comme dans Ada;1.234,56;UK. Si vous ne voyez de virgules qu'entre des chiffres, ce sont des séparateurs décimaux, pas des séparateurs de champs.
  5. Examinez les premiers octets à la recherche d'une ligne sep= ou d'un BOM avant de conclure quoi que ce soit.

Les tabulations méritent une mention particulière. Un fichier séparé par des tabulations est en général ce que l'on peut confier de plus sûr à un tableur, car les tabulations n'apparaissent presque jamais à l'intérieur de vraies valeurs et Excel les gère bien par la voie de l'importation. Le hic, c'est que les tabulations sont invisibles : un fichier où quelqu'un a aligné les colonnes avec des espaces a exactement la même allure et ne s'analyse en rien d'utile.

Une fois le séparateur connu, convertir le fichier est mécanique : lisez-le avec le bon délimiteur et le bon guillemetage, puis réécrivez-le avec celui qu'attend votre destination, ou convertissez-le en JSON pour éviter complètement la question du délimiteur.