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

Cet article vous explique comment déplacer vos données à partir d’Excel pour accéder à vos données et les convertir en tableaux relationnels afin de pouvoir utiliser Microsoft Excel et Access ensemble. Pour résumer, Access est préférable pour la capture, le stockage, l’interrogation et le partage de données, et il est préférable d’utiliser Excel pour calculer, analyser et visualiser des données.Deux articles qui vous permettent d’utiliser Access ou Excel pour gérer vos données et 10 raisons pour lesquelles vous pouvez utiliser Access avec Excel, discuter du programme le mieux adapté à une tâche particulière et utiliser Excel et Access pour créer une solution pratique.Lorsque vous déplacez des données d’Excel vers Access, le processus comporte trois étapes de base.

Trois étapes de base

Remarque : Pour plus d’informations sur la modélisation et les relations de données dans Access, voir notions de base de la conception de base de données.

Étape 1 : importer des données à partir d’Excel dans Access

L’importation de données est une opération qui peut être effectuée beaucoup plus facilement si vous prenez du temps pour préparer et nettoyer vos données. L’importation de données est comme le déplacement vers une nouvelle maison. Si vous nettoyez et organisez vos propres mains avant de procéder à la migration, le passage de votre nouvelle maison est beaucoup plus facile.

Nettoyer vos données avant de procéder à l’importation

Avant d’importer des données dans Access, dans Excel, il est conseillé de :

  • 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 « Skills » qui contient plusieurs valeurs de compétences, telles que « programmation C# », « programmation VBA » et « conception Web », doit être scindée en deux colonnes qui contiennent chacune une valeur de compétence.
  • Utilisez la commande découper pour supprimer les espaces de début, de fin et multiples.
  • Supprimer les caractères non imprimables.
  • Rechercher et corriger les fautes d’orthographe et de ponctuation.
  • Supprimez les lignes en double ou les champs en double.
  • Assurez-vous que les colonnes de données ne contiennent pas de formats combinés, en particulier en tant que nombres au format texte ou dates au format numérique.

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

Remarque : Si les besoins en matière de nettoyage des données sont complexes, ou si vous n’avez pas le temps ou les ressources pour automatiser le processus, vous pouvez envisager d’utiliser un fournisseur tiers. Pour plus d’informations, recherchez « logiciel de nettoyage des données » ou « qualité des données » de votre moteur de recherche préféré dans votre navigateur Web.

Choisir le type de données le plus approprié lors de l’importation

Pendant l’opération d’importation dans Access, vous pouvez choisir de bon choix pour recevoir des erreurs de conversion qui nécessiteront une intervention manuelle. Le tableau suivant résume la façon dont les formats de numéros Excel et les types de données Access sont convertis lorsque vous importez des données à partir d’Excel vers Access, et propose des conseils sur les types de données les plus appropriés à votre choix dans l’Assistant importation de feuille de calcul.

Format numérique Excel Type de données Access Commentaires Bonne pratique
Texte Texte, mémo Le type de données texte Access stocke les données alphanumériques jusqu’à 255 caractères. Le type de données Mémo Access stocke les données alphanumériques jusqu’à 65 535 caractères. Sélectionnez Mémo pour éviter de tronquer les données.
Nombre, pourcentage, fraction, scientifique Nombre Access possède un type de données numérique qui varie en fonction de la propriété taille du champ (octet, entier, entier long, simple, double, Decimal). Choisissez double pour éviter les erreurs de conversion de données.
Date Date Dans Access et Excel, les deux utilisent le même numéro de date en série pour le stockage des dates. Dans Access, la plage de dates est plus grande : de-657 434 (1er janvier 100 A.D.) à 2 958 465 (31 décembre 9999).

Dans la mesure où Access ne reconnaît 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, reportez-vous à modifier le système de dates, le format ou l’interprétation des années à deux chiffres et importer ou lier des données dans un classeur Excel.

Sélectionnez Date.
Heure Time Dans Access et Excel, les valeurs d’heure sont stockées à l’aide du même type de données. Sélectionnez heure, qui est généralement la valeur par défaut.
Devise, comptabilité Devise Dans Access, le type de données Currency stocke les données sous forme de nombres à 8 octets avec une précision de quatre décimales et est utilisé pour stocker les données financières et empêcher l’arrondi de valeurs. Sélectionnez 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 vraies et 0 pour toutes les valeurs faux. Choisissez Oui/non, ce 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 que vous pouvez cliquer et suivre. Cliquez sur lien hypertexte, sinon Access pourrait 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 d’abord le classeur Excel d’origine avant de le supprimer.

Pour plus d’informations, consultez la rubrique d’aide Access importer ou lier des données dans un classeur Excel.

Ajouter automatiquement des données de manière simple

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 été démarrée dans Excel, mais elle a maintenant été développée pour inclure les fichiers de nombreux groupes de travail et services. Ces données peuvent se trouver dans des feuilles de calcul et des classeurs différents, ou dans des fichiers texte qui sont des flux de données d’autres systèmes. Il n’y a pas de commande d’interface utilisateur ou simple pour ajouter des données similaires dans Excel.

La meilleure solution consiste à utiliser Access, mais vous pouvez facilement importer et ajouter des données dans une table à l’aide de l’Assistant importation de feuille de calcul. Par ailleurs, vous pouvez ajouter un grand nombre de données dans une 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 analyse de table

Tout d’abord, le processus de normalisation de vos données risque de sembler une tâche ardue. Heureusement, il est beaucoup plus facile de normaliser les tables dans Access à l’aide de l’Assistant analyse de table.

Assistant Analyseur de table

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

2. utiliser les commandes de bouton pour renommer un tableau, ajouter une clé primaire, définir une colonne existante comme clé primaire et annuler la dernière action

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

  • Convertir un tableau en un ensemble de plus petits tableaux et créer automatiquement une relation de clé primaire et étrangère entre les tables.
  • Ajoutez une clé primaire à un champ existant qui contient des valeurs uniques ou créez un nouveau champ ID utilisant le type de données NuméroAuto.
  • Créer automatiquement des relations pour appliquer l’intégrité référentielle avec des mises à jour en cascade. Les suppressions en cascade ne sont pas automatiquement ajoutées pour éviter toute suppression accidentelle de données, mais vous pouvez facilement ajouter des suppressions en cascade ultérieurement.
  • Recherchez de nouveaux tableaux pour les données redondantes ou dupliquées (par exemple, le même client avec deux numéros de téléphone différents) et mettez-le à jour comme 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 de telle sorte que les formulaires ou États existants basés sur la table d’origine fonctionneront avec la nouvelle structure de tableau.

Pour plus d’informations, voir normaliser vos données au moyen de l’analyseur de table.

Étape 3 : se connecter à des données Access à partir 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 recréer les données d’origine, il est très simple de se connecter à des données Access à partir d’Excel. Vos données étant désormais accessibles en tant que source de données externe, vous pouvez vous connecter au classeur par le biais d’une connexion de données, qui est un conteneur d’informations permettant de rechercher, de se connecter et d’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) ou un fichier de nom de source de données (extension. DSN). Après vous être connecté à des données externes, vous pouvez également actualiser automatiquement (ou mettre à jour) 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édez à vos données dans Access

Cette section vous guide dans les étapes suivantes sur la normalisation de vos données : rupture de valeurs des colonnes SalesPerson et Address dans leurs propres parties atomiques, en séparant les sujets associés dans leurs propres tables, en copiant et en collant ces tables d’Excel dans Accédez à la création de relations principales entre les tables Access nouvellement créées et créez et exécutez une requête simple dans Access pour renvoyer des informations.

Exemple de données dans un formulaire non normalisé

La feuille de calcul suivante contient des valeurs non atomiques dans la colonne SalesPerson et la colonne adresse. Les deux colonnes doivent être fractionnées en deux colonnes distinctes. Cette feuille de calcul contient également des informations sur les vendeurs, produits, clients et commandes. Ces informations doivent également être fractionnées en fonction de leur sujet dans des tableaux séparés.

Vendeur Réf commande Date de commande Réf produit Nbre Prix Nom du client 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, Élisabeth 2350 3/4/09 A-2275 2 $16,75 Adventure Works 1025 Columbia Circle Kirkland, WA 98234 425-555-0185
Adams, Élisabeth 2350 3/4/09 F-198 6 $5,25 Adventure Works 1025 Columbia Circle Kirkland, WA 98234 425-555-0185
Adams, Élisabeth 2350 3/4/09 B-205 1 $4,50 Adventure Works 1025 Columbia Circle Kirkland, WA 98234 425-555-0185
Hance, Jean 2351 3/4/09 C-795 6 $9,75 Contoso, Ltd. 2302 Harvard moyenne Bellevue, WA 98227 425-555-0222
Hance, Jean 2352 3/5/09 A-2275 2 $16,75 Adventure Works 1025 Columbia Circle Kirkland, WA 98234 425-555-0185
Hance, Jean 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

Informations dans les parties les plus petites : données atomiques

Utilisation des données dans cet exemple, vous pouvez utiliser la commande convertir dans Excel pour séparer les parties « atomiques » d’une cellule (par exemple, adresse postale, ville, État et code postal) en colonnes discrètes.

Le tableau suivant montre les nouvelles colonnes dans la même feuille de calcul, une fois qu’elles ont été fractionnées, afin d’obtenir une valeur atomique. Notez que les informations de la colonne vendeur ont été fractionnées en noms et les premières colonnes nom et que les informations de la colonne adresse sont fractionnées dans les colonnes adresse, ville, État et code postal. Ce type de données est « première forme normale ».

Nom Prénom   Rue Ville État Code postal
Li Yale 2302 Harvard-rue Bellevue WA 98227
Adams Élisabeth 1025 Columbia Circle Strasbourg WA 98234
Hance Jim 2302 Harvard-rue Bellevue WA 98227
Koch Reed 7007 Cornell St Redmond Redmond WA 98199

Fractionner des données en objets organisés dans Excel

Les différentes tables de données d’exemple qui suivent montrent les mêmes informations que celles de la feuille de calcul Excel, une fois qu’elles ont été divisées en tables pour vendeurs, produits, clients et commandes. La création de table n’est pas définitive, mais elle est sur la bonne voie.

La table vendeurs ne contient que des informations sur les membres du personnel. Notez que chaque enregistrement possède un ID unique (IDENTIFIant commercial). La valeur de l’ID commercial est utilisée dans la table commandes pour relier les commandes aux commerciaux.

Vendeurs
ID du vendeur Nom Prénom
101 Li Yale
103 Adams Élisabeth
105 Hance Jim
107 Koch Reed

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

Produits
Réf 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 Réf client est utilisée pour connecter les informations client à la table commandes.

Clients
Réf consommateur Nom Rue Ville État Code postal Téléphone
1001 Contoso, Ltd. 2302 Harvard-rue 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 ce tableau doivent être divisées en une table supplémentaire qui contient les détails de la commande de sorte que la table commandes ne contient que quatre colonnes : l’ID de commande unique, la date de commande, l’ID de vendeur et l’ID client. La table affichée ici n’a pas encore été scindée en table Détails commande.

Orders
Réf commande Date de commande ID du vendeur Réf consommateur Réf produit Nbre
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 du produit et la quantité, sont déplacés de la table commandes et stockés dans une table intitulée Détails de la commande. Rappelez-vous qu’il y a 9 commandes, il est donc judicieux qu’il y a 9 enregistrements dans cette table. Notez que la table commandes possède un ID unique (ID de commande), qui fera l’appel de la table Détails commande.

La conception finale de la table commandes doit ressembler à ce qui suit :

Orders
Réf commande 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 commandes ne contient pas de colonnes qui requièrent des valeurs uniques (c’est-à-dire qu’il n’y a pas de clé primaire), de sorte que tout ou partie des colonnes contiennent des données « redondantes ». Néanmoins, il est préférable de ne pas avoir deux enregistrements dans cette table (cette règle s’applique uniquement à une table d’une base de données). Dans ce tableau, il devrait y avoir 17 enregistrements, chacun correspondant à un produit dans une commande individuelle. Par exemple, dans l’ordre 2349, trois produits C-789 constituent l’une des deux parties de l’ordre complet.

Par conséquent, la table Détails commande doit ressembler à ce qui suit :

Détails de la commande
Réf commande Réf produit Nbre
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 à partir d’Excel dans Access

À présent que les informations sur les vendeurs, les clients, les produits, les commandes et les détails de la commande sont décomposées en différents sujets dans Excel, vous pouvez copier ces données directement dans Access, à l’endroit où elles deviennent des tableaux.

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

Après avoir 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 les différents sujets. Par exemple, vous pouvez créer une requête qui renvoie l’ID de commande et les noms des représentants pour les commandes entrées entre 3/05/09 et 3/08/09.

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