Utiliser Microsoft Query pour extraire des données externes

Microsoft Query vous permet de vous connecter à des sources de données externes, de sélectionner des données à partir de ces sources externes, d’importer ces données dans votre feuille de calcul et d’actualiser les données au besoin pour assurer la synchronisation des données de votre feuille de calcul avec celles des sources externes.

Types de bases de données accessibles     Vous pouvez récupérer des données à partir de plusieurs types de bases de données, notamment Microsoft Office Access, Microsoft SQL Server et Microsoft SQL Server services OLAP. Vous pouvez également récupérer des données à partir de Excel et des fichiers texte.

Microsoft Office fournit des pilotes que vous pouvez utiliser pour récupérer des données à partir des sources de données suivantes :

• Microsoft SQL Server Analysis Services (fournisseur OLAP )

• Microsoft Office Access

• dBASE

• Microsoft FoxPro

• Microsoft Office Excel

• Oracle

• Paradox

• Bases de données de fichiers texte

Vous pouvez également utiliser des pilotes ODBC ou des pilotes de sources de données d’autres fabricants pour récupérer des informations à partir de sources de données qui ne sont pas répertoriées ici, y compris d’autres types de bases de données OLAP. Pour plus d’informations sur l’installation d’un pilote ODBC ou d’un pilote de source de données qui n’est pas répertorié ici, consultez la documentation relative à la base de données ou contactez votre fournisseur de base de données.

Sélection de données à partir d’une base de données     Vous récupérez des données d’une base de données en créant une requête (question que vous posez sur les données stockées dans une base de données externe). Par exemple, si vos données sont stockées dans une base de données Access, vous souhaitez peut-être connaître les chiffres de ventes d’un produit spécifique par région. Vous pouvez récupérer une partie des données en sélectionnant uniquement les données du produit et de la région que vous voulez analyser.

Microsoft Query vous permet de sélectionner les colonnes de données et d’importer uniquement ces données dans Excel.

Mise à jour de votre feuille de calcul en une seule opération     Une fois que vous avez des données externes dans un Excel, chaque fois que votre base de données change, vous pouvez actualisation les données pour mettre à jour votre analyse, sans avoir à re créer vos rapports de synthèse et graphiques. Par exemple, vous pouvez créer un récapitulatif des ventes mensuelles et l’actualiser chaque mois lorsque les nouveaux chiffres de ventes entrent en ligne.

Utilisation des sources de données par Microsoft Query     Après avoir installé une source de données pour une base de données spécifique, vous pouvez l’utiliser chaque fois que vous voulez créer une requête pour sélectionner et extraire des données à partir de cette base de données, sans avoir à retaper toutes les informations de connexion. Microsoft Query utilise la source de données pour se connecter à la base de données externe et vous montrer les données disponibles. Après avoir créé votre requête et renvoyé les données à Excel, Microsoft Query fournit le Excel avec les informations de la requête et de la source de données afin que vous pouvez vous reconnecter à la base de données lorsque vous voulez actualiser les données.

Utilisation de Microsoft Query pour importer des données     pour importer des données externes dans Excel’aide de Microsoft Query, suivez ces étapes de base, décrites plus en détail dans les sections suivantes.

Qu’est-ce qu’une source de données ?     Une source de données est un ensemble d’informations stocké qui permet à Microsoft Query et Excel se connecter à une base de données externe. Lorsque vous utilisez Microsoft Query pour configurer une source de données, vous donnez un nom à la source de données, puis fournissez le nom et l’emplacement de la base de données ou du serveur, le type de base de données, ainsi que vos informations d' logo et de mot de passe. Ces informations incluent également le nom d’un pilote OBDC ou d’un pilote de source de données, programme qui connecte un type spécifique de base de données.

Pour configurer une source de données à l’aide de Microsoft Query :

1. Sous l’onglet Données, dans le groupe Obtenir des données externes, cliquez sur Autres sources,puis sur À partir de Microsoft Query.

2. Effectuez l’une des opérations suivantes :

   • Pour spécifier une source de données pour une base de données, un fichier texte ou un Excel de données, cliquez sur l’onglet Bases de données.

   • Pour spécifier une source de données de cube OLAP, cliquez sur l’onglet Cubes OLAP. Cet onglet est disponible uniquement si vous avez mis en place Microsoft Query à partir Excel.

3. Double-cliquez sur <nouvelle source de>.

   -ou-

   Cliquez <la nouvelle source>données, puis cliquez sur OK.

   La boîte de dialogue Créer une source de données s’affiche.

4. À l’étape 1, tapez un nom pour identifier la source de données.

5. À l’étape 2, cliquez sur un pilote pour le type de base de données que vous utilisez comme source de données.

   Remarques : 

   • Si la base de données externe à partir de qui vous voulez accéder n’est pas prise en charge par les pilotes ODBC installés avec Microsoft Query, vous devez obtenir et installer un pilote ODBC compatible Microsoft Office auprès d’un fournisseur tiers tel que le fabricant de la base de données. Pour obtenir des instructions d’installation, contactez le fournisseur de la base de données.

   • Les bases de données OLAP ne nécessitent pas de pilotes ODBC. Lorsque vous installez Microsoft Query, des pilotes sont installés pour les bases de données créées à l’aide de Microsoft SQL Server Analysis Services. Pour vous connecter à d’autres bases de données OLAP, vous devez installer un pilote de source de données et un logiciel client.

6. Cliquez Connectersur votre source de données, puis fournissez les informations nécessaires à la connexion à votre source de données. Pour les bases de Excel, les Excel et les fichiers texte, les informations que vous fournissez dépendent du type de source de données que vous avez sélectionné. Vous pouvez être invité à fournir un nom d’utilisateur, un mot de passe, la version de la base de données que vous utilisez, l’emplacement de la base de données ou d’autres informations spécifiques au type de base de données.

   Important : 

   • Utilisez un mot de passe fort qui associe des lettres majuscules et minuscules, des nombres et des symboles. Les mots de passe faibles ne combinent pas ces éléments. Mot de passe fort : Y6dh!et5. Mot de passe faible : Maison27. Les mots de passe doivent compter au moins 8 caractères. Nous vous conseillons d’en utiliser au moins 14.

   • Il est très important de ne pas oublier votre mot de passe. Si cela se produit, Microsoft ne pourra pas le récupérer. Stockez les mots de passe que vous écrivez dans un endroit sûr, éloigné des informations qu’ils sont censés protéger.

7. Après avoir entré les informations requises, cliquez sur OK ou sur Terminer pour revenir à la boîte de dialogue Créer une source de données.

8. Si votre base de données contient des tables et que vous souhaitez qu’une table spécifique s’affiche automatiquement dans l’Assistant Requête, cliquez sur la zone de l’étape 4, puis sur la table de votre souhaitez.

9. Si vous ne voulez pas taper votre nom d’utilisateur et votre mot de passe lorsque vous utilisez la source de données, cochez la case Enregistrer mon ID utilisateur et mon mot de passe dans la case à cocher Définition de la source de données. Le mot de passe enregistré n’est pas chiffré. Si la case à cocher n’est pas disponible, consultez votre administrateur de base de données pour déterminer si cette option peut être mise à disposition.

   Note de sécurité : Évitez d’enregistrer les informations d’connexion lors de la connexion à des sources de données. Ces informations peuvent être stockées en texte simple et un utilisateur malveillant pourrait accéder à ces informations afin de compromettre la sécurité de la source de données.

Une fois ces étapes terminées, le nom de votre source de données s’affiche dans la boîte de dialogue Choisir la source de données.

Utiliser l’Assistant Requête pour la plupart des requêtes     L’Assistant Requête facilite la sélection et l’ensemble des données de différentes tables et champs de votre base de données. À l’aide de l’Assistant Requête, vous pouvez sélectionner les tables et les champs que vous voulez inclure. Une jointeur interne (opération de requête qui spécifie que les lignes de deux tables sont combinées en fonction de valeurs de champ identiques) est créée automatiquement lorsque l’Assistant reconnaît un champ de clé primaire dans une table et un champ du même nom dans une deuxième table.

Vous pouvez également utiliser l’Assistant pour trier le jeu de résultats et filtrer simplement. Dans la dernière étape de l’Assistant, vous pouvez choisir de renvoyer les données à Excel, ou d’affiner davantage la requête dans Microsoft Query. Après avoir créé la requête, vous pouvez l’exécuter dans Excel ou Microsoft Query.

Pour démarrer l’Assistant Requête, effectuez les étapes suivantes.

1. Sous l’onglet Données, dans le groupe Obtenir des données externes, cliquez sur Autres sources,puis sur À partir de Microsoft Query.

2. Dans la boîte de dialogue Choisir la source de données, vérifiez que la case à cocher Utiliser l’Assistant Requête pour créer/modifier des requêtes est sélectionnée.

3. Double-cliquez sur la source de données à utiliser.

   -ou-

   Cliquez sur la source de données à utiliser, puis sur OK.

Travaillez directement dans Microsoft Query pour d’autres types de requêtes     Si vous voulez créer une requête plus complexe que ce que l’Assistant Requête vous autorise, vous pouvez travailler directement dans Microsoft Query. Vous pouvez utiliser Microsoft Query pour afficher et modifier les requêtes que vous commencez à créer dans l’Assistant Requête, ou vous pouvez créer des requêtes sans utiliser l’Assistant. Travaillez directement dans Microsoft Query lorsque vous voulez créer des requêtes qui font les opérations suivantes :

• Sélectionner des données spécifiques d’un champ     Dans une base de données de grande taille, vous pouvez choisir certaines des données d’un champ et omettre les données dont vous n’avez pas besoin. Par exemple, si vous avez besoin de données pour deux produits dans un champ qui contient des informations sur plusieurs produits, vous pouvez utiliser critères pour sélectionner les données des deux produits de votre choix.

• Extraire des données sur la base de différents critères chaque fois que vous exécutez la requête     Si vous devez créer le même rapport Excel ou une synthèse pour plusieurs zones dans les mêmes données externes (par exemple, un rapport des ventes distinct pour chaque région), vous pouvez créer un requête avec paramètres. Lorsque vous exécutez une requête Paramètre, vous êtes invité à utiliser une valeur comme critère lorsque la requête sélectionne des enregistrements. Par exemple, une requête Paramètre peut vous inviter à entrer une région spécifique, et vous pouvez la réutiliser pour créer chacun de vos rapports de ventes régionaux.

• Joindre des données de différentes manières     Les jointilles internes créées par l’Assistant Requête sont les plus courantes utilisées dans la création de requêtes. Toutefois, il peut se peut que vous vouliez utiliser un autre type de jointe. Par exemple, si vous avez une table d’informations sur les ventes de produits et une table d’informations sur les clients, une jointie interne (type créé par l’Assistant Requête) empêchera l’extraction des enregistrements client pour les clients qui n’ont pas effectué d’achat. Microsoft Query vous permet de joindre ces tables afin que tous les enregistrements client soient récupérés, ainsi que les données de ventes pour les clients qui ont effectué des achats.

Pour démarrer Microsoft Query, effectuez les étapes suivantes.

1. Sous l’onglet Données, dans le groupe Obtenir des données externes, cliquez sur Autres sources,puis sur À partir de Microsoft Query.

2. Dans la boîte de dialogue Choisir la source de données, vérifiez que la case à cocher Utiliser l’Assistant Requête pour créer/modifier des requêtes est claire.

3. Double-cliquez sur la source de données à utiliser.

   -ou-

   Cliquez sur la source de données à utiliser, puis sur OK.

Réutilisation et partage de requêtes     Dans l’Assistant Requête et dans Microsoft Query, vous pouvez enregistrer vos requêtes en tant que fichier .dqy que vous pouvez modifier, réutiliser et partager. Excel peut ouvrir des fichiers .dqy directement, ce qui vous permet, à vous ou à d’autres utilisateurs, de créer des plages de données externes supplémentaires à partir de la même requête.

Pour ouvrir une requête enregistrée à partir de Excel :

1. Sous l’onglet Données, dans le groupe Obtenir des données externes, cliquez sur Autres sources,puis sur À partir de Microsoft Query. La boîte de dialogue Choisir la source de données s’affiche.

2. Dans la boîte de dialogue Choisir la source de données, cliquez sur l’onglet Requêtes.

3. Double-cliquez sur la requête enregistrée que vous voulez ouvrir. La requête s’affiche dans Microsoft Query.

Si vous voulez ouvrir une requête enregistrée et que Microsoft Query est déjà ouvert, cliquez sur le menu Fichier de requête Microsoft, puis sur Ouvrir.

Si vous double-cliquez sur un fichier .dqy, Excel s’ouvre, exécute la requête, puis insère les résultats dans une nouvelle feuille de calcul.

Si vous souhaitez partager un résumé ou un rapport Excel basé sur des données externes, vous pouvez donner à d’autres utilisateurs un document contenant une plage de données externe ou créer une modèle. Un modèle vous permet d’enregistrer le résumé ou le rapport sans enregistrer les données externes de sorte que le fichier soit plus petit. Les données externes sont récupérées lorsqu’un utilisateur ouvre le modèle de rapport.

Après avoir créé une requête dans l’Assistant Requête ou Microsoft Query, vous pouvez renvoyer les données dans Excel calcul. Les données deviennent alors un plage de données externes ou un rapport de tableau croisé dynamique que vous pouvez formater et actualiser.

Mise en forme des données récupérées     Dans Excel, vous pouvez utiliser des outils, tels que des graphiques ou des sous-totaux automatiques, pour présenter et synthétiser les données récupérées par Microsoft Query. Vous pouvez formater les données, et votre mise en forme est conservée lorsque vous actualisez les données externes. Vous pouvez utiliser vos propres étiquettes de colonne à la place des noms de champs et ajouter automatiquement des numéros de lignes.

Excel mettre en forme automatiquement les nouvelles données que vous tapez à la fin d’une plage pour qu’elles correspondent aux lignes précédentes. Excel peuvent également copier automatiquement des formules répétées dans les lignes précédentes et les étendre à d’autres lignes.

Vous pouvez activer cette option (ou la désactiver) à tout moment :

1. Cliquez sur Fichier > Options > Options avancées.

   Dans Excel 2007: Cliquez sur Microsoft Office bouton , cliquez Excel Optionsavancées, puis cliquez sur la catégorie Options avancées.

2. Dans la section Options d’édition, sélectionnez la vérification des formats et formules des plages de données Étendue. Pour désactiver la mise en forme automatique des plages de données, cochez cette case.

Actualisation des données externes     Lorsque vous actualisez des données externes, vous exécutez la requête pour récupérer les données nouvelles ou modifiées qui correspond à vos spécifications. Vous pouvez actualiser une requête dans Microsoft Query et dans Excel. Excel propose plusieurs options pour l’actualisation des requêtes, notamment l’actualisation des données chaque fois que vous ouvrez le workbook et l’actualisation automatique à intervalles réguliers. Vous pouvez continuer à travailler dans Excel pendant que les données sont actualisées, et vous pouvez également vérifier l’état pendant l’actualisation des données. Pour plus d’informations, voir Actualiser une connexion de données externes dans Excel.

Haut de la page