1. EXTRACTION BASE ACCESS

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


 onglet "données"
groupe "données externes"  (1er bloc)
<clic g> sur
sélectionner le fichier
<clic g>   sur
accepter le mode
 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


 Il est aussi toujours possible de choisir "base de données Access" en cliquant sur  de  dans la boîte de dialogue d'ouverture d'Excel



 Excel ouvre les fichiers au format . dbf ( dbase : ancien standard d'échange de donnée) ou xml (standard actuel). Pour ouvrir dans Excel un fichier provenant d'une base de données autre qu'Access, utiliser ces formats.


  si le fichier à ouvrir est un fichier de base de données mais n'est pas au format "dbf" ou "xml"
ouvrir l'application d'origine des données puis le fichier contenant les données
" enregistrer sous" ce fichier au format "xml" ou "dbf" puis fermer l'application d'origine
Ouvrir le fichier "dbf" sous Excel


 


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



2. EXTRACTION AVANCÉE 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.


 onglet "données"
groupe "données externes"  (1er bloc)
<clic g> sur  
<clic g> sur  
"nouvelle source de données" sélectionné
 pour valider



a) CRÉATION 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) CONNEXION 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
sélectionner une table par défaut
 pour créer la source de données



La connexion est établie.



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



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


c) CRÉATION 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 table "clients" de la base access.accdb pour les champs " société", "contact", "adresse" et "cdpostal" puis enregistrer sous le nom "req"



d) MODIFICATION 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.


 onglet "données"
groupe "connexions"  (3è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 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. EXTRACTION 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) INSTALLATION 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
Une fois ce pilote installé, vous pourrez intervenir directement et de manière très souple avec Excel(copier/coller, recopie, recherche/remplacement…) sur les données d'une base locale mysql ( gérée par Microsoft webMatrix ou WampServer)


b) CRÉATION 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

<double clic> sur  
 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  (installé précédemment)
paramétrer la connexion
<clic g> sur


    


c) CONNEXION 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.


  paramétrage de la connexion
saisir un nom pour la source
renseigner le serveur tcpip (si local : localhost )
renseigner l'utilisateur (si local : root )
renseigner éventuellement le mot de passe
indiquer la base de données
<clic g>  sur  pour tester le lien


 


 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) 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   ou

 
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é. S'assurer cependant de bien être sur le site de l'éditeur sous peine de télécharger en plus des logiciels non désirés


4. EXTRACTION AVANCÉE 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 REQUÊTE

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


 onglet "données"
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 l'ouverture de la requête, il peut arriver qu'Excel ne trouve plus le chemin de la base ou 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 alors la requête (éventuellement en la décomposant ou directement en sql afin qu'Excel la comprenne )


 Ouvrir la requête "requete_source1.dqy" créée précédemment avec Query ; 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 CRITÈRES

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


  fenêtre 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 2016 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
Enregistrer la requête en cours sous le nom "requete_source2.dqy "



 Penser à cliquer éventuellement sur  pour actualiser les enregistrements affichés


c) AJOUTER UNE TABLE

L'opération est simple.


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


d) RENVOYER VERS EXCEL

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


  fenêtre query
<clic g>sur


 Renvoyer les données de la requête en cours vers Excel, enregistrer et fermer le classeur



5. EXTRACTION PAR REQUÊTE

Excel peut charger les données extraites à partir d'une requête portant sur des sources de données très variées.


a) CHARGEMENT DES DONNÉES

Les données sont alors chargées directement dans Excel.


  onglet "données"
groupe "récupérer et transformer"  (2ème bloc)
<clic g> sur  
<clic g> sur
sélectionner le format de la base
sélectionner le fichier
<clic g> sur
sélectionner la table ou la requête
<clic g> sur  pur insérer les données dans Excel



   Charger dans un nouveau classeur les données de "vente de produits par catégorie et par date" de Northwind 2016ios.accdb :  et les afficher


b) ÉDITEUR DE REQUÊTES

Il est alors possible de traiter les données dans l'éditeur de requête avant de les charger.


 onglet "données"
groupe "récupérer et transformer"  (2ème bloc)
<clic g> sur  
<clic g> sur
sélectionner le format de la base
sélectionner le fichier
<clic g> sur
sélectionner la table ou la requête
ou
<clic g> sur pour éditer la requête
l'éditeur de requêtes s'affiche
modifier éventuellement la requête
de nombreuses modifications peuvent êtes exécutées ici mais moins simplement que dans Excel (changer les valeurs, modifier les formats, ajouter des colonnes, faire un tableau croisé…)
 puis <clic g> sur



 Sur une autre feuille du classeur en cours, créer une requête extrayant les données de "vente de produits par catégorie et par date" de Northwind 2016ios.accdb, les afficher dans l'éditeur de requêtes, les charger dans Excel puis enregistrer


 Dans Excel, un <double clic> sur la requête dans le volet droit l'affiche dans l'éditeur de requête pour modification



6. AUTRES SOURCES

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 ne peuvent évidemment pas être directement importées.


 onglet "donné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 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 mais il peut aussi y avoir un "nettoyage" important à faire…


 Importer les données à partir du lien suivant :
http://ark.intel.com/fr/products/family/88393/6th-Generation-Intel-Core-i5-Processors#@Desktop
afficher les propriétés de la connexion avec l'outil
ne laisser que les données utiles
enregistrer



   permet de récupérer des données à jour mais fait perdre un éventuel "nettoyage" effectué

Modifié le: mardi 23 avril 2019, 10:45