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

S’applique à :SQL ServerAzure 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 devrez peut-être 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 :

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.

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.

1. Dans SQL Server Management Studio, connectez-vous à une instance du SQL Server Moteur de base de données.

2. Développez Bases de données.

3. Cliquez avec le bouton droit sur le nom d’une base de données.

4. Sélectionner Tâches.

5. Choisissez d’Importer les données ou d’Exporter les données :

   

Cela lance l’Assistant :

Pour en savoir plus, consultez :

• Démarrer l’Assistant Importation et Exportation SQL Server
• Bien démarrer avec cet exemple simple de l’Assistant Importation et Exportation

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, créez un package SSIS qui utilise la source Excel et la destination SQL Server dans le flux de données.

Pour en savoir plus, consultez :

• Source Excel
• Destination SQL Server

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

OPENROWSET et serveurs liés

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.

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 la page Option de 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 :

• Requêtes distribuées (Les requêtes distribuées sont toujours prises en charge dans SQL Server 2019 (15.x), mais la documentation relative à cette fonctionnalité n’a pas été mise à jour.)
• OPENROWSET
• OPENDATASOURCE

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 :

• Créer des serveurs liés
• OPENQUERY

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 - Enregistrer des données Excel sous forme de texte

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.

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.

l’Assistant Importation d’un 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 SQL Database, consultez les articles suivants :

• Importer des données en bloc à l’aide de BULK INSERT ou OPENROWSET(BULK...)
• BULK INSERT

Outil BCP

BCP est un programme que vous exécutez à 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. 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.

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

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

• Importer et exporter des données en bloc à l’aide de l’utilitaire bcp
• Utilitaire bcp
• Préparer des données en vue d’une exportation ou d’une importation en bloc

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 :

• Assistant Data Factory Copy
• Didacticiel : Créer un pipeline avec activité de copie à l’aide de l’Assistant Copie de Data Factory

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.

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’utilisation de ces sources et récepteurs Data Factory, consultez les articles suivants :

• Système de fichiers
• SQL Server
• Azure SQL Database

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

• Déplacer des données à l’aide de l’activité de copie
• Didacticiel : Créer un pipeline avec l’activité de copie à l’aide du portail Azure

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.

L’erreur complète est la suivante :

Msg 7403, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

Impossible de créer une instance du fournisseur OLE DB « Microsoft.ACE.OLEDB.12.0 » du serveur lié « (null) »

Ce résultat indique que Microsoft OLEDB n’a pas été configuré correctement. Exécutez le code Transact-SQL suivant pour résoudre ce problème :

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1;
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1;

L’erreur complète est la suivante :

Msg 7302, Level 16, State 1, Line 3
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Impossible de charger le fournisseur OLE DB 32 bits « Microsoft.ACE.OLEDB.12.0 » in-process sur un serveur SQL Server 64 bits

Cela se produit quand une version 32 bits du fournisseur OLE DB est installée avec un serveur SQL Server 64 bits. Pour résoudre ce problème, désinstallez la version 32 bits et installez la version 64 bits du fournisseur OLE DB à la place.

L’erreur complète est la suivante :

Msg 7438, Level 16, State 1, Line 3
The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.

Le fournisseur OLE DB « Microsoft.ACE.OLEDB.12.0 » du serveur lié « (null) » a signalé une erreur.

Impossible d’initialiser l’objet de la source de données du fournisseur OLE DB « Microsoft.ACE.OLEDB.12.0 » du serveur lié « (null) »

Ces deux erreurs indiquent généralement un problème d’autorisations entre le processus SQL Server et le fichier. Vérifiez que le compte qui exécute le service SQL Server dispose de droits d’accès complet au fichier. Nous vous déconseillons d’essayer d’importer des fichiers à partir du bureau.

Les erreurs complètes sont :

Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Étapes suivantes

• Bien démarrer avec cet exemple simple de l’Assistant Importation et Exportation
• Importer des données à partir d’Excel ou exporter des données vers Excel avec SQL Server Integration Services (SSIS)
• Utilitaire bcp
• Déplacer des données à l’aide de l’activité de copie