Il existe plusieurs façons d’exporter/importer des données SQL Server vers un fichier Excel. Dans cet article, quelques façons d’importer et d’exporter des données SQL Server vers un fichier Excel seront expliquées :
- Exporter des données SQL Server vers un fichier Excel en utilisant
- l’assistant d’importation et d’exportation de SQL Server
- T-.SQL
- Exportation vers Excel à partir d’ApexSQL Complete
Importer des données SQL Server dans Excel à l’aide de l’assistant de connexion de données
Exporter des données SQL Server vers un fichier Excel à l’aide de l’assistant d’importation et d’exportation SQL Server
Dans SQL Server Management Studio (SSMS), il existe une fonctionnalité qui permet d’exporter des données d’une source de données vers une autre source de données ; dans ce cas, les données de SQL Server seront copiées vers un fichier Excel via l’assistant d’exportation.
Pour commencer à utiliser cette fonctionnalité, allez dans l’Explorateur d’objets, faites un clic droit sur n’importe quelle base de données (par ex. AdventureworksDW2016CTP3), sous les Tâches, choisissez la commande Exporter des données:
Cela ouvrira la fenêtre Assistant d’importation et d’exportation du serveur SQL:
Pour procéder à l’exportation des données du serveur SQL vers un fichier Excel, cliquez sur le bouton Suivant. Cela ouvre la fenêtre Choisir une source de données. Sur cette fenêtre, on peut choisir une source à partir de laquelle on veut copier les données :
Dans la boîte déroulante Source de données, choisissez l’élément SQL Server Native Client 11.0. Dans la zone déroulante Nom du serveur, choisissez l’instance de SQL Server à partir de laquelle les données doivent être copiées. Dans la section Authentification, choisissez le mode d’authentification pour la connexion de la source de données et sous la boîte déroulante Base de données, choisissez la base de données à partir de laquelle les données seront copiées. Après avoir défini toutes ces options, cliquez sur le bouton Suivant pour continuer.
Cela ouvrira la fenêtre Choisir une destination dans laquelle peut être spécifié où copier les données de la source de données :
Dans la boîte déroulante Destination, choisissez l’élément Microsoft Excel. Sous le chemin d’accès au fichier Excel, choisissez la destination des données copiées de la source de données SQL Server (SQL Data.xlsx). Dans la zone de liste déroulante Version Excel, choisissez la version de la feuille de calcul Microsoft Excel. Après avoir défini tous ces paramètres, appuyez sur le bouton Suivant pour continuer. Mais après avoir appuyé sur le bouton Suivant, le message d’avertissement suivant peut apparaître :
L’opération n’a pas pu être achevée.
INFORMATIONS SUPPLÉMENTAIRES:
Le fournisseur ‘Microsoft.ACE.OLEDB.12.0’ n’est pas enregistré sur la machine locale. (System.Data)
Cela se produit car SSMS est une application 32 bits et lorsque vous lancez l’assistant d’exportation via SSMS, il lance une version 32 bits de l’assistant d’exportation. D’autre part, la machine (système d’exploitation) sur laquelle SSMS est installé est une version 64 bits et le serveur SQL installé est une version 64 bits.
Pour résoudre ce problème, fermez SSMS, allez dans le menu Démarrer et trouvez la version 64 bits de SQL Server Import and Export Data:
Après avoir lancé cet assistant d’importation et d’exportation de SQL Server, il sera invité à utiliser la même fenêtre que celle utilisée via SSMS. Après avoir défini toutes les options précédentes, cliquez sur le bouton Suivant. La fenêtre Spécifier la copie de table ou la requête apparaîtra :
Sur cette fenêtre, il est possible de spécifier s’il faut copier les données d’une ou plusieurs tables et vues ou copier les résultats d’une requête.
Sélectionnez le bouton radio Copier les données d’une ou plusieurs tables ou vues et cliquez sur le bouton Suivant. La fenêtre Sélectionner la table et les vues sources permet de choisir une ou plusieurs tables et vues à partir desquelles on souhaite exporter les données du serveur SQL vers un fichier Excel en cochant la case située à côté du nom de la table/vue :
Comme on peut le remarquer, dès que la case située à côté d’une table/vue est cochée, le nom de la table/vue sera copié sous la colonne Destination. Ce nom représente le nom de la feuille où seront mises les données de la table, ce nom peut être modifié à votre guise, mais pour l’instant, il sera laissé tel quel :
Pour avoir un aperçu des données qui seront générées vers un fichier Excel, cliquez sur le bouton Aperçu :
Après avoir choisi les données des tableaux/vue qui seront copiées dans un fichier Excel, cliquez sur le bouton Suivant :
Sur la fenêtre Save as Run Package, cochez la case Run immediately et cliquez sur le bouton Next:
La fenêtre Complete the Wizard affiche toutes les options choisies pour les opérations d’exportation. Pour finaliser le processus d’exportation des données du serveur SQL vers un fichier Excel, cliquez sur le bouton Terminer. La dernière fenêtre de l’assistant d’importation et d’exportation de SQL Server indique l’état des données ont été exportées avec succès ou certaines erreurs se sont produites pendant l’exportation des données :
Dans notre cas, les données ont été générées avec succès dans le fichier SQL Data.xlsx dans la feuille DimScenario:
Exporter des données SQL Server vers un fichier Excel à l’aide du code T-SQL
Le Transact-SQL OPENROWSET peut être utilisé pour exporter des données SQL Server vers un fichier Excel via SSMS. Dans un éditeur de requêtes, tapez et exécutez le code suivant :
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM ')
SELECT * FROM dbo.DimScenario
Mais, lors de l’exécution du code ci-dessus, l’erreur suivante peut se produire :
Msg 15281, Level 16, State 1, Line 1
SQL Server a bloqué l’accès à STATEMENT ‘OpenRowset/OpenDatasource’ du composant ‘Ad Hoc Distributed Queries’ car ce composant est désactivé dans le cadre de la configuration de sécurité de ce serveur. Un administrateur système peut activer l’utilisation de ‘Ad Hoc Distributed Queries’ en utilisant sp_configure. Pour plus d’informations sur l’activation de ‘Ad Hoc Distributed Queries’, recherchez ‘Ad Hoc Distributed Queries’ dans SQL Server Books Online.
Pour résoudre cette erreur, l’option Ad Hoc Distributed Queries doit être activée Cela peut être fait en utilisant la procédure sp_configure et en exécutant le code SQL suivant dans un éditeur de requêtes :
EXEC sp_configure 'show advanced options', 1RECONFIGUREEXEC sp_configure 'Ad Hoc Distributed Queries', 1RECONFIGURE
Après avoir exécuté le code ci-dessus, le message suivant apparaîtra pour indiquer que les options show advanced options et Ad Hoc Distributed Queries sont activées:
L’option de configuration ‘show advanced options’ est passée de 0 à 1. Exécutez l’instruction RECONFIGURE pour installer.
L’option de configuration ‘Ad Hoc Distributed Queries’ est passée de 0 à 1. Exécutez l’instruction RECONFIGURE pour installer.
Maintenant, lorsque de nouveau le code est exécuté :
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM ')
SELECT * FROM dbo.DimScenario
L’erreur suivante peut apparaître :
Fournisseur OLE DB « Microsoft.ACE.OLEDB.12..0 » pour le serveur lié « (null) » a renvoyé le message « Le moteur de base de données Microsoft Access ne peut pas ouvrir ou écrire dans le fichier « . Il est déjà ouvert exclusivement par un autre utilisateur, ou vous avez besoin d’une autorisation pour afficher et écrire ses données. ».
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider « Microsoft.ACE.OLEDB.12.0 » pour le serveur lié « (null)
Cela se produit généralement en raison de permissions inadéquates.
Plus d’informations sur la résolution de ces problèmes sont disponibles sur la page Comment interroger les données Excel à l’aide des serveurs liés de SQL Server.
Une façon de résoudre ce problème est d’ouvrir SSMS en tant qu’administrateur et d’exécuter le code à nouveau. Mais cette fois, une autre erreur peut apparaître :
Msg 213, Level 16, State 1, Line 1
Le nom de la colonne ou le nombre de valeurs fournies ne correspond pas à la définition de la table.
Pour résoudre cela, ouvrez le fichier excel (par exemple, SQL Data.xlsx) pour lequel il est prévu de stocker les données de la table SQL Server (par exemple dbo.DimScenario) et saisissez les noms des colonnes qui représenteront les noms des colonnes de la table DimScenario :
Fermettez le fichier SQL Data.xlsx et exécutez à nouveau le code :
Maintenant, le message suivant apparaîtra :
(3 lignes concernées)
Enfin, les données de la table SQL Server sont copiées dans le fichier Excel :
Importer les données de SQL Server dans Excel à l’aide de la boîte de dialogue Assistant de connexion de données
Une autre façon de copier les données de la table SQL Server dans un fichier Excel est d’utiliser la boîte de dialogue Assistant de connexion de données depuis Excel.
Pour ce faire, ouvrez un fichier Excel (par exemple, Données SQL.xlsx) dans lequel vous souhaitez importer des données. Dans l’onglet Données, sous le sous-menu Depuis une autre source, choisissez la commande Depuis le serveur SQL :
Cela ouvrira la boîte de dialogue Assistant de connexion de données. Dans le nom du serveur, saisissez un nom de l’instance du serveur SQL à partir duquel les données doivent être copiées. Dans la section Log on credentials, choisissez le mode d’authentification pour la connexion de la source de données et cliquez sur le bouton Next:
Dans la zone déroulante Select the database that contains the data you want, choisissez la base de données à partir de laquelle les données seront copiées. Dans la grille, toutes les tables et vues disponibles seront listées. Choisissez une table / vue souhaitée (par exemple, DimScenario) et cliquez sur le bouton Suivant pour continuer.
Sur cette fenêtre, laissez tout tel quel et appuyez sur le bouton Terminer :
Dans la boîte de dialogue Importer des données, choisissez le bouton radio Table, sous la section Où voulez-vous mettre les données ? section, choisissez le bouton radio Feuille de calcul existante et appuyez sur le bouton OK:
Après avoir appuyé sur le bouton OK, les données de la table DimScenario seront copiées dans le fichier SQL Data.xslx:
Exporter les données du serveur SQL vers un fichier Excel à l’aide de la fonction Export to Excel d’ApexSQL Complete
En utilisant la fonction Export to Excel d’ApexSQL Complete, add-in pour SSMS et Visual Studio, l’exportation des données de la table SQL Server vers un fichier Excel peut être effectuée en un clic.
Dans un éditeur de requêtes, tapez et exécutez le code suivant :
SELECT * FROM dbo.DimScenario ds
Dans la grille des résultats, sélectionnez les données que vous souhaitez exporter vers un fichier Excel, faites un clic droit dans la grille des résultats et, dans le menu contextuel, choisissez l’option Exporter vers Excel :
Cela ouvrira un nouveau classeur Excel et les données sélectionnées dans la grille Résultats seront copiées dans la première feuille de calcul :
Plus d’informations sur l’exportation vers Excel via ApexSQL Complete sont disponibles sur la page Exportation vers Excel.
Voir aussi :
- Tutoriel : Importer des données dans Excel, et créer un modèle de données
- Démarrez avec cet exemple simple de l’assistant d’importation et d’exportation
- Comment interroger des données Excel à l’aide de serveurs liés à SQL Server
- Exporter vers Excel
.