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

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 :

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

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

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.

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

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

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.

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 :

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