Étiquette : PowerQuery

Apprendre le VBA Excel : Summum de la maîtrise d’Excel ?

Apprendre le VBA Excel : Summum de la maîtrise d’Excel ?

Par où commencer pour apprendre le VBA Excel ? Doit-on apprendre le VBA d’Excel ? Que vais-je savoir faire avec cet apprentissage ? Comment choisir la meilleure formation pour apprendre le VBA d’Excel ? Peut-on faire autrement qu’apprendre le VBA Excel ?

Apprendre le VBA d’Excel vous semble être le summum de l’utilisation d’Excel et vous vous posez de nombreuses questions sur les moyens de parvenir à ce sommet.

À travers cet article, je vous propose de répondre à toutes les questions, et d’autres encore, que votre désir d’apprendre le VBA avec Excel a fait émerger.

Qu’est-ce que le VBA ?

Le terme signifie Visual Basic for Application. C’est un langage de programmation extrait de son parent : le Visual Basic.

Apprendre le VBA Excel - Logo VBA
Logo VBA

Un peu d’histoire pour commencer

Le Visual Basic (VB pour les intimes) est un langage de programmation qui a été inventé par Microsoft pour faciliter la création de programmes pour Windows. Ce qui peut paraître naturel et évident de nos jours, ne l’était pas dans les débuts de l’Ordinateur Personnel (mieux connu sous le nom anglais Personnal Comupter = PC).

Les besoins étaient pourtant déjà les mêmes : pouvoir échanger des informations entre les applications, intégrer le meilleur de chaque logiciel dans un autre (le traitement d’une image au sein d’un tableur par exemple).

C’est ici que les fameux acronymes que nous avons souvent entendus sont apparus :

  • OLE : Object Linking and Embedding ou en bon français “Liens et inclusion des objets” – Permet d’intégrer des morceaux d’une application au sein d’une autre.
  • COM : Component Object Model soit “Modèle objet des composants” – Décrit les objets utilisables ainsi que les modalités d’utilisation.
  • DCOM : Distributed COM ou encore “Modèle objet des composants distribués” – Identique à COM mais à travers un réseau informatique.

Tout ceci est-il véritablement utile pour apprendre le VBA avec Excel ? Personnellement, je le pense. À moins que vous ne soyez un génie qui réussit tout du premier coup, vous verrez des messages d’erreurs apparaître lors de vos débuts. Et je trouve que rien n’est plus frustrant que de ne pas savoir à quoi cela fait référence.

VBA - Erreur OLE
VBA – Erreur OLE

Et le VBA Excel alors ? Pourquoi ce “A” en plus ?

VBA ou VB, les deux permettent de programmer les mêmes choses. Ce n’est pas au niveau des possibilités que se situent les différences. Il faut aller les chercher ailleurs. Il y en a 4.

Le contexte d’exécution

Le VBA est du code qui nécessite une application telle qu’Excel, Word ou PowerPoint (ou d’autres) pour s’exécuter. Le code VB permet de créer des programmes qui se lance directement à partir de Windows.

La compatibilité de votre VBA Excel entre Windows et Mac

Eh oui ! C’est une chose qui peut surprendre, mais il possible d’exécuter du code écrit en VBA depuis Windows sur un Mac ! Comme le contexte d’exécution est Excel, Microsoft se charge de faire les adaptations nécessaires avec le système sous-jacent.

En revanche, votre programme en VB qui a été compilé pour un environnement Windows ne pourra pas s’exécuter sur un Mac.

La licence d’utilisation

Créer des applications pour Windows n’est pas gratuit ! Il faut payer pour disposer de l’environnement de développement, Visual Studio pour le citer. (Même s’il faut l’avouer, ces derniers temps le modèle économique de Microsoft évolue vers un modèle plus ouvert pour le développement des applications.)

Pour le VBA Excel, vous avez déjà payé car vous possédez déjà une licence d’Excel.

La performance du code

Le code VBA Excel s’exécute au sein d’Excel. Par principe, il reste moins de ressources utilisables car Excel est en cours d’exécution. De plus, lorsqu’on développe une application, on fait ce qu’on nomme une compilation du code.

Cette dernière action traduit le code écrit dans un langage machine, l’Intermediate Language (IL, pour Microsoft, il s’appelle le MSIL). Ainsi, en étant compilé, un programme développé en VB sera bien plus performant que le même code en VBA.

Ce tableau extrait de cet article de blog montre des différences de vitesse de traitement :

Execution Time (Seconds)
Excel VBA114
Visual Studio 2010 (w/o Parallel Programming)58
Visual Studio 2010 (with Parallel Programming)23

Par où commencer pour apprendre le VBA Excel ?

Point de départ

Depuis le début de cet article, vous n’avez pas encore commencé à apprendre le VBA Excel… En êtes-vous certain ?

Vous savez déjà que :

  1. Il n’y a pas de limitation en termes de capacité de programmation par rapport à un langage comme le VB.
  2. Le code sera moins performant qu’une application développée à part entière.
  3. Votre VBA sera compatible entre Windows et Mac (si vous restez dans les objets Excel).
  4. Et vous savez surtout que vous avez affaire à un langage de programmation !

Apprendre le VBA Excel : En voiture s’il vous plaît !

Je commencerai par mon dernier point ci-dessus. Apprendre le VBA Excel, c’est avant tout apprendre à faire de la programmation comme un développeur en informatique !

Avec tout le respect que je dois à tous mes confrères de la branche, devenir développeur nécessite un véritable investissement pour se former.

Une langue

Comme tous les métiers, celui-ci possède sa propre langue. String, char, bouton radio ne veulent pas dire la même chose dans ce monde… 🙂

Prévoyez ainsi d’apprendre à parler DEUX langues ! Celle du langage de programmation à proprement parler (le VBA) ET celle des développeurs.

Une manière de penser

Le VBA est un langage orienté objet (c’est une langue… rappelez-vous). Là où vous verrez les données de votre métier, un développeur verra un objet avec des attributs. Il va s’abstraire de ce qu’il voit pour le transformer en objets qui interagissent.

Afin de commencer à programmer, vous devrez vous aussi passer par cette étape d’abstraction de votre problème.

Et cela va sans dire, mais c’est mieux en le disant, il faut apprendre la syntaxe du VBA, comment créer des procédures, des fonctions, des objets, des variables, etc.

Une manière de voir le monde

Le monde vu par un développeur n’est qu’un monde dans lequel il y a des entrées et des sorties.

Voir le monde autrement
Voir le monde autrement

Avec la même entrée, vous aurez la même sortie.

Le programme est une usine que vous alimentez avec des données qui va réaliser une suite d’actions automatiquement, toujours dans le même ordre, sans se poser de questions.

Le programme ne connaît que ce qu’on lui donne. Le développeur doit donc s’assurer que le VBA qu’il est en train d’écrire possède toutes les informations nécessaires pour fonctionner dans toutes les situations !

Vu le nombre de correctifs que nous avons à appliquer sur tous nos équipements, tout n’a pas été prévu et construit comme ça…!

D’ailleurs, cela devrait vous interpeler à ce stade que tant de professionnels formés à programmer écrivent tant de programmes avec tant de bugs…

Apprendre le VBA Excel : Et après ?

Maintenant que vous savez comment penser votre programme, il faut mettre en pratique ces connaissances.

Une fois qu’on sait programmer et que l’on connaît un langage, on peut commencer ?

A vrai dire… pas tout à fait.

L’API Culture…

Qu’est-ce que cela encore ?

Rappelez-vous, vous allez apprendre le VBA Excel, pas celui de Word ou PowerPoint. Il est clair qu’un objet cellule d’un tableau Excel n’a pas les mêmes attributs qu’une cellule d’un tableau sur Word…

L’API c’est l’Application Programming Interface (ou Interface de programmation de l’application).

Apprendre-le-VBA-Excel-API

Même si Microsoft est en train de s’ouvrir au monde open source, il ne va pas tout de suite vous permettre d’accéder au code source d’Excel.

A la place, il a mis en place un ensemble de fonctions et objets accessibles par nous. Cet ensemble, c’est l’API.

Une API est structurée et liste tous les objets et fonctions que vous pouvez utiliser.

Ainsi, comme vous comptez manipuler vos feuilles, cellules et autres informations contenues dans votre classeur Excel, vous aurez à apprendre à vous en servir.

Microsoft vous fournit d’ailleurs une documentation complète sur son API d’Excel.

Mais je veux manipuler des objets qui sont hors d’Excel…

Ah… il fallait bien que cela arrive…

Vous souhaitez en plus écrire des fichiers (vous interagissez avec l’API de Windows). Si en plus vous pouviez automatiquement intégrer les données de votre application de compta dans votre classeur…

Aucun problème.

C’est autant d’API que vous aurez à utiliser !

Pour l’exemple, voici l’API de SAP.

Ci-gît la perte de la compatibilité avec l’environnement Mac.

Charles Exceller

Tant que vous étiez dans le cocon d’Excel à n’utiliser que les objets internes, pas de problèmes. Dès que vous sortez de votre bac à sable, vous ne pouvez plus être compatible avec tout le monde.

Créer un fichier sur Windows s’écrit d’une manière, le même sur un Mac se fait différemment.

En plus de manipuler les données, je veux aussi interagir avec l’utilisateur. Comment faire ?

Autre point de passage quand on veut apprendre le VBA Excel : les formulaires, les menus, les listes, etc.

Sur le principe décrit jusqu’ici, c’est totalement en accord. Toutefois, vous aurez une dimension supplémentaire à considérer : les interactions avec l’utilisateur. En termes techniques, on parle d’UI ou de GUI (User Interface ou Graphical User Interface – on parle aussi d’IHM, Interface Homme-Machine).

D’un point de vue programmation, c’est la même chose.

Toutefois, les considérations suivantes s’ajoutent :

  • Les événements
  • La modalité
  • La notion “synchrone / asynchrone

Les événements sont toutes ces interactions que vous aurez avec les objets présentés à l’écran et qui enverront des signaux à votre code. Ce sera à vous de déterminer ceux sur lesquels vous ferez ou non quelque chose.

Par exemple, le clic sur un bouton ou le simple passage de la souris sur le bouton sont deux événements que le système va déclencher. Ce sera à vous de déterminer si vous souhaitez réagir ou non à celui-ci.

Et chaque objet de votre interface graphique peut (et parfois pas) réagir à certains événements.

Apprendre-le-VBA-Excel-Evenements-Bouton
Évènements disponibles pour un bouton

La modalité déterminera si une fenêtre qui s’ouvre au premier plan conserve “la main” tant qu’elle est ouverte ou bien si vous pouvez continuer à interagir avec les autres fenêtres ouvertes.

Enfin, la notion de synchronicité est essentielle à comprendre et maîtriser pour gérer les délais d’attentes lors d’un appel à une API externe par exemple (et si votre application comptable n’était plus joignable au moment précis où vous l’appeler, votre écran va probablement “freezer”).

Que vais-je savoir faire avec cet apprentissage du VBA Excel ?

Arrivé jusqu’ici, vous avez déjà appris beaucoup de choses.

Vous êtes presque devenu un développeur. Le presque, c’est par respect pour ceux qui mettent plusieurs années d’études et d’années d’expérience pour se dire développeur.

Vous DEVREZ devenir un développeur pour bien faire les choses. Sinon, des erreurs, des dysfonctionnements, des erreurs inattendues seront sur votre route en permanence.

N’a-t-on pas juste oublié une chose ? Nous sommes partis d’Excel et vous devenez développeur…

Pourtant, le terme est très clair sur Excel, le ruban qui vous donne accès au VBA s’appelle “Développeur”.

Avez-vous pris la bonne voie ? Fallait-il apprendre le VBA Excel ?

Apprendre-le-VBA-Excel-Question
Véritable question à se poser !

Tout ça pour ça ?! Tout cet article pour laisser entendre qu’il ne fallait pas le faire ?

Je crois que c’était nécessaire oui. Car oui, je vous le dis, n’apprenez pas le VBA ! Vous faites partie du passé avec cette technologie.

C’est un peu le problème avec les applications qui ont une histoire. Et quelle histoire, Excel 1.0 est sorti en 1985. Cela fait donc 35 ans ! Et en informatique, avoir 35 ans, c’est faire partie de la préhistoire…

Une génération entière a appris Excel à cette époque. La deuxième génération (à partir de 2005), a commencé avec la version Excel 2007 qui a profondément changé le format interne des fichiers. Les fameux .xlsx ont plus de 13 ans, et pourtant les dangereux fichiers Excel au format .xls sont partout !

À la première génération, j’aurais dit de faire avec ce qui était à la pointe du moment, mais aujourd’hui, VBA n’est plus à la pointe !

Quelle est la bonne manière de faire alors ? Que dois-je apprendre si je NE DOIS PAS apprendre le VBA Excel ?

Tout d’abord, pour les développements un peu plus sérieux, Microsoft vous permet de passer à un langage un peu plus sérieux : Le .Net.

Attention, là, vous devenez véritablement un informaticien. Vous quittez presque le bateau Excel pour changer de quai.

Alors que le VBA est un environnement de développement embarqué dans Excel, là, vous partez d’un environnement de développement pour aller vers Excel. Il s’agit de VSTA : Visual Studio Tools for Application.

Certains s’y sont mis tout de suite à la sortie (j’imagine avec un peu de sponsoring de la part de Microsoft… 🙂 ), et pas des moindres. En effet, il s’agit de Dassault Systems qui adopte VSTA en 2005 !!!

Cette voie n’est pas pour tout le monde

Je ne crois pas que ceux qui veulent véritablement progresser dans leur utilisation d’Excel doivent aller vers un langage de développement. Le développement est un métier différent.

Il est évident que, comme tous les logiciels au monde, Excel fournit des fonctionnalités et que vous finirez par en trouver une qui vous manque.

Si vous êtes déjà un développeur et que vous devez fournir une application qui s’intègre avec Excel, alors oui, allez vers VSTA !

Les erreurs les plus communément commises avec le VBA

Une fois que le chemin d’apprendre à coder et à développer avec VBA ou VSTA est parcouru, on a oublié plusieurs choses en route.

  1. Est-ce qu’Excel ne possède pas une fonction pour mon besoin ?
  2. Est-ce qu’on ne pourrait pas écrire une formule Excel pour mon besoin ?
  3. Pourquoi ai-je choisi Excel si je dois faire des développements ? Allez relire mes conseils pour bien démarrer votre apprentissage d’Excel, en particulier, en apprenant ce qu’Excel n’est pas !
  4. Programmer des boucles itératives alors qu’Excel sait faire du calcul matriciel
  5. Ne pas avoir un assez bon niveau en Mathématiques
  6. Ne pas connaître Power Query et son grand cousin Power BI
  7. Négliger l’option Access !

Malheureusement pour vous, les francs-tireurs du code à tout va foisonnent sur les forums Excel sur Internet. Et cela donne l’impression que pour être bon avec Excel, il faut apprendre le VBA Excel…

Ils sont généreux de prendre du temps à aider les autres. Mais les aident-ils véritablement ?

Ouvrez-vous aux autres options que le développement VBA

Option 1 : Découvrez Power Query

Depuis 2013, Excel intègre un nouveau moteur : Power Query. Power Query est un ETL : Extract Transform Load.

Ce sont habituellement de gros systèmes dans les entreprises qui permettent de transférer les données d’un système vers un autre. Par exemple, de votre système de paie vers le système de paiement bancaire.

Intégrer des données externes à Excel

Cette technologie est faite pour manipuler de grandes masses de données. D’abord à l’aide du “E : Extract” de ETL, Power Query permet d’intégrer des données externes à Excel.

Apprendre le VBA Excel Decouvrir Power Query
Découvrir Power Query

En regardant de plus près, cela facilite toute la couche de développement d’intégration de données de systèmes tiers.

Adapter et compléter les données

Nous arrivons au “T : Transform” d’ETL.

Lorsque vous avez sélectionné votre source, vous allez pouvoir ouvrir le nouveau capot, Power Query Editor.

Apprendre le VBA Excel Découvrir Power Query Transformer les données
Apprendre le VBA Excel – Découvrir Power Query – Transformer les données

Et à partir de là, un autre monde de traitement des données s’offre à vous. Il dispose également de plusieurs niveaux de langage à apprendre :

Ces langages vont permettre de manipuler vos données en masse et de manière performante.

Utiliser les données préparées dans Excel

Une fois terminée, vous pourrez alors fermer et charger vos données dans Excel sous la forme d’une ou de plusieurs tables.

Il devient désormais possible d’ouvrir Excel, le connecter à des systèmes externes pour importer des données, les modifier à souhait pour les mettre dans un format qui vous convient sans écrire une seule ligne de code !

Votre fichier restera un fichier sans macro et avec l’extension .xlsx (pourquoi je tiens à ce point à cette extension).

Option 2 : Apprenez les fonctions d’Excel

Lorsque Microsoft met à jour Excel, on pense généralement que c’est juste pour vendre une nouvelle licence, que rien ne change vraiment.

Maintenant, je vous mets au défi de programmer la fonction UNIQUE() présente dans la dernière version d’Excel 365 avec la même performance.

Ensuite, découvrez les tableaux dynamiques (rien à voir avec les tableaux croisés dynamiques).

Et si vous voulez réellement progresser dans votre utilisation d’Excel, apprenez à manier les formules matricielles.

Vous pouvez également aller télécharger mon livre PDF qui vous récapitule les 12 fonctions Excel à maîtriser absolument.

Lorsque vous aurez fait véritablement le tour de toutes ces possibilités, alors vous pourrez vous poser la question de développer… quoique… Vous êtes-vous posé la question d’utiliser Access ?

Option 3 : (Re-)Découvrez Access

Finalement, la véritable plateforme que Microsoft vous a mise à disposition pour créer des applications, des formulaires, des règles de saisies, etc. c’est Access.

Apprendre le VBA Excel - Option 3 Access
Option 3 Access

C’est écrit juste ici : Suivi des biens, Base de données de gestion de projets, Gestion des tâches.

Hélas, combien de fois trouverez-vous des demandes d’assistance sur les forums pour recréer ce type d’application de gestion ?

N’apprenez pas le VBA, apprenez Access ! Tout y est :

  1. Formulaires de saisie
  2. Contraintes d’unicité d’un identifiant
  3. Incrément automatique d’un identifiant
  4. Rapports récapitulatifs (on appelle cela un “état” dans Access)

Pourquoi vouloir tout réinventer ? C’est entre vos mains. Au lieu de vous démener à apprendre le VBA Excel, apprenez Access et vous deviendrez un génie qui résout tous les problèmes !

Option 4 : Découvrez ScriptLab et la gestion d’Excel avec du JavaScript

Le JavaScript (JS) est un langage de programmation initialement conçu pour le web et les navigateurs web. Toutefois, sans entrer dans les considérations techniques, il est désormais employé à peu près partout. Excel ne fait pas exception.

Le véritable langage moderne est aujourd’hui JavaScript.

Comment passer à JS sur Excel ?

Première étape : Installer ScriptLab depuis l’AppSource

Apprendre le VBA Excel - Découvrir ScriptLab
Découvrir ScriptLab

Un nouveau ruban fait alors son apparition, c’est “ScriptLab”.

Deuxième étape : Commencer par utiliser les exemples de scripts fournis

Afin de vous aider à apprendre ScriptLab, vous trouverez de nombreux exemples embarqués dans le composant.

Pour y accéder, vous aurez à suivre les étapes suivantes :

Apprendre le VBA Excel - Premiers pas avec ScriptLab
Premiers pas avec ScriptLab

Au passage, pensez à agrandir le panneau qui s’ouvre sur la droite pour avoir une meilleur lisibilité du contenu.

Troisième étape : Suivez le guide de Microsoft

Je ne vais pas réécrire ici la prise en main déjà documentée par Microsoft. Aussi, je vous invite à consulter la documentation fournie sur le site de l’éditeur.

Mon conseil pour apprendre le VBA Excel

Depuis plusieurs années, Microsoft enrichit de manière très importantes les capacités d’Excel. En particulier sa capacité à intégrer des données provenant de systèmes externes.

Pourquoi toutes ces possibilités ? Mon avis est qu’il souhaite détourner les utilisateurs du VBA pour utiliser des fonctionnalités intégrées.

Pourquoi détourner du VBA ? Parce que la programmation est une source majeure de risques pour la sécurité de votre poste de travail et de tout ce qui est connecté au même réseau.

Pour faire une analogie avec le monde qui nous entoure, utiliser les fonctionnalités c’est conduire avec un code de la route. Faire du VBA, c’est conduire sans code de la route. Tout peut très bien se passer, mais les pirates ont bien compris qu’un monde sans contraintes était disponible et ils l’exploitent. (ici, ici ou encore ici)

Apprendre le VBA Excel pour se rendre compte que les fichiers avec macros seront bloqués en envoi et en réception dans quelques temps serait bien dommage, non ? Microsoft lui-même le restreint fortement par défaut dans son environnement Microsoft 365.

Finalement, si vous souhaitez dépasser les limites qu’Excel vous pose, apprenez à développer une application à partir de zéro !

Mettez en place un environnement de développement avec sa base de données et toutes les fonctionnalités dont vous avez besoin. Vous pourrez décider de faire une app pour votre smartphone ou une application web, mais au moins ce sera clair !

Et après ?

Vous souhaitez en savoir plus sur les fonctions Excel ? Restez informé sur l’ouverture des sessions de formations.

Vous avez un problème à résoudre et ne savez pas comment l’aborder ? Contactez-moi via la page de contact.

Vous avec un avis sur cet article ? Utilisez la partie des commentaires et parlons-en !

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.

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.
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 !

>