Utiliser un fichier CSV avec Excel

Utiliser un fichier CSV avec Excel

Le fichier CSV et Excel sont des amis mais parfois, ils ont du mal à s’entendre. Je vous propose dans cet article de rétablir la meilleure entente entre les deux. Commençons par la définition : un fichier CSV signifie « Comma Separated Values », des valeurs séparées par des virgules. Or, parfois, le fichier CSV contient des valeurs séparées par … des points-virgules !

Bref histoire du format de fichier CSV

Tout d’abord, c’est un format qui est proposé comme format standard pour Internet par l’IETF (Internet Engineering Task Force) à travers la RFC 4180 puis mise à jour avec la RFC 7111.

Donc, si c’est un standard, tout le monde devrait pouvoir lire et écrire de la même façon, non ? Malheureusement, entre un standard déclaré et son application, il y a souvent des écarts importants… Et vous pouvez le constater à chaque fois que vous ouvrez un fichier CSV avec Excel, c’est toujours une surprise. Enfin, avant d’avoir lu cet article… car après, vous maîtriserez parfaitement la question !

Prérequis pour lire et écrire un fichier CSV

Les fichiers CSV sont des fichiers texte. Premièrement, ce sont des fichiers texte que n’importe quel éditeur de fichiers peut lire et écrire. Ils n’ont pas besoin d’un outil propriétaire (payant) pour être lus. Deuxièmement, l’idée de ces fichiers est de pouvoir échanger des données entre deux organisations (cela peut être deux personnes, deux programmes, un mélange des deux).

L’intérêt d’un standard, c’est que chacun sait à quoi s’attendre et donc comment lire un fichier reçu. Or, plusieurs problèmes viennent troubler l’ordre a été établi. Nous allons les passer en revue.

Le format des nombres

Dans le monde anglo-saxon, le séparateur de décimales est le point, alors qu’en Europe (sauf pour les britanniques), c’est la virgule. Cela va donc commencer à poser un problème pour un fichier CSV qui veut dire « Comma Separated Values »… vos valeurs à virgule risquent de se faire découper en deux !

Il serait donc bienvenu de disposer d’un autre séparateur que la virgule…

De plus, la virgule est utilisée par les anglo-saxons comme un séparateur de milliers. Cela ne facilite pas la lecture des valeurs.

Le format des dates

Pour continuer à simplifier la situation (!), la manière dont les dates sont écrites dans un fichier risque également de subir une interprétation. La même différence entre le monde anglo-saxon et les autres existe pour le format des dates.

Ainsi, une date inscrite 8/12/11 correspond-elle au 8 décembre 2011 ou au 12 août 2011 ? Rien n’empêche d’imaginer le 11 décembre 2008… Il est donc vital de comprendre comment les dates sont inscrites dans un fichier pour pouvoir les interpréter de la bonne manière !

Le format du texte

Pour des raisons techniques, les données de type texte apportent leur lot de complexité également. En dehors de l’encodage dont je vous parle plus loin, il faut traiter le problème du caractère apostrophe ‘ et du guillemet « .

Le premier (apostrophe) pose des problèmes à nos amis les développeurs. En effet, les apostrophes sont utilisées dans les appels aux bases de données et génèrent donc des difficultés. Le moyen de contourner ce problème consiste à placer des guillemets  » autour du texte contenant une apostrophe ‘.

Là aussi, pour des raisons que je ne détaillerai pas, c’est parfois l’inverse qui est utilisé ! Des guillemets à la place d’une apostrophe et vice-versa.

Les séparateurs de valeurs

Normalement, le nom du fichier indique clairement que les valeurs de notre fichier CSV devraient être séparées par des virgules. Faisons un petit récapitulatif de la situation :

  1. Les nombres décimaux utilisent parfois des virgules, parfois des points
  2. Les dates ont des ordres variables d’affichage du jour, du mois et de l’année
  3. Les textes sont parfois encerclés de guillemets, parfois d’apostrophes
  4. Les textes voient parfois leurs apostrophes remplacées par des guillemets

Ces raisons font que différentes méthodes existent pour séparer les valeurs dans un fichier. Il s’agit parfois de point-virgule, parfois le caractère tabulation, parfois ce sera un autre caractère (#, |, …)… l’objectif étant d’éviter qu’il y ait des collusions entre ce caractère et les données.

Les personnes qui ont cet article ont aussi lu :  Apprendre Power Query : Pourquoi Excel est limité !

Et pour terminer, je vous laisse le meilleur pour le prochain paragraphe : l’encodage du fichier !

L’encodage du fichier

Comme la situation était déjà simple, le monde des technologies a ajouté un autre problème… l’encodage.

Je n’aime pas devoir rentrer dans les considérations si techniques. Mais, dans le cas présent, c’est essentiel pour pouvoir comprendre pourquoi, dans certains cas, un import de fichier CSV dans Excel ne fonctionne pas…

Lorsque vous écrivez un fichier texte sur votre ordinateur, le programme d’édition utilise un certain ensemble de caractères (un charset – contraction de « characters » et « set »). Ainsi, lorsque j’utilise le 8e caractère de l’ensemble, cela correspond à une lettre, un chiffre ou un caractère spécial en particulier. Il se trouve que la technologie s’est d’abord développée dans le monde anglo-saxon. Ces derniers n’ont pas prévu dès le départ que le système développé allait autant s’étendre. Ils n’ont donc pas prévu d’intégrer tous les alphabets ou idéogrammes du monde.

L’internationalisation des échanges a vu naître de plus en plus de charsets différents ! Et biensûr, ils ne sont pas compatibles entre eux ! Vous devez donc savoir avec qui vous traitez…

Vos paramètres régionaux

Eh oui, les paramètres système de votre ordinateur vont avoir un impact sur la manière dont un fichier CSV sera lu par Excel. Il est fréquent de voir ce problème lors d’échange de fichiers entre collègues de la même organisation mais ne travaillant pas dans la même langue ou la même région du monde (et ayant donc des configurations d’ordinateur différentes).

Ces paramètres se trouvent dans le panneau de configuration (ancienne version d’affichage – même sur Windows 10 !). Vous trouverez les paramètres qui sont les vôtres pour votre ordinateur.

Vous pouvez également aller explorer les autres onglets (en 4) comme présenté ci-dessous.

Fichier CSV Excel - Paramètres régionaux
Fichier CSV Excel – Paramètres régionaux

Importer un fichier CSV avec Excel

Même si l’explorateur de fichiers associe les fichiers .csv avec Excel, je ne vous recommande pas d’ouvrir directement un fichier csv (en double-cliquant dessus). La probabilité que le fichier ne s’ouvre pas convenablement est très (trop) élevée. Au final, vous risquez plus de frustrations que de gagner réellement du temps.

Mon meilleur ami pour ouvrir les fichiers CSV est Notepad++. C’est un éditeur de texte très puissant et totalement libre d’utilisation. Il permet d’ouvrir les fichiers texte volumineux sans planter ! Et surtout, il sait :

  1. Reconnaître automatiquement l’encodage des fichiers
  2. Convertir un encodage très simplement
  3. Remplacer des caractères par un autre à toute vitesse

Méthode n°1 : Préparer votre fichier avec Notepad++ avant d’ouvrir votre fichier CSV dans Excel

Dans mon tuto sur comment réussir son premier tableau croisé dynamique, j’utilise un exemple de données provenant du site du gouvernement français : https://www.data.gouv.fr/fr/datasets/r/cc16163c-aca0-4977-97da-8ce592f78de1.

Ce fichier CSV n’a pas un encodage qu’Excel comprend immédiatement (vous pouvez tester, vous verrez !).

Alors que lorsque vous l’ouvrez avec Notepad++, cela fonctionnera parfaitement. Vous pourrez alors aller dans le menu « Encodage » (en 1) et visualiser l’encodage actuel (en 2).

Ensuite, vous pourrez convertir ce fichier dans un encodage qui sera immédiatement reconnu par Excel, à savoir ANSI. Cet encodage est celui par défaut pour Windows.

Fichier CSV Excel - Changer encodage Notepad++
Fichier CSV Excel – Changer encodage Notepad++

Vous pourrez ensuite travailler depuis Excel pour terminer la conversion. Mais au moins, à ce stade, les caractères spéciaux du type é ne seront plus présents !

Avant de quitter Notepad++, vous devez:

  1. Vérifier que l’encodage du fichier a bien changé (cf. image ci-dessous)
  2. Enregistrer votre fichier avant de fermer ! (si la disquette en haut à gauche est rouge, elle montre que l’enregistrement n’a pas été fait)
Fichier CSV Excel - ANSI
Fichier CSV Excel – Encodé ANSI

Méthode n°2 : Utiliser l’import de données externes d’un fichier CSV dans Excel

Étape n°1 : Importer le fichier CSV

Il y a un avantage à utiliser cette technique : Excel peut cette fois détecter l’encodage correctement !

Fichier CSV Excel - Importer fichier texte
Fichier CSV Excel – Importer fichier texte

Vous pouvez ensuite sélectionner votre fichier CSV à importer. L’écran suivant apparaît alors.

  1. Type de données d’origine
    • Délimité : Vous disposez de caractères de séparation, par défaut Excel reconnaît les virgules.
    • Largeur fixe : Parfois, les fichiers CSV sont construits avec le même nombre de caractères à chaque ligne. Les lignes qui ont plus ou moins de caractères sont comblées avec des espaces.
  2. Origine du fichier.
    • C’est l’encodage. Ici, comme avec Notepad++, Excel reconnaît que le contenu est en UTF-8 (et non ANSI). Les caractères spéciaux n’apparaissent pas.
    • Toutefois, il est possible qu’Excel se trompe. Vous devez alors choisir par vous-même le bon encodage dans la liste déroulante.
  3. Commencer l’importation à la ligne xx.
    • Il est possible de ne pas importer la ligne d’en-tête par exemple. Ou parfois, les fichiers CSV ont des lignes de description qui ne sont pas utiles à l’importation.
  4. Mes données ont des en-têtes.
    • Cette case permet d’indiquer à Excel si la première ligne représente les noms des colonnes.
  5. Vous avez une prévisualisation de vos données.
    • Cela permet de vérifier si les paramètres sont adaptés.
Les personnes qui ont cet article ont aussi lu :  Les 4,5 apprentissages qu'un cours sur Excel devrait vous enseigner
Assistant importation données CSV - Étape 1
Assistant importation données CSV – Étape 1

Vous pouvez alors cliquer sur le bouton « Suivant > » et continuer la configuration de votre import.

Étape n°2 : Configurer les séparateurs de votre fichier CSV

Assistant importation données CSV - Étape 2
Assistant importation données CSV – Étape 2

À ce stade, par défaut, la seule case cochée est « Tabulations ». Vous aurez certainement besoin de cocher soit « Virgule » soit « Point-virgule ». Les autres options sont explicites. Dès que vous aurez choisi le bon séparateur, l’aperçu se met à jour. Vous pourrez évaluer la pertinence de votre choix.

Vous pouvez alors passer à l’étape 3.

Étape n°3 : Configurer le choix et le format de vos colonnes

Assistant importation données CSV - Étape 3
Assistant importation données CSV – Étape 3

À ce moment, 3 parties très, très, très importantes se présentent à vous (j’ai déjà dit que c’était important ?)

  1. Vous pouvez déterminer le type de chacune des colonnes importées à ce moment précis.
    • Vous pouvez notamment adapter le format des dates. JMA signifie Jour Mois Année. Dans le menu déroulant, vous trouverez les 6 combinaisons possibles. À vous de choisir la bonne !
  2. Vous pouvez déterminer les séparateurs de décimales et de milliers (en cliquant sur « Avancé… »).
  3. Vous pouvez sélectionner chaque colonne les unes après les autres pour déterminer leur type.

N’oubliez surtout pas l’option « Colonne non distribuée ». Cette option permet de ne pas importer les données de la colonne en question. Cela peut s’avérer très utile de n’importer que les données nécessaires.

Enfin, lorsque vous avez terminé, vous pouvez cliquer sur le bouton « Terminer » et utiliser vos données dans Excel.

Considérations (avantages/inconvénients) de cette méthode

Selon les cas, un avantage pour les uns peut être un inconvénient pour lesn autres, je préfère donc lister les considérations à avoir avec cette méthode.

  • Votre fichier CSV reste intact. Vous n’importez que les données.
  • Vous pouvez mettre à jour votre fichier CSV et Excel pourra actualiser l’import des données (en cliquant sur Actualiser). C’est à dire que si le nombre de lignes varie, Excel s’adaptera.
  • Si vous déplacez le fichier CSV ou le fichier Excel, vous aurez un message d’erreur à chaque rafraîchissement des données.
  • Plusieurs personnes peuvent travailler à partir du même fichier CSV.

Méthode n°3 : Récupérer et transformer votre fichier CSV dans Excel

Pour utiliser cette méthode, PowerQuery est nécessaire. Ce composant est présent nativement à partir d’Excel 2016. Pour les Excel 2013 ou Excel 2010 Professionnal Plus (+Software Assurance), Microsoft propose de télécharger le composant depuis son site.

Étape n°1 : Débuter l’import

C’est certainement la méthode la plus complète, et par conséquent, la plus complexe également.

Importer un fichier CSV dans Excel - Récupérer et transformer
Importer un fichier CSV dans Excel – Récupérer et transformer

L’écran suivant apparaît avec les options suivantes :

  1. Origine du fichier
    • Comme nous l’avons déjà vu, c’est l’encodage.
    • Ce qui est surprenant, c’est que l’interface de PowerQuery ne reconnaît pas le bon encodage automatiquement !
    • C’est donc à vous de sélectionner le bon encodage ! La liste est longue, mais je vous recommande de tester parmi les encodages les plus fréquemment utilisés que sont :
      • 1252 – Europe de l’ouest (Windows)
      • 1200 – Unicode
      • 65001 – Unicode UTF-8
      • >>Ne me demandez pas pourquoi, les valeurs ne sont pas triées selon les valeurs numériques, mais selon le nom de l’encodage…
  2. Délimiteur
    • C’est le séparateur qui est utilisé dans votre fichier.

À ce stade, au lieu de tout de suite cliquer sur le bouton « Charger », je vous invite à découvrir un peu plus PowerQuery en cliquant sur « Modifier ».

Fichier CSV Excel - Configuration PowerQuery
Fichier CSV Excel – Configuration PowerQuery

Étape n°2 : Modifier la requête d’importation

Dès que vous avez sélectionné le bon encodage et cliqué sur « Modifier », vous vous retrouvez face à l’affichage suivant.

Fichier CSV Excel - Interface PowerQuery
Fichier CSV Excel – Interface PowerQuery

Je pourrais vous détailler à ce stade toute l’interface, cela serait un peu long. Toutefois, je peux vous informer sur les quelques possibilités que vous pourrez explorer. Quelques exemples :

  • Changer l’ordre des colonnes ou en supprimer
  • Filtrer automatiquement des lignes, autrement dit ne pas les importer
  • Calculer de nouvelles colonnes sur la base des autres colonnes
  • Changer les types des données
  • etc.

Lorsque vous aurez terminé vos paramétrages, vous n’aurez qu’à cliquer sur « Enregistrer et charger » pour que les données se chargent sur votre feuille Excel.

Considérations (avantages/inconvénients) de cette méthode

C’est sans conteste la méthode la plus complète pour importer des données CSV dans Excel. C’est également la méthode la plus complexe. Selon vos besoins, vous pourrez déterminer la solution la plus adaptée.

Comme précédemment, vous aurez les considérations suivantes :

  • Votre fichier CSV reste intact. Vous n’importez que les données.
  • Vous pouvez mettre à jour votre fichier CSV et Excel pourra actualiser l’import des données (en cliquant sur Actualiser). C’est-à-dire que si le nombre de lignes varie, Excel s’adaptera.
  • Si vous déplacez le fichier CSV ou le fichier Excel, vous aurez un message d’erreur à chaque rafraîchissement des données.
  • Plusieurs personnes peuvent travailler à partir du même fichier CSV.

Dernière étape

Avez-vous encore des questions ? Est-ce que cet article vous a plu ? J’attends votre feed-back dans les commentaires !

Merci !

Partager l'article
>