Connecter une base de données MySQL à Excel 2013
Sommaire
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 :
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".
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.
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.
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é.
Un assistant s'ouvre... Dans l'onglet "Sources de données utilisateur" cliquez sur "Ajouter" sur la droite.
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".
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.
Vous devriez obtenir un message de validation :
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 :
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" :
Cliquez sur le bouton "Ajouter".
Aucune liaison existante n'est intégrée dans ce document Excel, il faut donc cliquer sur "Rechercher".
Cliquez sur "Connexion à une nouvelle source de données" pour créer un nouveau fichier de connexion (.odc).
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.
On sélectionne la source de données "Mon App" que nous avions créé précédemment. Cliquez sur "Suivant".
Décochez la case "Connexion à une table spécifique" pour englober toute la base de données et cliquez sur "Suivant" pour poursuivre.
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.
Maintenant, en cliquant sur "Connexions existantes" on va ouvrir notre connexion. Dans la liste, sélectionnez "contact" et cliquez sur "Ouvrir".
Validez l'insertion en forme de tableau en cliquant sur "OK".
On peut voir que les données sont bien présentent dans Excel et qu'elles sont identiques à celles de la BDD :
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.
Cliquez sur "Propriétés".
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) :
Après avoir cliqué sur "OK", vous devrez cliquer sur "Oui" pour mettre à jour les informations de connexion.
Vous revoilà dans Excel, on remarque bien qu'une colonne a disparue :
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 :
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).
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 ?
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
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.
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.
Non ce n’est pas possible…
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.
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 ?
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
slt!
Le connecteur mysql « »mysqlconnect/odbc ne veut pas installer sur mon ordi c’est quoi le prob?
merci
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)
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
Un tuto merveilleux ! Merci
Cela semble pas autorisé chez l’hébergeur Funio.
(funio.com)
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.
bonjour ,
c’est très intéressant… après réaliser ces étapes et je veux ajouter des nouvelles informations c’est possible de les ajouter en excel et qu’il soit ajouter automatiquement à mySQL ???
merci
Bonjour Florian,
Merci infiniment pour ce tuto, ma question est la suivante :
Est ce qu’on peut realiser des requêtes sur plusieures tables avec jointure en modifiant la requête SQL qui se trouve dans la zone texte de la commande ??
Merci d’avance de votre réponse.
Bonjour, je suis totalement débutante en MySQL…
Je souhaiterai savoir si il existe une possibilité d’associer Excel et MySQL mais dans le sens inverse ce qui est présenté ci-dessus : en fait je voudrais que MySQL soit la BDD visible et que les données soient saisies dans Excel.
Pourquoi saisir dans excel : parce que MySQL ne permet pas d’intégrer des données au fur et à mesure des saisies qui sont en lien avec une BDD extérieure à chaque saisie. Je m’explique, saisie d’une référence de journal et ce journal à un numéro particulier que l’on trouve dans une autre base…
Et puis, je ne sais pas si dans MySQL il est possible de faire un lien afin que lorsque je saisie une donnée, je puisse savoir si cette donnée existe déjà dans l’un des tableaux de MySQL, comme dans excel qui donne le lien avec le »lien déroulant ».
Bref, je ne sais pas si je me suis fait bien comprendre, j’espère que vous pourrez me donner quelques réponses.
Merci par avance
Valérie