Importer des données d’Excel vers SQL Server ou Azure SQL Database

Article
07/24/2024

S’applique à : SQL Server Azure SQL Database

Il existe plusieurs moyens d’importer des données provenant de fichiers Excel vers SQL Server ou Azure SQL Database. Certaines méthodes permettent d’importer directement des données à partir de fichiers Excel, en une seule étape ; d’autres impliquent d’exporter les données Excel au format texte (fichier CSV) pour pouvoir les importer.

Cet article récapitule les méthodes fréquemment utilisées et comporte des liens vers des informations plus détaillées. La description complète des outils et services complexes, par exemple SSIS ou Azure Data Factory, n’entre pas dans le cadre de cet article. Pour plus d’informations sur la solution qui vous intéresse, suivez les liens fournis.

Liste des méthodes

Il existe diverses façons d’importer des données à partir d’Excel. Vous devez installer SQL Server Management Studio (SSMS) pour utiliser certains de ces outils.

Vous pouvez utiliser les outils suivants pour importer des données à partir d’Excel :

Exporter au format texte en premier (SQL Server et Base de données Azure SQL)	Directement à partir d’Excel (SQL Server local uniquement)
Assistant Importation de fichier plat	Assistant Importation et Exportation SQL Server
Instruction BULK INSERT	SQL Server Integration Services (SSIS)
Outil de copie en bloc (bcp)	Fonction OPENROWSET
Assistant Copie (Azure Data Factory)
Azure Data Factory.

Si vous voulez importer plusieurs feuilles de calcul d’un classeur Excel, vous devez généralement exécuter l’un de ces outils une fois pour chaque feuille.

Pour plus d’informations, consultez les limitations et problèmes connus concernant le chargement des données vers et depuis des fichiers Excel.

Assistant Importation et Exportation

Importez des données directement depuis des fichiers Excel à l’aide de l’Assistant Importation et exportation SQL Server. Vous pouvez également enregistrer les paramètres sous forme de package SQL Server Integration Services (SSIS) pour pouvoir le personnaliser et le réutiliser plus tard.

Dans SQL Server Management Studio, connectez-vous à une instance du SQL Server Moteur de base de données.
Développez Bases de données.
Cliquez avec le bouton droit sur le nom d’une base de données.
Sélectionner Tâches.
Choisissez d’Importer les données ou d’Exporter les données :

Cela lance l’Assistant :

Capture d’écran de la page Connexion à une source de données Excel.

Pour plus d’informations, consultez les articles suivants :

Integration Services (SSIS)

Si vous connaissez SSIS (SQL Server Integration Services) et que vous ne souhaitez pas exécuter l’Assistant Importation et Exportation SQL Server, vous pouvez créer un package SSIS qui utilise la source Excel et la destination SQL Server dans le flux de données.

Pour plus d’informations, consultez les articles suivants :

Pour apprendre à créer des packages SSIS, consultez le didacticiel Guide pratique pour créer un Package ETL.

Capture d’écran des composants du flux de données.

OPENROWSET et serveurs liés

Important

Dans Azure SQL Database, vous ne pouvez pas importer directement à partir d’Excel. Vous devez d’abord exporter les données vers un fichier texte (CSV).

Le fournisseur ACE (anciennement fournisseur Jet) qui se connecte à des sources de données Excel est destiné à une utilisation interactive côté client. Si vous utilisez le fournisseur ACE sur SQL Server, en particulier dans des processus automatisés ou qui s’exécutent en parallèle, vous constaterez peut-être des résultats inattendus.

Requêtes distribuées

Importez des données directement dans SQL Server à partir de fichiers Excel à l’aide de la fonction Transact-SQL OPENROWSET ou OPENDATASOURCE. Cette utilisation est appelée requête distribuée.

Important

Dans Azure SQL Database, vous ne pouvez pas importer directement à partir d’Excel. Vous devez d’abord exporter les données vers un fichier texte (CSV).

Pour pouvoir exécuter une requête distribuée, vous devez activer l’option de configuration du serveur Ad Hoc Distributed Queries, comme l’indique l’exemple suivant. Pour plus d'informations, consultez Configuration du serveur : Requêtes distribuées ad hoc.

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

L’exemple de code suivant utilise OPENROWSET pour importer les données de la feuille de calcul Excel Sheet1 dans une nouvelle table de base de données.

USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]);
GO

Voici le même exemple avec OPENDATASOURCE.

USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
    'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0')...[Sheet1$];
GO

Pour ajouter les données importées à une table existante au lieu d’en créer une nouvelle, utilisez la syntaxe INSERT INTO ... SELECT ... FROM ... à la place de la syntaxe SELECT ... INTO ... FROM ... utilisée dans les exemples précédents.

Pour interroger les données Excel sans les importer, utilisez simplement la syntaxe SELECT ... FROM ... standard.

Pour plus d’informations sur les requêtes distribuées, consultez les articles suivants :

¹ Les requêtes distribuées sont toujours prises en charge par SQL Server, mais la documentation relative à cette fonctionnalité n'a pas été mise à jour.

Serveurs liés

Vous pouvez également configurer une connexion permanente de SQL Server au fichier Excel sous forme de serveur lié. L’exemple suivant importe les données de la feuille de calcul Data sur le serveur lié Excel EXCELLINK dans une nouvelle table de base de données SQL Server nommée Data_ls.

USE ImportFromExcel;
GO
SELECT * INTO Data_ls FROM EXCELLINK...[Data$];
GO

Vous pouvez créer un serveur lié à partir de SQL Server Management Studio (SSMS), ou en exécutant la procédure stockée de système sp_addlinkedserver, comme l’illustre l’exemple suivant.

DECLARE @RC INT;
DECLARE @server NVARCHAR(128);
DECLARE @srvproduct NVARCHAR(128);
DECLARE @provider NVARCHAR(128);
DECLARE @datasrc NVARCHAR(4000);
DECLARE @location NVARCHAR(4000);
DECLARE @provstr NVARCHAR(4000);
DECLARE @catalog NVARCHAR(128);

-- Set parameter values
SET @server = 'EXCELLINK';
SET @srvproduct = 'Excel';
SET @provider = 'Microsoft.ACE.OLEDB.12.0';
SET @datasrc = 'C:\Temp\Data.xlsx';
SET @provstr = 'Excel 12.0';

EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server,
    @srvproduct,
    @provider,
    @datasrc,
    @location,
    @provstr,
    @catalog;

Pour plus d’informations sur les serveurs liés, consultez les articles suivants :

Pour plus d’exemples et d’informations sur les serveurs liés et les requêtes distribuées, consultez l’article suivant :

Guide pratique pour utiliser Excel avec des serveurs liés et des requêtes distribuées SQL Server

Prérequis

Pour utiliser les autres méthodes décrites sur cette page (l’instruction BULK INSERT, l’outil bcp ou Azure Data Factory), vous devez d’abord exporter vos données Excel dans un fichier texte.

Enregistrer des données Excel sous forme de texte

Dans Excel, sélectionnez Fichier | Enregistrer sous, puis Texte (délimité par des tabulations) (*.txt) ou CSV (séparé par des virgules) (*.csv) comme type de fichier de destination.

Si vous voulez exporter plusieurs feuilles de calcul du classeur, sélectionnez chaque feuille et répétez cette procédure. La commande Enregistrer en tant que exporte uniquement la feuille active.

Conseil

Pour obtenir de meilleurs résultats avec les outils d’importation de données, enregistrez les feuilles qui contiennent uniquement les en-têtes de colonnes et les lignes de données. Si les données enregistrées contiennent des titres des pages, des lignes vides, des notes et ainsi de suite, il se peut que vous constatiez des résultats inattendus par la suite, lorsque vous importerez des données.

Assistant Importation de fichier plat

Importez des données enregistrées en tant que fichiers texte en parcourant les pages de l’Assistant Importation de fichier plat.

Comme nous l’avons expliqué dans la section Prérequis, il est nécessaire d’exporter les données Excel sous forme de texte pour pouvoir les importer avec l’Assistant Importation de fichier plat.

Pour plus d’informations sur l’Assistant Importation de fichier plat, voir Assistant Importation de fichier plat vers SQL.

Commande BULK INSERT

BULK INSERT est une commande Transact-SQL exécutable à partir de SQL Server Management Studio. L’exemple suivant charge les données du fichier délimité par des virgules Data.csv dans une table de base de données existante.

Comme décrit précédemment dans la section Prérequis, vous devez exporter vos données Excel sous forme de texte pour pouvoir utiliser BULK INSERT pour les importer. BULK INSERT ne peut pas lire les fichiers Excel directement. À l’aide de la commande BULK INSERT, vous pouvez importer un fichier CSV stocké localement ou dans le stockage Blob Azure.

USE ImportFromExcel;
GO
BULK INSERT Data_bi FROM 'C:\Temp\data.csv'
   WITH (
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
);
GO

Pour obtenir plus d’informations et d’exemples sur SQL Server et la Base de données Azure SQL, consultez les articles suivants :

Outil de copie en bloc (bcp)

L’outil bcp est exécuté à partir de l’invite de commandes. L’exemple suivant charge les données du fichier délimité par des virgules Data.csv dans la table de base de données existante Data_bcp.

Comme décrit précédemment dans la section Prérequis, vous devez exporter vos données Excel sous forme de texte pour pouvoir utiliser bcp pour les importer. L’outil bcp ne peut pas lire les fichiers Excel directement. Utilisez pour importer dans SQL Server ou SQL Database à partir d’un fichier texte (CSV) enregistré dans le stockage local.

Important

Pour un fichier texte (CSV) stocké dans le stockage Blob Azure, utilisez BULK INSERT ou OPENROWSET. Pour obtenir un exemple, consultez Utiliser BULK INSERT ou OPENROWSET(BULK...) pour importer des données dans SQL Server.

bcp.exe ImportFromExcel..Data_bcp in "C:\Temp\data.csv" -T -c -t ,

Pour plus d’informations sur les bcp, consultez les articles suivants :

Assistant Copie (ADF)

Importez des données enregistrées en tant que fichiers texte en effectuant les étapes des pages de l’Assistant Copie d’Azure Data Factory (ADF).

Comme décrit précédemment dans la section Prérequis, vous devez exporter vos données Excel sous forme de texte pour pouvoir utiliser Azure Data Factory pour les importer. Data Factory ne peut pas lire les fichiers Excel directement.

Pour plus d’informations sur l’Assistant Copie, consultez les articles suivants :

Azure Data Factory.

Si vous connaissez Azure Data Factory et que vous ne voulez pas exécuter l’Assistant Copie, créez un pipeline avec une activité de copie qui permet d’effectuer une copie à partir du fichier texte dans SQL Server ou Azure SQL Database.

Pour plus d’informations sur l’utilisation de ces sources et récepteurs Data Factory, consultez les articles suivants :

Pour apprendre à copier des données avec Azure Data Factory, consultez les articles suivants :

Erreurs courantes

« Microsoft.ACE.OLEDB.12.0 » n’a pas été inscrit

Cette erreur se produit, car le fournisseur OLEDB n’est pas installé. Installez-le à partir de Microsoft Access Database Engine 2016 Redistributable. Veillez à installer la version 64 bits si Windows et SQL Server sont tous deux 64 bits.