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
Modifié le: lundi 28 octobre 2013, 15:52