Insérer des données d’un fichier Excel ou Access dans MySQL

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.

Maintenant que vous avez tous les outils nécessaires, concentrons-nous sur la mise en œuvre.

Solution n° 1 : avec un pilote ODBCPré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 :

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 :

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

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

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.

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 :-° ).

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

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

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

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. :)

Petite astuce : placez-vous en cellule A1 et appuyez sur les touches suivantes : Ctrl + Shift + Fin. Ainsi, toutes les données placées entre la cellule de départ en haut à gauche (ici A1) et la cellule en bas à droite seront sélectionnées jusqu’à ce qu’Excel ne trouve plus de données (dans notre cas, en B18). Vous pouvez maintenant copier (Ctrl + C).

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) :

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

Si vous êtes sous Windows XP, vous aurez peut-être cette erreur. Si tel est le cas, reportez-vous au chapitre « Résolution du problème ».

Reprenons. Vous devriez maintenant obtenir ceci :

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

Nous retrouvons donc nos enregistrements : mission accomplie !

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é :

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 :

Pour que les accents soient correctement enregistrés dans la table de destination, il faut convertir le fichier *.csv en UTF-8. Pour cela, vous pouvez l’ouvrir avec Notepad++ et utiliser le menu Encodage > Convertir en UTF-8.

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.

Que ce soit sous UNIX/Linux ou Windows, le séparateur de répertoire reste le slash (/).

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. ;)

• #

Waouh !

Très heureux de voir que nos cours vous plaisent, déjà 3 pages lues aujourd'hui ! Vous pouvez continuer la lecture de nos cours en devenant un Roomie, un membre de la communauté d'OpenClassrooms. C'est gratuit !

Vous pourrez aussi suivre votre avancement dans le cours, faire les exercices et discuter avec les autres Roomies.

S'inscrire Se connecter

• Thématiques du cours : Base de données Bureautique Excel Microsoft Office MySQL Tableur

Chaque cours est créé par un professeur ou un expert dans le domaine. Il est composé d'une ou plusieurs parties et peut comporter du texte, des images (schéma, illustration) et des vidéos. Chaque partie d'un cours certifiant est ponctuée d'exercices de 2 types : Des quiz corrigés automatiquement Des devoirs libres (exemple : créer un site web avec des consignes précises). Ces devoirs sont évalués par les pairs. Chaque devoir est corrigé 3 fois par 3 autres élèves, dans un processus en double aveugle, selon un barème fixé par le professeur. La note finale est la moyenne des 3 notes reçues sur le devoir. 50% des devoirs corrigés par les pairs obtiennent quasiment les 3 mêmes notes, avec une variance inférieure à 1. La recherche académique montre d'ailleurs que l'évaluation par les pairs peut être aussi précise que celle des professeurs.