Résumé de section

  • Access et Excel communiquent et échangent des données très naturellement.


    groupe "données externes"     (1er bloc)

    <clic g>  sur
    sélectionner le fichier
      pour accepter l'importation des données dans Excel
    Si la base compte plus d'une table, Excel demande de définir la table à importer

     

    Importer les données du fichier "access.accdb"

    2.         L'EXTRACTION AVANCÉE DE DONNÉES D'UNE BASE ACCESS

    Excel permet d'extraire d'une base les seules données utiles par l'intermédiaire d'une requête effectuée avec un assistant. La requête peut être enregistrée.


    groupe "données externes"     (1er bloc)

    <clic g>  sur  
    <clic g>      sur  
    sélectionner "nouvelle source de données"
     pour valider

    a)   LA CRÉATION DE LA SOURCE DE DONNÉES

    Elle consiste à enregistrer dans un fichier "source de données" les paramètres de connexion au fichier de base de données par le canal du pilote (driver) "odbc"

      fenêtre query

    1 saisir le nom de la source de données
    2 sélectionner le driver odbc associé
    3     établir la connexion avec le fichier de base de données
    4     indiquer éventuellement la table ou sont les données recherchées

    Un connecteur odbc permet de lier une ou plusieurs tables de base de données à l'application en cours (ici Excel)

    b)  LA CONNEXION À LA BASE DE DONNÉES

    Elle crée un canal (grâce au pilote odbc) entre un fichier de base de données et Excel.

      fenêtre query

    <clic g> sur
    <clic g> sur
    désigner le fichier de données Access (accdb)
     pour valider le fichier
     pour valider la création de la connexion
     pour créer la source de données

    La source de données est créée.

    Elle est maintenant directement accessible dans  du groupe "données externes"

    c)   LA CREATION DE LA REQUÊTE

    L'assistant "requête" prend alors la main et permet alors de sélectionner les champs à prendre en compte ainsi que les critères d'extraction.

      assistant requête

    <clic g> sur + de la table affiche la liste des champs
    sélectionner     le champ puis      pour le prendre dans la requête
    répéter l'opération pour tous les champs à prendre en compte
    les données afférentes peuvent être affichées avec
    sélectionner le champ dans la partie droite puis      ou pour en modifier l'ordre
    <clic g>      sur
    filtrer et trier éventuellement les enregistrements
    <clic g>      sur
    <clic g>     sur
    <clic g>     sur
    confirmer ou modifier les coordonnées de la cellule et la feuille d'insertion
     pour valider

    Les données peuvent être modifiées dans Excel mais ces modifications ne sont pas répercutées dans la base source ; l'outil  réaffiche les données d'origine

     

    Créer un nouveau classeur
    Y afficher les enregistrements extraits de la base access.accdb pour les champs "société", "contact", "adresse" et "cdpostal" puis enregistrer sous le nom "req"

    d)  LA MODIFICATION DE LA REQUÊTE

    La requête peut être modifiée. L'assistant permet d'utiliser des critères de sélection pour les enregistrements et de trier ces enregistrements.


    groupe "connexions"     (2ème bloc)

    une cellule des données importées étant sélectionnée
    <clic g>      sur      de  
    <clic g>      sur
    onglet
    <clic g>  sur
    la requête s'affiche de nouveau dans l'assistant

    Modifier la requête et ne prendre que les enregistrements dont le code postal est supérieur ou égal à 10000 et strictement inférieur à 45000
    Trier par "cdpostal" et "société" dans l'ordre croissant

     

    Lorsque qu'un champ est filtré ; il est en gras.
    Lors de plusieurs requêtes successives, enlever les filtres utilisés précédemment

    e)  'ENREGISTREMENT DE LA REQUÊTE

    À la fin de l'assistant requête, Excel permet l'enregistrement de la requête.

      assistant requête

    dans la dernière étape de l'assistant
    <clic g>  sur
    nommer la requête et indiquer le dossier

    La requête est enregistrée au format .dqy, le dossier requête de l'utilisateur est alors proposé mais un autre peut être choisi

     

    Enregistrer la requête précédente sous le nom "req1" dans le même dossier que les exercices puis fermer le classeur

    3.         L'EXTRACTION DE DONNÉES D'UNE BASE EXTERNE

    Excel permet d'extraire des données issues de la plupart des bases avec les drivers odbc. Sont accessibles la plupart des formats du marché si les pilotes adéquats ont été installés. Seule la connexion à la source diffère du cas précédent.

    a)   L'INSTALLATION DU PILOTE ODBC

    Le pilote doit être le plus souvent téléchargé à partir du site web de l'éditeur.

    Pour utiliser le pilote odbc de MySql,
    Se rendre sur http://www.mysql.fr/downloads/connector/odbc/
    Télécharger le pilote     Windows (x86, 32-bit), MSI Installer Connector-ODBC
    L'installer

    b)  LA CRÉATION DE LA SOURCE DE DONNÉES

    Elle va consister à enregistrer dans un fichier "source de données" les paramètres du pilote (driver)  "odbc"

     panneau de configuration


    onglet
    <clic g>  sur
    sélectionner le driver
    <clic g>  sur

     

    Pour créer une source de données à partir du pilote odbc MySql
    onglet
    <clic g>  sur
    sélectionner le driver
    paramétrer la connexion
    <clic g>  sur

    avancé

    Un connecteur odbc permet de lier une ou plusieurs tables de base de données à l'application en cours (ici Excel). Il peut être nécessaire de rajouter les pilotes ODBC du type de base de données source (Mysql : mysql-connector-odbc-5.2 win32.msi ou postérieur)

    c)   LA CONNEXION A LA BASE DE DONNÉES

    Elle crée un canal entre un fichier de base de données et Excel par l'intermédiaire du pilote odbc.

    Vous avez une version locale de votre site web qui fonctionne en php avec une base de données Mysql (pour cela, vous utilisez webmatrix, Wampserver ou Easyphp qui créent un environnement Web local). Vous pouvez extraire des données de cette base et les traiter dans Excel : la fenêtre ci-dessus illustre la création d'une source de données à partir de cette base Mysql locale à l'aide du driver odbc précédemment téléchargé

    Access permet de faire la même chose et d'agir directement sur les données source en mode "table", ce qui est très pratique pour modifier globalement les données sans passer par des requêtes ; il suffit alors de rapatrier en ligne les données (restauration Sql et après vérification)

    d)  L'IMPORTATION DANS EXCEL

    Il faut désigner à Excel la source de données créée.

     onglet "données"
    groupe "données externes"     (1er bloc)

    <clic g>  sur  
    sélectionner     la source créée
    <clic g>  sur

     

    Pour trouver le driver odbc d'un type de base de données, interrogez simplement votre moteur de recherche préféré.

    4.         L'EXTRACTION AVANCÉE AVEC MICROSOFT QUERY

    Microsoft Query permet d'ouvrir les requêtes enregistrées ou de traiter les données avant de les renvoyer vers Microsoft Excel.

    a)   OUVERTURE DE LA REQUÊTE

    Il faut d'abord désigner la requête, préalablement enregistrée, pour l'ouvrir.


    groupe "données externes"     (1er bloc)

    <clic g>  sur  
    <clic g>      sur
    onglet
    <clic g>      sur
    sélectionner     la requête
     puis
     pour valider
    dans la dernière étape de l'assistant, choisir  

    Lors de la modification d'une requête, il peut arriver qu'Excel ait synthétisé les critères au sein d'un seul filtre et n'arrive plus à les lire (l'ouvrir alors directement en sql et modifier)
    Modifier la requête en la décomposant afin qu'Excel la comprenne

     

    Ouvrir la requête "req1" créée précédemment ; modifier les critères de filtre si nécessaire

    Si Excel ne trouve plus la base de données (déplacée, pas le même micro…), cliquez sur  pour désigner son chemin d'accès

    b)  DÉTERMINATION DES CRITÈRES

    La requête ouverte, les critères peuvent être ajoutés ou modifiés.

     query
    indiquer directement le nouveau critère dans la zone de critères

    ou
    menu critères
    ajouter des critère
    indiquer le champ, l'opérateur et la valeur
     lorsque le critère est défini
     permet de combiner des critères
     lorsque tous les critères ont été définis

     

    les critères s'inscrivent dans un volet de la fenêtre et les valeurs affichées correspondent aux critères

     

      

    Plutôt qu'indiquer un critère complexe pour un même champ comme ici :
    il vaut mieux répéter le champ de critère dans la colonne suivante et d'indiquer le critère sur la même ligne (équivalent à "et")
    ou sur une ligne différente (équivalent à "où")

     

    Créer une source de données pour les fichiers "accdb" à partir de la base de données "northwind 2013 ios.accdb"
    Afficher la table "employes", et extraire :
    - les employés     habitant une ville dont le code postal est strictement supérieur à 60000 et inférieur ou égal à 74000 exerçant la profession de "représentant commercial"
    - ceux exerçant la profession de "Vice-président des ventes" quelle que soit leur ville
    - ainsi que les employés exerçant la profession de "représentant commercial" habitant Cherbourg

    Penser à cliquer sur  pour actualiser les enregistrements affichés

     

    Il est possible d'afficher cette requête directement dans Query (menu Fichier). Elle est enregistrée sous le nom  du dossier d'exercices ; si Query affiche un message d'erreur réseau ou disque, il suffit de lui indiquer le chemin d'accès à la base de données en cliquant sur  (ok sur log admin et mot de passe vide)

    c)   AJOUTER UNE TABLE

    L'opération est simple.

     query
    <clic g>      sur
    sélectionner la table
    <clic g>      sur  puis

    d)  RENVOYER LES DONNÉES VERS EXCEL

    Une fois toutes les manipulations effectuées, il faut renvoyer les données dans Excel.

     query
    <clic g>      sur

     

    Enregistrer la requête en cours sous le nom "req2" dans le même dossier que les exercices puis renvoyer les données vers Excel

    5.         LE CUBE OLAP

    Le cube Olap est une structure de base de données intelligente et pluridimensionnelle permettant de travailler sur une partie des données comme dans un sous-ensemble de la base. Excel permet d'utiliser un cube existant.


    groupe "données externes"     (1er bloc)

    <clic g>  sur  
    <clic g>      sur
    onglet     ,  <clic g>  sur
    sélectionner     le fichier "cube"
     puis  pour valider
    la source de données OLAP s'affiche dans la liste
    la sélectionner
     pour importer
    <faire glisser> les champs du tableau croisé à leurs positions respectives

    6.         LES AUTRES SOURCES D'EXTRACTION

    Il est aussi possible d'extraire des données à partir du Web ; les données affichées sous forme de "table" sur la page peuvent être récupérées mais les données de la base liée (mysql ou autre) ne peuvent évidemment pas être directement importées.


    groupe "données externes"     (1er bloc)

    <clic g>  sur  
    saisir     l'adresse du site puis
    <clic g>      sur  pour cocher  les tables (    au sens internet de "tableau")
    les tables cochées ont le signe  à la place de
    <clic g>  sur  pour importer
     pour valider

    Effectuez auparavant une recherche avec votre navigateur préféré puis copier/coller l'adresse internet dans la boîte de dialogue précédente

    Le résultat peut être tout à fait satisfaisant pour les tableaux de type "liste" qu'il suffit alors de mettre en forme.

     

    Ne pas utiliser le mode "Mettre sous forme de tableau" sous peine de rompre la connexion