Comment Exporter des Données de SQL Server vers Excel

Comment Exporter des Données de SQL Server vers Excel

Dans un article précédent, SQL et Excel: Pourquoi vous avez besoin des deux, nous avons montré que pour de nombreuses entreprises, l’utilisation de SQL Server et d’Excel est une approche optimale de la gestion des données. Pour les organisations qui utilisent SQL Server et Excel de manière intensive, il est souvent nécessaire d’exporter des données de SQL Server vers Excel. Les raisons les plus courantes pour obtenir les données dans Excel sont les suivantes::

• produire des visualisations de données telles que des tableaux et des graphiques à utiliser dans des rapports
• agréger ou analyser des données dans des tableaux croisés dynamiques
• rassembler des données provenant de sources multiples pour une transformation ultérieure
• rendre les données accessibles aux utilisateurs non-bases de données pour les visualiser ou les traiter

Dans cet article, nous allons examiner comment pour exporter des données de SQL Server vers Excel. La première méthode utilise l’Assistant d’importation et d’exportation SQL Server, qui peut être utilisé pour exporter des données vers un certain nombre de types de destination, pas seulement Excel. La deuxième méthode utilise des fonctionnalités natives dans Excel. La troisième méthode utilise le complément SQL Spreads pour Excel, qui fournit des fonctionnalités au-delà du processus d’exportation. Il y a des avantages et des inconvénients de chaque méthode, que nous résumerons à la fin de l’article.

Méthode (1) : Exportation à l’aide de l’Assistant d’importation et d’exportation SQL Server

L’Assistant d’importation et d’exportation SQL Server peut être lancé depuis SQL Server Management Studio ou en tant qu’application autonome.

Depuis SQL Server Management Studio, cliquez sur la base de données à partir de laquelle vous souhaitez exporter les données. Nous utilisons la base de données de démonstration dans notre exemple, alors cliquez dessus, puis faites un clic droit et sélectionnez Tâches > Exporter les données.

La page d’accueil s’affiche, et vous pouvez continuer et cliquer sur ‘Suivant’.

Sur l’écran suivant, sélectionnez la source de données. Pour exporter à partir de SQL Server, sélectionnez  » Client natif SQL Server « .

Une fois que vous avez sélectionné ‘Client natif SQL Server’, les détails sur la source de données seront renseignés par l’assistant.

Cliquez sur ‘Suivant’ pour passer à l’écran suivant, où vous allez choisir la destination de l’exportation, qui pour nous est Excel.

Lorsque vous sélectionnez Excel comme destination de choix, vous devez ensuite indiquer l’emplacement du fichier Excel. Lorsque vous cliquez ensuite sur « Suivant », vous pouvez voir le message suivant:

L’opération n’a pas pu être terminée.
INFORMATIONS SUPPLÉMENTAIRES:
Le ‘Microsoft.ACE.OLEDB.16.0’ le fournisseur n’est pas enregistré sur la machine locale. (Système.Données)

La raison pour laquelle ce message apparaît est que lorsque vous lancez l’Assistant d’importation et d’exportation SQL Server à partir de SSMS, une version 32 bits de l’assistant est lancée (car SSMS est 32 bits) et votre système d’exploitation est 64 bits. Il y a plus d’informations à ce sujet ici.

Pour contourner ce problème, vous devez fermer SSMS et accéder au menu Démarrer de Windows et rechercher la version 64 bits de l’Assistant d’importation et d’exportation SQL Server dans le menu Démarrer. Notez que cette version ne sera installée sur votre ordinateur que si vous avez installé SQL Server.

Une fois que la version autonome de l’Assistant a démarré, vous pouvez suivre les étapes décrites jusqu’à présent, qui consistent à spécifier la source et la destination de la copie de données. Nous devons maintenant définir ce que nous voulons copier et (éventuellement) configurer l’opération de copie.

Après avoir cliqué sur  » Suivant  » sur l’écran  » Choisir une destination « , vous spécifiez si vous souhaitez exporter une ou plusieurs tables ou vues complètes, ou un sous-ensemble de données via une requête SQL. Dans notre cas, nous exportons à partir d’une seule table, nous pouvons donc laisser l’option par défaut sélectionnée.

Ensuite, nous allons modifier les mappages de colonnes en cliquant sur le bouton « Modifier les mappages… ». L’écran Mappages de colonnes vous permet d’apporter des modifications au nom et au type de données des colonnes qui seront créées dans Excel par l’opération de copie. Dans notre exemple, nous allons changer le type de données de la colonne ‘order_date’ de VarChar en DateTime. Nous laisserons les autres comme valeur par défaut définie par l’assistant. Cliquez sur  » Suivant « . L’écran suivant de l’assistant résume que la source est une table SQL appelée sales et que la destination sera une feuille appelée ‘sales’ dans le classeur Excel. Vous pouvez modifier le nom de la feuille au besoin.

Une fois cela fait, cliquez sur ‘Ok’ pour fermer la boîte de dialogue, puis sur ‘Suivant’.

Si l’un des mappages que nous avons spécifiés peut ne pas réussir dans le processus d’exportation, l’Assistant Importation et exportation de SQL Server affiche la page de mappage de type de données d’examen. Cette page met en évidence les conversions que l’assistant doit effectuer pour gérer les mappages que vous avez spécifiés. Il indique également ce que l’assistant fera s’il y a une erreur lors de la conversion. Il peut soit ignorer l’erreur, soit provoquer l’échec de l’exportation. Dans notre exemple, plusieurs colonnes ont été marquées: celles où il y a une conversion de nvarchar(max) en son équivalent Excel, Texte long, et aussi la conversion de VarChar en DateTime pour la colonne ‘order_date’. Le paramètre par défaut est que l’assistant échoue en cas d’erreur, nous laisserons donc cette option en place.

Remarque : Cet Assistant d’importation et d’exportation SQL utilise SSIS sous le capot ; en d’autres termes, chaque fois que vous exécutez l’assistant, vous créez un package SSIS. C’est pourquoi vous avez la possibilité de l’enregistrer pour une utilisation future.Cliquez sur  » Suivant « . L’assistant vous offre maintenant la possibilité d’enregistrer la configuration de l’Assistant en tant que package SSIS en plus de l’option par défaut d’exécuter la copie immédiatement.

Nous allons simplement laisser l’option par défaut cochée, puis cliquer sur le bouton « Suivant ».

L’assistant affiche maintenant un résumé des étapes que nous avons configurées.

Cliquez sur « Terminer », et l’assistant affichera maintenant l’écran final, qui est le résultat de l’exécution. Dans notre cas, l’exécution a réussi et nous pouvons voir que 5484 enregistrements ont été copiés dans la feuille « Requête » de notre classeur Excel.

Nous pouvons vérifier que les données ont été exportées de SQL Server vers Excel en ouvrant le fichier que nous avons spécifié et en vérifiant la feuille ‘ventes’.

Maintenant que les données sont dans Excel, nous pouvons créer des visualisations ou des agrégations, ou tout autre traitement que nous voulons effectuer. Par exemple, nous pourrions créer un tableau croisé dynamique pour afficher le total par élément et par région.

Méthode (2): Exportation à l’aide de fonctionnalités natives dans Excel

Bien que la méthode elle-même soit à peu près la même, Microsoft a apporté des améliorations dans les dernières versions de la façon dont vous obtenez des données provenant d’autres sources dans Excel. Le principal changement a été l’ajout des assistants et outils d’importation de données Power Query en tant que composant natif dans Excel 2016. Ceux–ci sont accessibles à partir de l’éditeur Power Query, bien qu’il ne soit pas affiché en tant que tel sur le ruban Excel – à la place, il est étiqueté comme Groupe de données Get& Transform dans l’onglet Données du ruban Excel.

Les étapes décrites ci-dessous sont valables pour Excel 2016 et au-delà.

Pour commencer l’exportation de SQL Server vers Excel, cliquez sur Data > Get Data > From Database > From SQL Server Database.

Vous êtes ensuite invité à entrer dans le serveur de base de données auquel vous vous connectez et, éventuellement, à spécifier une base de données. Si vous ne spécifiez pas de base de données, vous pouvez choisir parmi celles disponibles sur l’écran suivant.

Cliquez sur ‘Ok’. Vous serez alors invité à fournir des détails d’authentification. La valeur par défaut est Windows, vous pouvez donc continuer et cliquer sur « Connecter ».

L’écran suivant vous permet de naviguer dans les bases de données et les tables du serveur de base de données que vous avez spécifié. Cliquez sur la base de données ‘démo’ pour la développer, puis cliquez sur le tableau ‘ventes’. Les données de la table sélectionnée sont affichées dans la fenêtre de droite. Nous pouvons maintenant cliquer sur le bouton « Charger » pour obtenir immédiatement les données dans Excel. Alternativement, nous pouvons cliquer sur ‘Transformer les données’ pour effectuer des transformations sur les données (par exemple, modifier les types de données, nettoyer certaines données, effectuer des calculs). Pour cet exemple, nous allons simplement charger les données telles quelles, alors allez-y et cliquez sur « Charger ».

Les données sont maintenant exportées de la table dans SQL Server et chargées dans une nouvelle feuille dans Excel.

C’est tout ce que nous devons faire pour effectuer une simple exportation d’une table complète de SQL Server vers Excel. Mis à part la procédure simple que nous avons décrite ici, les assistants et outils d’importation de données Power Query fournissent des moyens de « façonner » les données provenant de sources externes. Par exemple, vous pouvez supprimer une colonne, modifier un type de données, fusionner des tables pour répondre à vos besoins spécifiques.

Méthode (3): Exportation à l’aide du complément SQL Spreads pour Excel

Si vous n’avez pas déjà installé le complément SQL Spreads pour Excel, vous pouvez en télécharger une copie ici.

Une fois que SQL Spreads est installé, vous verrez qu’il a été ajouté en tant que nouvel onglet ruban; allez ici et cliquez sur le bouton Mode de conception.

Dans le panneau Concepteur de Spreads SQL sur le côté droit, cliquez sur le bouton Modifier pour ouvrir la boîte de dialogue de connexion SQL Server.

Entrez le nom de votre serveur SQL dans le champ Nom du serveur SQL. Vous devez également choisir une méthode d’authentification: Windows – Connexion (authentification Windows) ou authentification SQL Server (en utilisant un nom et un mot de passe configurés dans SQL Server). L’authentification Windows est la plus sécurisée des deux options.

Cliquez sur OK. Les spreads SQL essaieront de se connecter à la base de données. Si la connexion est réussie, vos bases de données apparaîtront dans le panneau SQL Spreads Designer.

Nous pouvons maintenant développer la base de données de démonstration et sélectionner la table des ventes.

Lorsque vous cliquez sur le tableau, les données sont immédiatement exportées depuis SQL Server et copiées dans une feuille appelée « ventes » dans Excel.

C’est tout! Nous avons réussi à exporter des données de SQL Server vers Excel en quelques clics seulement.

Plusieurs autres options s’offrent à nous dans le concepteur de Spreads SQL. Par exemple, nous pouvons:

• filtrer les données exportées depuis SQL Server
• définir si certaines colonnes sont affichées et/ou en lecture seule
• utiliser une valeur de recherche pour afficher du texte au lieu d’un id pour une colonne
• trier les données
• effectuer des mises à jour dans Excel et enregistrer les modifications dans SQL Server

Nous examinerons certaines de ces options dans un prochain article de blog.

Résumé

Pouvoir exporter des données de SQL Server vers Excel est une exigence courante dans la plupart des entreprises. Nous avons examiné trois méthodes pour effectuer cette tâche quotidienne. Le premier utilise l’Assistant d’importation et d’exportation SQL Server, qui est une fonctionnalité native de SQL Server. La deuxième méthode utilise les outils natifs ‘Get& Transform’ dans Excel. Le troisième utilise le complément Spreads SQL pour Excel.

Alors quelle méthode devriez-vous utiliser?

Si vous travaillez normalement avec des bases de données et que SSMS est votre deuxième maison, vous aurez probablement l’impression que l’Assistant d’importation et d’exportation SQL Server est la meilleure option pour vous. Si vous êtes un utilisateur d’Excel, il est logique de travailler à partir d’Excel lui-même pour exporter des données à partir de SQL Server. Les deux options Excel que nous avons examinées sont robustes et faciles à utiliser, mais SQL Spreads présente un net avantage car il permet aux utilisateurs de mettre à jour les données directement dans Excel, puis de les sauvegarder sur SQL Server. C’est un énorme avantage pour les organisations qui aiment le meilleur des deux mondes: la facilité d’utilisation et la familiarité d’Excel, la puissance et l’intégrité des données associées à SQL Server.

Essayez les Spreads SQL pour voir à quel point il est rapide et facile d’exporter des données de SQL Server vers Excel ; téléchargez une version d’essai gratuite dès aujourd’hui.