Déplacer des données d’Excel vers Access

Cet article explique comment déplacer vos données d’Excel vers Access et convertir vos données en tables relationnelles afin de pouvoir utiliser conjointement Microsoft Excel et Access. En résumé, Access est idéal pour capturer, stocker, interroger et partager des données, et Excel est idéal pour calculer, analyser et visualiser les données.

Deux articles, Utilisation d’Access ou d’Excel pour gérer vos données et Les 10 principales raisons d’utiliser Access avec Excel, traitent du programme le mieux adapté à une tâche spécifique et de la manière d’utiliser Excel et Access ensemble pour créer une solution pratique.

Le processus de transfert de données d’Excel vers Access comporte trois étapes de base.

Étape 1 : Importer des données d’Excel dans Access

L’importation de données est une opération qui peut se dérouler beaucoup plus facilement si vous prenez le temps de préparer et de propre vos données. Importer des données, c’est comme déménager dans un nouveau domicile. Si vous propre et organisez vos biens avant de déménager, il est beaucoup plus facile de s’installer dans votre nouvelle maison.

Nettoyer vos données avant d’importer

Avant d’importer des données dans Access, dans Excel, il est judicieux d’effectuer les opérations suivantes :

• Convertir des cellules qui contiennent des données non atomiques (c’est-à-dire plusieurs valeurs dans une cellule) en plusieurs colonnes. Par exemple, une cellule d’une colonne « Compétences » qui contient plusieurs valeurs de compétence, telles que « Programmation C# », « Programmation VBA » et « Conception Web » doit être divisée en colonnes distinctes contenant chacune une seule valeur de compétence.
• Utilisez la commande SUPPRESPACE pour supprimer les espaces de début, de fin et les espaces incorporés multiples.
• Supprimer les caractères non imprimables.
• Recherchez et corrigez les erreurs d’orthographe et de ponctuation.
• Supprimez les lignes ou les champs en double.
• Vérifiez que les colonnes de données ne contiennent pas de formats mixtes, en particulier des nombres formatés en tant que texte ou des dates formatées en tant que nombres.

Pour plus d’informations, consultez les rubriques d’aide Excel suivantes :

• Les dix meilleures solutions pour nettoyer vos données
• Filtrer des valeurs uniques ou supprimer des doublons
• Convertir les nombres stockés en tant que texte en nombres
• Convertir les dates stockées en tant que texte en dates

Choisissez le meilleur type de données lors de l’importation

Au cours de l’opération d’importation dans Access, vous devez faire les bons choix afin de recevoir peu (voire aucune) d’erreurs de conversion nécessitant une intervention manuelle. Le tableau suivant décrit la façon dont les formats de nombre et les types de données Access sont convertis lors de l’importation de données d’Excel vers Access, et fournit quelques conseils sur les meilleurs types de données à choisir dans l’Assistant Feuille de calcul.

Format de nombre Excel	Type de données Access	Commentaires	Bonne pratique
Texte	Texte, Mémo	Le type de données Access Texte stocke des données alphanumériques jusqu’à 255 caractères. Le type de données Mémo Access stocke des données alphanumériques jusqu’à 65 535 caractères.	Choisissez Mémo pour éviter de tronquer les données.
Nombre, pourcentage, fraction, scientifique	Nombre	Access a un type de données Nombre qui varie en fonction d’une propriété Taille du champ (Byte, Integer, Long Integer, Single, Double, Decimal).	Choisissez Double pour éviter toute erreur de conversion des données.
Date	Date	Access et Excel utilisent le même numéro de série pour stocker des dates. Dans Access, la plage de dates est plus grande : de -657 434 (1er janvier 100 après JC) à 2 958 465 (31 décembre 9999 après JC). Access ne reconnaissant pas le système de date 1904 (utilisé dans Excel pour Macintosh), vous devez convertir les dates dans Excel ou Access pour éviter toute confusion. Pour plus d’informations, voir Modifier le système de date, le format ou l’interprétation de l’année à deux chiffres et Importer ou lier des données dans un classeur Excel.	Choisissez une date.
Heure	Heure	Access et Excel stockent les valeurs de temps à l’aide du même type de données.	Choisissez l’heure, qui est généralement la valeur par défaut.
Devise, comptabilité	Devise	Dans Access, le type de données Monétaire stocke les données sous forme de nombres de 8 octets avec une précision de quatre décimales, et est utilisé pour stocker des données financières et empêcher l’arrondissement des valeurs.	Choisissez Devise, qui est généralement la valeur par défaut.
Booléen	Oui/non	Access utilise -1 pour toutes les valeurs Oui et 0 pour toutes les valeurs Non, tandis qu’Excel utilise 1 pour toutes les valeurs VRAI et 0 pour toutes les valeurs FAUX.	Choisissez Oui/Non, qui convertit automatiquement les valeurs sous-jacentes.
Lien hypertexte	Lien hypertexte	Un lien hypertexte dans Excel et Access contient une URL ou une adresse web sur laquelle vous pouvez cliquer et suivre.	Sélectionnez Lien hypertexte, sinon Access peut utiliser le type de données Texte par défaut.

Une fois les données dans Access, vous pouvez supprimer les données Excel. N’oubliez pas de sauvegarder le classeur Excel d’origine avant de le supprimer.

Pour plus d’informations, voir la rubrique d’aide Access Importer ou attacher des données dans un classeur Excel.

Ajouter automatiquement des données en toute simplicité

Un problème courant rencontré par les utilisateurs d’Excel consiste à ajouter des données avec les mêmes colonnes dans une grande feuille de calcul. Par exemple, vous avez peut-être une solution de suivi des biens qui a commencé dans Excel, mais qui s’est développée pour inclure les fichiers de nombreux groupes de travail et services. Ces données peuvent se trouver dans différentes feuilles de calcul ou classeurs, ou dans des fichiers texte qui sont des flux de données provenant d’autres systèmes. Il n’existe aucune commande d’interface utilisateur ni moyen simple d’ajouter des données similaires dans Excel.

La meilleure solution consiste à utiliser Access, qui vous permet d’importer et d’ajouter facilement des données dans une table à l’aide de l’Assistant Importation de feuille de calcul. De plus, vous pouvez ajouter un grand nombre de données dans une seule table. Vous pouvez enregistrer les opérations d’importation, les ajouter en tant que tâches Microsoft Outlook planifiées et même utiliser des macros pour automatiser le processus.

Étape 2 : Normaliser les données à l’aide de l’Assistant Analyseur de table

À première vue, le processus de normalisation de vos données peut sembler une tâche ardue. Heureusement, la normalisation des tables dans Access est un processus beaucoup plus facile grâce à l’Assistant Analyseur de table.

1. Faites glisser les colonnes sélectionnées vers une nouvelle table et créez automatiquement des relations

2. Utilisez les commandes de bouton pour renommer une table, ajouter une clé primaire, faire d’une colonne existante une clé primaire et annuler la dernière action

Vous pouvez utiliser cet Assistant pour effectuer les opérations suivantes :

• Convertissez une table en un ensemble de tables plus petites et créez automatiquement une relation de clé primaire et de clé étrangère entre les tables.
• Ajoutez une clé primaire à un champ existant qui contient des valeurs uniques ou créez un champ ID utilisant le type de données NuméroAuto.
• Créez automatiquement des relations pour renforcer l’intégrité référentielle avec des mises à jour en cascade. Les suppressions en cascade ne sont pas ajoutées automatiquement pour éviter les suppressions accidentelles de données, mais vous pouvez facilement ajouter des suppressions en cascade ultérieurement.
• Recherchez les données redondantes ou en double dans les nouvelles tables (par exemple, le même client avec deux numéros de téléphone différents) et mettez-les à jour si vous le souhaitez.
• Sauvegardez la table d’origine et renommez-la en ajoutant « _OLD » à son nom. Ensuite, vous créez une requête qui reconstruit la table d’origine avec le nom de la table d’origine afin que tous les formulaires ou états existants basés sur la table d’origine fonctionnent avec la nouvelle structure de table.

Pour plus d’informations, voir Normaliser vos données à l’aide de l’Analyseur de table.

Étape 3 : Se connecter pour accéder aux données d’Excel

Une fois que les données ont été normalisées dans Access et qu’une requête ou une table a été créée pour reconstruire les données d’origine, il suffit de se connecter aux données Access à partir d’Excel. Vos données se trouvent désormais dans Access en tant que source de données externe, et peuvent donc être connectées au classeur via une connexion de données, qui est un conteneur d’informations utilisé pour localiser, se connecter et accéder à la source de données externe. Les informations de connexion sont stockées dans le classeur et peuvent également être stockées dans un fichier de connexion, tel qu’un fichier de connexion de données Office (ODC) (extension de nom de fichier .odc) ou un fichier de nom de source de données (extension .dsn). Une fois connecté à des données externes, vous pouvez également actualiser (ou mettre à jour) automatiquement votre classeur Excel à partir d’Access chaque fois que les données sont mises à jour dans Access.

Pour plus d’informations, voir Importer des données à partir de sources de données externes (Power Query).

Accéder à vos données dans Access

Cette section vous guide à travers les phases suivantes de normalisation de vos données : décomposer les valeurs des colonnes Vendeur et Adresse en leurs morceaux les plus atomiques, séparer les sujets connexes dans leurs propres tables, copier et coller ces tables d’Excel dans Access, créer des relations clés entre les tables Access nouvellement créées, et créer et exécuter une requête simple dans Access pour renvoyer des informations.

Exemple de données sous forme non normalisée

La feuille de calcul suivante contient des valeurs non atomiques dans les colonnes Vendeur et Adresse. Les deux colonnes doivent être fractionnées en deux colonnes distinctes ou plus. Cette feuille de calcul contient également des informations sur les vendeurs, les produits, les clients et les commandes. Ces informations devraient également être divisées, par sujet, dans des tableaux séparés.

Vendeur	Order ID	Date de commande	ID produit	La quantité	Prix	Nom du client	Address (Adresse)	Téléphone
Li, Yale	2349	3/4/09	C-789	3	7,00 $	Fourth Coffee	7007 Cornell St Redmond, WA 98199	425-555-0201
Li, Yale	2349	3/4/09	C-795	6	9,75 $	Fourth Coffee	7007 Cornell St Redmond, WA 98199	425-555-0201
Adams, Ellen	2350	3/4/09	A-2275	2	16,75 $	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Adams, Ellen	2350	3/4/09	F-198	6	5,25 $	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Adams, Ellen	2350	3/4/09	B-205	1	4,50 $	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Hance, Jim	2351	3/4/09	C-795	6	9,75 $	Contoso, Ltd.	2302 Harvard Ave Bellevue, WA 98227	425-555-0222
Hance, Jim	2352	3/5/09	A-2275	2	16,75 $	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Hance, Jim	2352	3/5/09	D-4420	3	7,25 $	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Koch, Reed	2353	3/7/09	A-2275	6	16,75 $	Fourth Coffee	7007 Cornell St Redmond, WA 98199	425-555-0201
Koch, Reed	2353	3/7/09	C-789	5	7,00 $	Fourth Coffee	7007 Cornell St Redmond, WA 98199	425-555-0201

L’information dans ses plus petites parties : les données atomiques

En travaillant avec les données de cet exemple, vous pouvez utiliser la commande Convertir en colonne dans Excel pour séparer les parties « atomiques » d’une cellule (telles que l’adresse postale, la ville, l’état et le code postal) en colonnes distinctes.

Le tableau suivant montre les nouvelles colonnes d’une même feuille de calcul après avoir été fractionnées pour rendre toutes les valeurs atomiques. Notez que les informations de la colonne Vendeur ont été divisées en colonnes Nom et Prénom et que les informations de la colonne Adresse ont été fractionnées en colonnes Adresse postale, Ville, État et Code postal. Ces données se trouvent dans une « première forme normale ».

Nom	Prénom	Adresse postale	Ville	État	Code postal
Li	Yale	2302 Harvard Ave	Bellevue	WA	98227
Adams	Ellen	1025 Columbia Circle	Strasbourg	WA	98234
Hance	Jim	2302 Harvard Ave	Bellevue	WA	98227
Koch	Roseau	7007 Cornell St Redmond	Redmond	WA	98199

Décomposition des données en sujets organisés dans Excel

Les différents tableaux de données d’exemple qui suivent affichent les mêmes informations de la feuille de calcul Excel après son fractionnement en tables pour les vendeurs, les produits, les clients et les commandes. La conception de la table n’est pas définitive, mais elle est sur la bonne voie.

La table Vendeurs contient uniquement des informations sur le personnel de vente. Notez que chaque enregistrement possède un ID unique (ID du vendeur). La valeur ID du vendeur sera utilisée dans la table Commandes pour connecter les commandes aux vendeurs.

Vendeurs
ID du vendeur	Nom	Prénom
101	Li	Yale
103	Adams	Ellen
105	Hance	Jim
107	Koch	Roseau

La table Produits contient uniquement des informations sur les produits. Notez que chaque enregistrement possède un ID unique (ID produit). La valeur ID de produit sera utilisée pour connecter les informations de produit à la table Détails de la commande.

Produits
ID produit	Prix
A-2275	16.75
B-205	4.50
C-789	7,00
C-795	9.75
D-4420	7.25
F-198	5.25

La table Clients contient uniquement des informations sur les clients. Notez que chaque enregistrement possède un ID unique (ID client). La valeur ID client sera utilisée pour connecter les informations client à la table Commandes.

Clients
Réf consommateur	Nom	Adresse postale	Ville	État	Code postal	Téléphone
1001	Contoso, Ltd.	2302 Harvard Ave	Bellevue	WA	98227	425-555-0222
1003	Adventure Works	1025 Columbia Circle	Strasbourg	WA	98234	425-555-0185
1005	Fourth Coffee	7007 Cornell St	Redmond	WA	98199	425-555-0201

La table Commandes contient des informations sur les commandes, les vendeurs, les clients et les produits. Notez que chaque enregistrement possède un ID unique (ID de commande). Certaines des informations de cette table doivent être fractionnées dans une table supplémentaire qui contient les détails de la commande de sorte que la table Commandes ne contienne que quatre colonnes : l’ID de commande unique, la date de commande, l’ID de vendeur et l’ID client. Le tableau affiché ici n’a pas encore été fractionné dans le tableau Détails commandes.

Commandes
Order ID	Date de commande	ID du vendeur	Réf consommateur	ID produit	La quantité
2349	3/4/09	101	1005	C-789	3
2349	3/4/09	101	1005	C-795	6
2350	3/4/09	103	1003	A-2275	2
2350	3/4/09	103	1003	F-198	6
2350	3/4/09	103	1003	B-205	1
2351	3/4/09	105	1001	C-795	6
2352	3/5/09	105	1003	A-2275	2
2352	3/5/09	105	1003	D-4420	3
2353	3/7/09	107	1005	A-2275	6
2353	3/7/09	107	1005	C-789	5

Les détails de la commande, tels que l’ID produit et la quantité, sont déplacés hors de la table Commandes et stockés dans une table nommée Détails de la commande. N’oubliez pas qu’il y a 9 commandes. Il est donc logique qu’il y ait 9 enregistrements dans cette table. Notez que la table Commandes possède un ID unique (ID de commande), qui sera référencé dans la table Détails de la commande.

La structure finale de la table Commandes doit ressembler à ce qui suit :

Commandes
Order ID	Date de commande	ID du vendeur	Réf consommateur
2349	3/4/09	101	1005
2350	3/4/09	103	1003
2351	3/4/09	105	1001
2352	3/5/09	105	1003
2353	3/7/09	107	1005

La table Détails de la commande ne contient aucune colonne nécessitant des valeurs uniques (c’est-à-dire qu’il n’y a pas de clé primaire). Par conséquent, il est possible que l’une ou l’ensemble des colonnes contiennent des données « redondantes ». Toutefois, aucun des deux enregistrements de cette table ne doivent être complètement identiques (cette règle s’applique à toute table d’une base de données). Cette table doit contenir 17 enregistrements, chacun correspondant à un produit dans un ordre individuel. Par exemple, dans la commande 2349, trois produits C-789 constituent l’une des deux parties de l’ensemble de la commande.

Par conséquent, la table Détails commande doit ressembler à ceci :

Détails de la commande
Order ID	ID produit	La quantité
2349	C-789	3
2349	C-795	6
2350	A-2275	2
2350	F-198	6
2350	B-205	1
2351	C-795	6
2352	A-2275	2
2352	D-4420	3
2353	A-2275	6
2353	C-789	5

Copier et coller des données d’Excel dans Access

Maintenant que les informations sur les vendeurs, les clients, les produits, les commandes et les détails de commande ont été divisées en sujets distincts dans Excel, vous pouvez copier ces données directement dans Access où elles deviendront des tables.

Création de relations entre les tables Access et exécution d’une requête

Une fois que vous avez déplacé vos données vers Access, vous pouvez créer des relations entre les tables, puis créer des requêtes pour renvoyer des informations sur différents sujets. Par exemple, vous pouvez créer une requête qui renvoie l’ID de commande et les noms des vendeurs pour les commandes entrées entre le 05/03/09 et le 08/03/09.

De plus, vous pouvez créer des formulaires et des états pour faciliter la saisie des données et l’analyse des ventes.

Vous avez besoin d’une aide supplémentaire ?

Vous pouvez toujours poser des questions à un expert de la Communauté technique Excel ou obtenir de l’aide dans les Communautés.