Ce cours est visible gratuitement en ligne.

Got it!
Insérer des données d’un fichier Excel ou Access dans MySQL
Last updated on Thursday, October 31, 2013
  • 30 minutes
  • Facile

Introduction du cours

Bonjour à tous !

Vous possédez un grand nombre de données contenues sur des supports tels que des fichiers issus d’Access ou d’Excel et souhaitez les insérer dans une base de données MySQL sans avoir à tout recopier ? Vous êtes sur la bonne page !

Ce tutoriel a pour vocation d’aider un maximum de personnes qui rencontrent cette difficulté. Ayant moi-même été confronté à cette situation, j’ai pour vous plusieurs solutions :

  1. avec un pilote ODBC, la plus complexe ;

  2. avec le format CSV, la plus rapide.

Afin de mener à bien nos manipulations, vous devez disposer d’un certain nombre d’éléments :

  • un fichier source *.xls de test (télécharger), que je vous fournis (il a été fait sous Excel 2010 mais enregistré pour une compatibilité Excel 2003 — notez que vous avez tout de même la possibilité, si vous êtes utilisateur de la version 2003, de lire des fichiers issus des versions 2007 et supérieures en téléchargeant un petit utilitaire fourni par Microsoft) ;

  • la suite Microsoft Office (2003 ou supérieure), principalement Access et Excel : j’utiliserai la version 2010 ;

  • la plateforme WAMP (ou similaire), que nous emploierons seulement pour le moteur MySQL : j’utiliserai WAMP Server 2.2 avec MySQL 5.5.16.

Solution n° 1 : avec un pilote ODBC

Préparation

Pour cette solution, il est nécessaire de passer par ces quelques étapes de préparation :

  1. analyser la structure du fichier ;

  2. mettre en place la base de données et la table qui recevra les données du côté de MySQL ;

  3. installer le pilote ODBC pour faire communiquer Access et MySQL ;

  4. créer la source de données.

Analyse du fichier source

Nous allons ici ouvrir le fichier qui contient les données, et ainsi prendre connaissance du nombre de champs (colonnes) et des types de valeurs que nous souhaiterions obtenir en sortie. Regardons notre exemple, fraîchement téléchargé et ouvert :

Image utilisateur

Dans la colonne A, nous avons des nombres (certes, pas stockés sous un type numérique dans notre champ, mais ce n’est pas un problème) et, en colonne B, des prénoms.
Cette situation est plus que classique : on peut déduire qu’à un identifiant unique correspond un prénom (certes, lui, pas forcément unique).

Mise en place de la base de données de destination

Nous avons donc deux champs. Le premier se nommera id et sera de type INT, le second se nommera prenom et sera de type VARCHAR(50).

Voici le code SQL permettant de créer la base de données tuto et d’ajouter la table personnes contenant les deux champs cités précédemment.

CREATE DATABASE tuto;
USE tuto;

CREATE TABLE personnes (
id int(11) NOT NULL AUTO_INCREMENT,
prenom varchar(50) NOT NULL,
PRIMARY KEY (id)
);

Notre objectif est maintenant d’insérer nos données dans cette table !

Installation du pilote

Pour cette solution, vous devez installer un pilote ODBC pour MySQL. Téléchargez la version 32 bits ou 64 bits (cliquez sur « No thanks, just take me to the downloads! » si vous ne souhaitez pas créer de compte) selon votre configuration Windows et installez. J’utiliserai cette version qui est la 5.1.10 (64 bits).

Création de la source de données

Pour qu’Access puisse communiquer avec MySQL, il a besoin d’un pilote mais aussi d’une source de données, habituellement appelée DSN (Data Source Name) ; c’est elle qui va indiquer sur quel serveur et quelle base se connecter.

Lancez votre serveur MySQL et vérifiez qu’il est bien démarré. Rendez-vous dans Panneau de configuration > Système et sécurité > Outils d’administration > Sources de données (ODBC). L’écran suivant s’affiche alors :

Image utilisateur

Dans « Source de données utilisateur », sélectionnez « Ajouter », après quoi vous obtenez ceci :

Image utilisateur

Vous avez la liste de tous les pilotes utiles pour les bases de données installés sur votre machine, sélectionnez donc « MySQL ODBC 5.1 Driver », le pilote que vous venez d’installer, et faites « Terminer ».

Image utilisateur

Configurons à présent notre connexion à MySQL. Il nous faut tout d’abord donner un nom à notre DSN, choisissez donc un nom qui vous permettra de la retrouver facilement par la suite. Indiquez ensuite le nom du serveur (localhost), le nom d’utilisateur (par défaut, root sans mot de passe) puis sélectionnez dans la liste la base de données concernée (soit tuto). Cliquez ensuite sur « Test » pour tester la connexion. Vous devriez obtenir « Connection successful ». Cliquez sur « OK ».

Notre DSN est désormais créée : nous pouvons ouvrir Access !

Connexion à MySQL depuis Access

Créez un nouveau fichier Access. Étant sous 2010, j’obtiens un fichier *.accdb — sous 2003, vous devriez obtenir un *.mdb. Ouvrez le fichier si ce n’est déjà fait.

Si vous utilisez Access 2007, cherchez Données externes > Plus > Base de données ODBC.
Sous Access 2010, cherchez Données externes > Base de données ODBC.

Image utilisateur

Mais qu’est-ce que ce « ODBC » ? Tu nous as fait installer un truc dont on ne connaît même pas l’utilité ?!

Citation : Comment Ça Marche

ODBC signifie Open DataBase Connectivity. Il s'agit d'un format défini par Microsoft permettant la communication entre des clients bases de données fonctionnant sous Windows et les SGBD du marché.

Le gestionnaire ODBC est présent sur les systèmes Windows. Il existe toutefois des implémentations sur d'autres plates-formes, notamment des plates-formes UNIX/Linux.

Voilà pour la définition. Seulement, le pilote pour MySQL n’est pas natif à Windows, c’est pourquoi je vous l’ai fait télécharger.

Vous devriez obtenir cette fenêtre. Sélectionnez le second choix car nous souhaitons nous synchroniser avec notre table personnes (pour ceux qui suivent :-° ).

Image utilisateur

Ensuite, nous allons retrouver notre DSN dans « Source de données machine ».

Image utilisateur

Sont répertoriées les tables appartenant à la base de données sélectionnée (d’où l’intérêt de construire sa table auparavant), sélectionnez donc la table personnes et cliquez sur « OK ».

Image utilisateur

Sur la liste, à gauche, apparaît l’entrée personnes : double-cliquez dessus pour l’ouvrir ; nous retrouvons bien nos champs id et prenom.

Image utilisateur

Retournons à présent sur notre fichier Excel (ne fermez pas le fichier Access) — rouvrez-le si vous l’aviez fermé. Il va maintenant s’agir de sélectionner toutes nos données par une combinaison que vous connaissez bien. :)

Image utilisateur

Retournez sur votre fichier Access puis sélectionnez l’ensemble des données en cliquant sur la petite flèche qui se situe à gauche de id et au-dessus de l’étoile * (c’est très important, sinon la copie ne fonctionnera pas) :

Image utilisateur

Il ne vous reste plus qu’à coller les données avec Ctrl + V.

Image utilisateur

Reprenons. Vous devriez maintenant obtenir ceci :

Image utilisateur

Bien sûr, répondez « Oui ». C’est terminé, vos données sont insérées ! Allez quand même vérifier via phpMyAdmin si vous en doutez. Ou via la console MySQL (pour le fun ! :magicien: ).

Image utilisateur

Nous retrouvons donc nos enregistrements : mission accomplie ! :ninja:

Résolution du problème

J’ai rencontré ce problème sous Windows XP et avec Office 2007.

Première solution

Allez dans Panneau de configuration > Ajout/Suppression de programmes > cochez « Afficher les mises à jour » > sélectionnez la mise à jour pour Excel 2007 KB958437 et supprimez-la. Copiez à nouveau la sélection dans Excel et collez dans Access. Si le problème persiste ou si vous ne trouvez pas cette mise à jour dans la liste, passez à la seconde solution.

Seconde solution

Ouvrez un document Word vierge, collez avec Ctrl + V, sélectionnez à nouveau avec Ctrl + A, copiez avec Ctrl + C, collez dans Access avec Ctrl + V et ça devrait fonctionner.

Solution n° 2 : avec le format CSV

Cette solution est la plus simple. Il faut au préalable convertir le fichier tuto.xls en tuto.csv.

Il faut pour cela, dans Excel, faire un « Enregistrer sous… » et choisir le type approprié :

Image utilisateur

En mode graphique

phpMyAdmin, pour ceux qui en disposent, propose un import de données par fichier dont le poids n’excède pas 2 Mo.
Choisissez votre base de données puis sélectionnez l’option « Importer » et remplissez le formulaire comme suit :

Image utilisateur

phpMyAdmin vous a créé une table et des colonnes avec des noms génériques.

En mode console

Par ce biais, vous n’aurez (normalement) pas de contrainte concernant le poids du fichier.
Il faut d’abord créer la base de données et la table, tout comme dans la solution no 1. Voici le code pour créer la base et la table :

CREATE DATABASE tuto;
USE tuto;

CREATE TABLE personnes (
id int(11) NOT NULL AUTO_INCREMENT,
prenom varchar(50) NOT NULL,
PRIMARY KEY (id)
);

Ensuite, il faut utiliser cette commande pour intégrer le *.csv.

LOAD DATA LOCAL INFILE 'C:/User/Syl/Desktop/tuto.csv' 
INTO TABLE tuto.personnes 
FIELDS TERMINATED BY ';' 
ENCLOSED BY '' 
LINES TERMINATED BY '\r\n';

Voilà, les données sont insérées, vous pouvez vérifier comme je l’ai indiqué en fin de solution no 1. :)

Le tutoriel est à présent terminé. Merci de m’avoir suivi ! J’espère avoir été assez clair, mais si vous avez des questions ou toute autre suggestion, n’hésitez pas à poster un commentaire. ;)

How courses work

  • 1

    You have now access to the course contents and exercises.

  • 2

    You will advance in the course week by week. Each week, you will work on one part of the course.

  • !

    Exercises must be completed within one week. The completion deadline will be announced at the start of each new part in the course. You must complete the exercises to get your certificate of achievement.

  • 3

    At the end of the course, you will get an email with your results. You will also get a certificate of achievement if you are a

Example of certificate of achievement
Example of certificate of achievement