B. EXTRACTION DE DONNÉES

B.             EXTRACTION DE DONNÉES

Il faut alors créer un classeur vierge et importer les données dans la feuille de travail.

Il est aussi toujours possible d'ouvrir le fichier directement en cliquant sur de dans la boîte de dialogue d'ouverture d'Excel

 

1.                L'EXTRACTION DE DONNÉES AU FORMAT XML

Le format .xml est un format d’échange (données et/ou structure) entre applications récentes qui permet de leur conserver la quasi-totalité de leurs caractéristiques.  Préférer ce format à tout autre si l’application source des données est assez récente pour le permettre.

ONGLET "DONNÉES"
groupe "récupérer et transformer des données" (1er bloc)
<clic g> sur
sélectionner
puis
sélectionner le fichier
<clic g>  sur
sélectionner les donnnées
<clic g>  sur
les données s'affichent dans la feuille

 

 

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

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

ONGLET "DONNÉES"
groupe "récupérer et transformer des données" (1er bloc)
<clic g> sur
sélectionner
puis
sélectionner le fichier
<clic g>  sur
sélectionner les donnnées
<clic g>  sur
les données s'affichent dans la feuille
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"

 

3.                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.

ONGLET "DONNÉES"
groupe "récupérer et transformer des données" (1er bloc)
<clic g> sur
sélectionner
<clic g> sur
"nouvelle source de données" sélectionné
pour valider

 

a)                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)               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
<clic g>  sur pour sélectionner une table
pour créer la source de données

 

 

La connexion est établie.

 

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

c)                 CREATION DE LA REQUÊTE

La source créée, il faut la désigner pour pouvoir importer les données.

FENÊTRE QUERY
désigner la source
pour valider

 

 

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

 

 

  ASSISTANT REQUÊTE
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
Créer une source de données à partir de la base "Northwind 2021.accdb"
Extraire de la table "clients" de la source créée les champs "société", "nom", "adresse" et "ville"puis enregistrer sous le nom "req_clients"

 

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 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.

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 puis <clic g> sur
ou
<clic g> sur
puis <double clic> sur

 

 

ONGLET "DONNÉES"
groupe "connexions" (3ème bloc)
<clic g> sur l'onglet
<clic g> sur
la requête s'affiche dans l'assistant pour modification

 

 

Modifier la requête et ne prendre que les enregistrements de "Marseille" et "Brest"
Trier par "société" dans l'ordre croissant

 

 

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
<clic g>  sur
<clic g>  sur

 

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 " source_2021_clients_ville.dqy " dans le même dossier que les exercices puis fermer le classeur

 

Le chemin d'enregistrement par défaut des requêtes est :
 

f)                 MODIFIER LA SOURCE DE DONNÉES

Il arrive souvent qu'au bout d'une certain temps, il n'y ait plus de correspondance entre le fichier Excel et la source des données :

• Fichier source supprimé
• Fichier source déplacé
• Fichier source renommé
• …

 

Heureusement, il est possible de recréer un lien avec ce fichier source ou un fichier ayant exactement la même structure.

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 puis <clic g> sur
ou
<clic g> sur
puis <double clic> sur la connexion
Un message s'affiche indiquant l'impossibilité de trouver le fichier source
<clic g> sur
<clic g> sur

 

 

  BOÎTE CONNEXION
séléctionner la base de données
pour valider
les données actualisées s'affichent

 

4.                L'EXTRACTION AVANCÉE AVEC MICROSOFT QUERY

Microsoft Query permet d'ouvrir les requêtes et connexion enregistrées précédemment avec le suffixe .dqy et 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.

ONGLET "DONNÉES"
groupe "récupérer et transformer des données" (1er bloc)
<clic g> sur
sélectionner
<clic g> sur
onglet
<clic g> sur
sélectionner la requête
puis
pour valider
dans la dernière étape de l'assistant, choisir

 

 

Un fichier  comme "source_"2021_clients_ville.dqy" est un fichier texte avec un certain nombre d'informations codifiées pouvant être modifié avec un éditeur comme "notepad+"

 

 

 

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 DES CRITÈRES

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

FENÊTRE QUERY
indiquer le nouveau critère dans la zone de critères
ou
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

 

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

 

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 2021 ios.accdb"
Afficher la table "employes", et extraire :
- les employés habitant une ville dont le code postal est strictement supérieur ou égale à 50000 et inférieur à 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 "

 

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 LES DONNÉES 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.                L'EXTRACTION PAR REQUÊTE - POWERQUERY

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

a)                REQUÊTE IMMÉDIATE

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

ONGLET "DONNÉES"
groupe "récupérer et transformer des données" (1er bloc)
<clic g> sur
sélectionner
<clic g> sur
la fenêtre PowerQuery s'affiche
<clic g>  sur puis sur
sélectionner puis le fichier
<clic g> sur
sélectionner la table ou la requête
pour insérer les données
<clic g>  sur pour charger 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 2021ios.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 des données" (1er bloc)
<clic g> sur
sélectionner
<clic g> sur
la fenêtre Powerpivot s'affiche
<clic g>  sur
<clic g>  sur
sélectionner
sélectionner le fichier
<clic g> sur
sélectionner la table ou la requête
pour insérer les données dans Excel
<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, extraire les données de "vente de produits par catégorie et par date" de Northwind 2021ios.accdb, les regrouper par catégories en comptant les lignes puis les charger dans Excel et les enregistrer

 

Dans Excel, pointer sur la requête pour l'afficher et <double clic> dans le volet droit pour l'éditer

c)                 FUSION ET COMBINAISON DE REQUÊTES

Il peut être utile de combiner plusieurs requêtes pour obtenir des informations. Il faut cependant d'abord avoir créé les requêtes dans Excel comme dans les étapes précédentes.

ONGLET "DONNÉES"
groupe "récupérer et transformer des données" (1er bloc)
<clic g> sur
sélectionner
<clic g> sur
sélectionner la première table, la deuxiéme table puis le champ de jointure
pour valider
le résultat de la fusion s'affiche dans Powerquery

 

 

Reste à sélectionner les colonnes de la seconde table, à supprimer les colonnes inutiles et à renvoyer les données vers Excel.

POWERQUERY
groupe "récupérer et transformer des données" (1er bloc)
<clic g>  sur à droite de la tête de colonne de la deuxième table
décocher les colonnes à ne pas prendre en compte
sélectionner les colonnes à ne pas prendre en compte
appuyer sur <supp> du clavier pour les supprimer
bien conserver le champ
<clic g>  sur pour accepter l'insertion d'une nouvelle étape
<clic g> sur
les données modifiées s'affichent dans une nouvelle feuille de travail dans Excel

 

Nommer l'onglet de la feuille de travail comme la requête

Les données sont alors manipulables dans Excel avec toutes les informations requises

 

Et les requêtes sont affichées dans le volet droit.

 

Afin d'avoir l'adresse de clients ayant commandé, je crée une première requête à partir de la table de Northwind2021ios.accdb ou se trouvent les commandes


Je crée une deuxième requête à partir de la table de Northwind2021ios.accdb ou se trouve l'adresse des clients


je fusionne les deux requêtes par le champ société
 

 

Reproduire l'exemple ci-dessus
Dans le même classeur, joindre les tables "produits" et "détail commandes" de Northwind2021ios.accdb afin d'afficher les noms des produits et des catégories par commande dans Excel comme dans le tableau ci-dessous

 

6.                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)                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 https://dev.mysql.com/downloads/connector/odbc/
Télécharger le pilote Windows (x86, 32/64-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 un serveur local sousWindows)

 

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

<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 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.

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. 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 "récupérer et transformer des données" (1er bloc)

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

 

 

Pour trouver le driver odbc d'un type de base de données, aller sur le site de l'éditeur mais s'assurer de bien être sur ce site sous peine de télécharger en plus des logiciels non désirés

7.                LES AUTRES SOURCES D'EXTRACTION

De nombreuses autres sources d'extraction sont disponibles

a)                À PARTIR DU WEB

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 "récupérer et transformer des données" (1er bloc)
<clic g> sur
copier/coller l'adresse du site puis
<clic g> sur la table (au sens internet de "tableau")
<clic g> sur
les données sont chargées dans excel et peuvent y être modifiées soit directement soit en revenant dans Powerquery
<double clic> sur la requête
supprimer les donnnées inutiles
<clic g>  sur

 

 

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
Ne laisser que les données utiles
Enregistrer

 

 

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

b)               À PARTIR D'UN TABLEAU

Plutôt que d'utiliser les fonctionnalités de Excel, il est possible d'utiliser dans un gros tableau les fonctionnalités de PowerQuery.

ONGLET "DONNÉES"
groupe "récupérer et transformer des données" (1er bloc)
tableau de données affiché et une cellule du tableau étant sétectionnée
<clic g> sur
les données s'affichent dans Powerquery
effectuer la ou les opérations de votre choix
<clic g>  sur
les données modifiées s'affichent dans une nouvelle feuille de travail dans Excel

 

Ouvrir le fichier "base2.xlsx"
Afficher les données dans PowerQuery
Regrouper les données par région et par ville en faisant la somme des ventes
Charger les données modifiées dans Excel

 

 

Les données sont modifiées dans PowerQuery

 

Puis chargées dans Excel

 

c)                 À PARTIR D'AUTRES SOURCES

De nombreuses autres sources de données sont disponibles, permettant de traiter la plupart des  données d'une entreprise quelque soient leur origine.

• 
  C’est-à-dire d'un fichier JavaScript Object Notation (JSON) qui est un format de données textuelles dérivé de la notation des objets du langage JavaScript. Il permet de représenter de l'information structurée comme le permet XML par exemple.
• 
  SQL Server est la base de données de Microsoft permettant de gérer des données de gros volumes.
• 
  Analysis Services est un moteur de données analytiques utilisé dans l’aide à la décision et l’analyse. Il fournit des fonctionnalités de modèle de données sémantiques pour les applications décisionnelles (BI), l’analyse des données et la création de rapports, telles que Power BI, Excel, Reporting Services et autres. Analysis Services est disponible sur différentes plateformes
• 
  
  version d'analysis services pour Sql Server
• 
  Azure est la plate-forme de services "Cloud" de Microsoft