Aller au menu - Aller au contenu

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

Par Avatar Desolation
Mise à jour : 17/02/2012
Difficulté : Facile Facile Durée d'étude : 30 minutes
493 visites depuis 7 jours, classé 232/786
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.
Retour en haut
Sommaire du tutoriel :
Icône du chapitre
Retour en haut

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.
Code : SQL
1
2
3
4
5
6
7
8
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. :)

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


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


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 :

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:
Retour en haut

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.
Retour en haut

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


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 :

Code : SQL
1
2
3
4
5
6
7
8
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 (/).

Code : SQL
1
2
3
4
5
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. :)
Retour en haut
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. ;)
Retour en haut

Partager

Retour en haut
33 commentaires pour "Insérer des données d’un fichier Excel ou Access dans MySQL"
Note moyenne : 3.50 / 4 (4 votes)
Pseudo Commentaire
Hors ligne LeFbb # Posté le 25/02/2012 à 16:58:40

Beeee! je m'explic. cmt faire pour executer une requete comme SELECT * FROM personnes WHERE id<10; bref executer une cmd SQL sur Access directeùent sur la BD MySQL???
Connecté Desolation # Posté le 25/02/2012 à 17:03:51
Codeur du dimanche
Avatar
Flux RSS
close

Citation : LeFbb
Beeee! je m'explic. cmt faire pour executer une requete comme SELECT * FROM personnes WHERE id<10; bref executer une cmd SQL sur Access directeùent sur la BD MySQL???

Ah ok, alors une fois que tu as mis en place la solution n° 1, dans Access tu fais Créer > Création de requête > Là il te propose un assistant tu fais "Fermer" > Tu auras un bouton "SQL" en haut à gauche, tu cliques dessus et tu peux faire ta requête parmi les tables qui ont été liées.
Mon blog chez Syl » Développement web
Extension Chrome : Recevez automatiquement vos interventions et les MP !
Mes articles jQuery ▪ Qui est représentée sur mon avatar ? ▪ Bonnes pratiques jQuery

Pour les codes Javascript, vous pouvez les héberger et les partager avec jsFiddle.
 
Hors ligne zied86 # Posté le 02/04/2012 à 22:14:14

Merci pour votre tuto
mais j'ai pas pu inserer des lignes avec la methode 1
impossible de coller ou d'ajouter des lignes (office 2007,windows7)
merci
Connecté Sebsomes # Posté le 04/05/2012 à 12:45:56

Bonjour,
Je ne trouve pas comment me connecté à Mysql depuis xampp. Merci
Connecté Desolation # Posté le 04/05/2012 à 13:24:38
Codeur du dimanche
Avatar
Flux RSS
close

Citation : Sebsomes
Bonjour,
Je ne trouve pas comment me connecté à Mysql depuis xampp. Merci

Quand tu rédiges un commentaire sur un tutoriel il y a un message qui dit :
Citation
Nous vous rappelons que cet espace est réservé aux commentaires à propos des tutoriels, veillez donc à ne pas poser vos questions dans cette section.
Pour cela, rendez-vous sur le forum ! Merci.
Mon blog chez Syl » Développement web
Extension Chrome : Recevez automatiquement vos interventions et les MP !
Mes articles jQuery ▪ Qui est représentée sur mon avatar ? ▪ Bonnes pratiques jQuery

Pour les codes Javascript, vous pouvez les héberger et les partager avec jsFiddle.
 

Voir tous les commentaires
Ce tutoriel a été corrigé par les zCorrecteurs.