Connecter une base de données MySQL à Excel 2013

I. Présentation

En entreprise, il est fréquent de trouver des bases de données gérées par le système MySQL. De la même manière, il est fréquent de trouver la suite Microsoft Office installée sur les postes clients. Alors pourquoi ne pas exploiter la puissance de ces deux produits en les faisant travailler ensemble ?

Dans ce tutoriel, nous allons établir une connexion entre un document Excel et une base de données MySQL. Ainsi, vos données se retrouveront dans le tableur et vous pourrez manipuler les données comme bon vous semble (filtre, tri, graphique, tableaux croisés dynamiques, etc...).

Autrement dit, MySQL fournira les données et Excel les manipulera, vous bénéficierez alors de la puissance d'Excel pour jouer avec vos précieuses données.

Pour ceux qui préfèrent suivre le cours au format vidéo :

II. Comment ça marche ?

Le serveur MySQL d'un côté, l'application Excel de l'autre, il faudra établir la connexion entre les deux grâce à une connexion ODBC. Pour cela, il sera nécessaire d'installer un driver spécifique à MySQL sur la machine où l'on souhaite créer la liaison.

ODBC (Open DataBase Connectivity) : Système de connexion intégré à Windows qui permet la communication entre des applications clientes et des systèmes de gestion de base de données (SGBD).

ODBC est donc au coeur des communications entre Excel et MySQL.

III. Base de données de démonstration

Dans le cadre de ce tutoriel, j'ai créé une base de données nommée "contact" et contenant une seule table appelée "personnes".

Dans cette table, on trouve trois champs : ID, Identification et Age. L'objectif est d'identifier rapidement des personnes avec un numéro unique, un prénom et l'âge.

Ce sont ces données que nous allons chercher à importer dans Excel grâce à la connexion ODBC.

Voici un aperçu de la base de données dans PhpMyAdmin :

excelsql25

Pour ma part, j'utilise WAMP sur une machine Windows, c'est également sur cette machine que j'utiliserais Excel. Autrement dit, tout est en local.

IV. Création d'un utilisateur dédié

Nous n'allons pas connecter Excel à MySQL en utilisant l'utilisateur "root" (ne me dite pas que vous y avez pensé !), c'est une précaution importante à prendre en matière de sécurité. Un utilisateur spécifique sera créé en lui donnant uniquement des droits de lecture sur la base de données "contact".

Sinon, imaginez si l'utilisateur à trop de droits... A partir du fichier Excel, on pourrait en s'appuyant sur la connexion établie, modifier la base de données... Sachant que ce serait qu'une partie de plaisir grâce à un script, aux macros, etc.

D'où l'importance de sécuriser la connexion... Pour créer un nouvel utilisateur, accédez à PhpMyAdmin. Sur la gauche, sélectionnez votre base de données, par exemple "contact" dans mon exemple.

Cliquez sur l'onglet "Privilèges" et sur "Ajouter un utilisateur".

excelsql26

Il faut ensuite remplir le formulaire. Indiquez en nom d'utilisateur "excel" et attribuez-lui un mot de passe (complexe). Quant à la ligne client, je choisis localhost.

Vous devez désactiver l'option "Donner tous les privilèges sur la base de données contact" car nous ne souhaitons pas donner tous les droits ! Contentez-vous de sélectionner "SELECT" dans la liste des autorisations pour l'utilisateur ait seulement l'autorisation de lire les données de la base.

Enfin, validez la création de l'utilisateur.

excelsql27

Note : Dans les paramètres de connexion ODBC, vous pouvez activer la connexion SSL à condition que votre serveur MySQL est paramétré pour cela.

 

V. Installer le driver MySQL Connector ODBC

Il faut se rendre sur le site de MySQL et disposer d'un compte Oracle (gratuit) pour télécharger le driver du connecteur.

Télécharger MySQL Connector ODBC

Prenez la version que vous souhaitez, 32 ou 64 bits mais soyez en adéquation avec votre OS et votre version d'Excel.

L'installation se déroule très simplement, pensez à choisir la version complète du driver.

excelsql1

VI. Création de la connexion ODBC MySQL

Désormais, nous allons pouvoir créer la connexion ODBC à destination du serveur MySQL. Pour cela, accédez au Panneau de configuration et cliquez sur "Outils d'administration".

Dans la liste, ouvrez "Sources de données ODBC (32 bits)" ou "Sources de données ODBC (64 bits)" selon la version du driver installé.

excelsql3

Un assistant s'ouvre... Dans l'onglet "Sources de données utilisateur" cliquez sur "Ajouter" sur la droite.

excelsql4

Dans la liste, vous devez trouver le driver ODBC pour MySQL que nous venons d'installer. Préférez l'utilisation de la version Unicode pour les applications plus récentes. Cliquez sur "Terminer".

excelsql5

Ensuite, on doit nommer la source de données (exemple : Mon App) et remplir les paramètres de connexion au serveur MySQL. Indiquez l'adresse IP ou le nom pour joindre le serveur, pour ma part c'est du local donc localhost. Le port 3306 n'a pas besoin d'être changé sauf si vous l'avez modifié dans la configuration de MySQL.

Enfin, indiquez l'utilisateur "excel" et son mot de passe, puis cliquez "Test" pour tester la connexion.

excelsql6
Configuration de MySQL Connector ODBC

 

Vous devriez obtenir un message de validation :

excelsql7
Test de la connexion ODBC

 

Terminez en sélectionnant votre base de données dans la liste "Database", pour ma part il s'agit de "contact". On peut voir que notre source de données apparaît dans la liste enregistrée :

excelsql8

VII. Connecter Excel à MySQL

La connexion ODBC est prête, le serveur MySQL également, il ne reste plus qu'à connecter Excel. Ouvrez un nouveau document Excel, et dans l'onglet "Données" cliquez sur "Connexion" :

excelsql9

Cliquez sur le bouton "Ajouter".

excelsql10

Aucune liaison existante n'est intégrée dans ce document Excel, il faut donc cliquer sur "Rechercher".

excelsql11

Cliquez sur "Connexion à une nouvelle source de données" pour créer un nouveau fichier de connexion (.odc).

excelsql12

Sélectionnez "DSN ODBC" et cliquez sur "Suivant".

DSN (Data Source Name) : Permet de déclarer la source de données qui sera accessible via ODBC.

excelsql13

On sélectionne la source de données "Mon App" que nous avions créé précédemment. Cliquez sur "Suivant".

excelsql14

Décochez la case "Connexion à une table spécifique" pour englober toute la base de données et cliquez sur "Suivant" pour poursuivre.

excelsql28

Enfin, validez en cliquant sur "Terminer". Il faut savoir qu'un fichier "contact.odc" sera créé sur la machine, c'est le fichier de configuration de la connexion. Il peut être exporté et copié sur une autre machine où vous souhaitez utiliser cette connexion, cela évite de refaire une partie de la configuration.

excelsql29

Maintenant, en cliquant sur "Connexions existantes" on va ouvrir notre connexion. Dans la liste, sélectionnez "contact" et cliquez sur "Ouvrir".

excelsql30
Ajout d'une connexion ODBC existante dans Excel

 

Validez l'insertion en forme de tableau en cliquant sur "OK".

excelsql18

On peut voir que les données sont bien présentent dans Excel et qu'elles sont identiques à celles de la BDD :

excelsql24

La connexion entre Excel et la base de données MySQL est désormais opérationnelle !

XIII. Modifier la requête SQL

Pour finir, nous allons voir comment modifier la requête SQL effectuée dans la base. Par défaut, tous les champs sont sélectionnés, on peut alors vouloir en sélectionner uniquement certains. Cliquez sur le bouton "Connexions".

Par exemple, plutôt que d'afficher l'ID, l'identification et l'âge, on pourrait sélectionner uniquement l'identification et l'âge.

excelsql20

Cliquez sur "Propriétés".

excelsql31

Dans l'onglet "Définition", il faudra modifier la requête SQL qui se trouve dans la zone "Texte de la commande" comme ceci (sur la copie d'écran la requête par défaut est affichée) :

excelsql32
Modifier la requête SQL effectuée

 

Après avoir cliqué sur "OK", vous devrez cliquer sur "Oui" pour mettre à jour les informations de connexion.

excelsql33

Vous revoilà dans Excel, on remarque bien qu'une colonne a disparue :

excelsql34

On peut très bien imaginer limiter le nombre de résultats retournés par la requête SQL, indiquer un ordre de tri, etc... A vous de voir, sachant que l'on peut aussi faire tout ça directement dans Excel. Enfin, pour actualiser les données, il suffit d'appuyer sur le bouton adéquat :

excelsql35

Pour pouvoir mettre à jour les données, il faut bien entendu être en ligne pour pouvoir contacter le serveur MySQL. Par ailleurs, une fois le document Excel enregistré avec des données, vous pouvez le consulter sur n'importe quel poste (en ligne ou hors ligne).

Partagez cet article Partager sur Twitter Partager sur Facebook Partager sur Linkedin Partager sur Google+ Envoyer par mail

Florian Burnel

Co-Fondateur d’IT-Connect, je souhaite partager mes connaissances et expériences avec vous, et comme la veille techno’ est importante je partage aussi des actus.

florian a publié 1614 articles sur IT-Connect.See all posts by florian

14 réactions sur “Connecter une base de données MySQL à Excel 2013

  • 19/03/2015 à 12:41
    Permalink

    C’est très intéressant… et à la fois complètement ahurissant !

    C’est très intéressant, parce que ça ouvre pas mal de perspectives nouvelles sur la gestion et l’analyse à distance de ses données utilisateur.

    Ahurissant, parce qu’on se rend compte qu’une feuille de calcul fonctionne comme une base de données, et qu’on pourrait presque imaginer utiliser le même logiciel sur son serveur et sur son ordinateur, pour effectuer les tâches de gestion et d’analyse de façon bien séparée…

    Ça ne serait pas d’autant plus simple, non ?

    Répondre
    • 19/03/2015 à 23:46
      Permalink

      Bonjour Philippe,

      L’intérêt c’est surtout d’aller piocher des données dans des applis plus importante, genre une appli web (ce qui n’est pas simulable dans Excel, quoi que ça dépend ce que fait l’appli…). Dans le cas de l’exemple du tutoriel, c’est sur que ce n’est pas utile de monter une base de données pour trois champs et une table, mais c’était pour montrer le principe de fonctionnement.

      La base de données MySQL contient les données, l’interface web rend accessible l’application à plusieurs personnes simultanément, et Excel permet d’extraire et d’analyser certaines données, notamment pour faire un peu de reporting 🙂

      Bonne nuit !
      Florian

      Répondre
      • 20/03/2015 à 01:21
        Permalink

        Bien sûr, et d’ailleurs c’est dans l’autre sens que j’imaginais les choses. Par exemple, d’installer un serveur SQL chez soi pour tenir sa comptabilité… Mais ça revient au même dans l’autre sens pour ce qui concerne l’absurdité du truc.

        Une fois alors qu’on m’avais mis de force devant Access, je me suis demandé à quoi ça pouvait vraiment servir, par rapport à Excel qui est nettement plus abordable.

        Mais c’est sans doute idiot également. Ce sont en réalité 4 produits logiciels qui font sensiblement la même chose, mais à des degrés différents parce qu’on les a packagés pour des utilisations plus ou moins poussées je présume.

        Répondre
  • 16/04/2015 à 00:10
    Permalink

    Bonjour Florian,
    J’ai réussi à connecter la base de donnée à mon fichier excel, cependant je cherche à insérer des données via mon excel (une nouvelle ligne). Ma question est de savoir s’il est possible d’ajouter des données via un fichier excel afin qu’elles s’ajoutent automatiquement dans phpMyAdmin ?

    Merci pour ce tuto et pour ta future réponse.

    Répondre
  • 28/05/2015 à 19:51
    Permalink

    Bravo et merci pour ce didacticiel très complet et que j’ai appliqué avec succès.

    J’ai juste eu un problème en téléchargeant d’abord le driver 64 bits, pensant que mon Excel 2013 sous Windows 8.1 était en 64 bits.

    Mais non, ça ne marchait pas, j’ai du installer la version 32 bits et là nickel.

    Répondre
  • 20/06/2015 à 20:05
    Permalink

    Bonjour Florian,

    Merci pour cet article, mais est ce qu’il est possible de modifier le contenu de la base de données à partir du fichier Excel dont le contenu change régulièrement ?

    Répondre
    • 21/06/2015 à 19:09
      Permalink

      Bonjour,

      Oui ça doit être possible, par contre il faut un utilisateur qui dispose des droits d’écriture sur la base de données. De plus, il y a surement un bouton pour envoyer les modifications effectuées dans Excel au sein de MySQL pour écrire les modifications (à tester).

      Florian

      Répondre
  • 06/10/2015 à 19:39
    Permalink

    slt!
    Le connecteur mysql «  »mysqlconnect/odbc ne veut pas installer sur mon ordi c’est quoi le prob?
    merci

    Répondre
  • 16/01/2016 à 17:54
    Permalink

    Un très très grand merci.

    Ton article est extrêmement complet et très bien expliqué, et m’a permis sans difficultés de connecter ma base de données!
    PS: Bien que mon système soit 64bit, j’ai été obligé d’installer le driver 32 bit (le driver 64 bit ne semble pas fonctionner)

    Si tu as un article sur comment se connecter via VBA, ça m’intéresse également!
    (pour modifier la table)

    Répondre
  • 13/04/2016 à 22:53
    Permalink

    Bonsoir merci pour la vidéo c intéressant
    j’ai un problème espérons que vous pouvez m’aidé.
    après une requête sql et des condition de teste et calcule j’ai plusieurs informations et je veux que c information s’affiche dans une fichier excel.
    mais j’arrive pas a réussir.
    base de donnée easyphp
    et langage de programmation php

    Répondre
  • 27/03/2017 à 11:01
    Permalink

    L’application MySQL For Excel, gratuitement téléchargeable chez Oracle, permet beaucoup plus de souplesse, par exempale en permattant d’éditer les données sous Excel et de les renvoyer vers la base, tout en étant bien plus simple à configurer.

    Répondre

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *