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

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, veuillez suivre les liens fournis.

Liste des méthodes

Il existe plusieurs méthodes pour importer des données depuis Excel. Installez la dernière version de 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.

Pour plus d’informations, consultez les limitations et les problèmes connus liés au chargement de données vers ou à partir de 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 dans un package SQL Server Integration Services (SSIS) que vous pourrez personnaliser et réutiliser ultérieurement.

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électionnez Tâches.

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

   

Cela lance l’Assistant :

Si vous souhaitez en savoir plus, consultez les articles suivants :

• Démarrez l’Assistant Importation et Exportation SQL Server
• Commencez avec cet exemple simple de l’Assistant Importation et Exportation

Integration Services (SSIS)

Si vous connaissez bien SQL Server Integration Services (SSIS) 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.

Si vous souhaitez en savoir plus, consultez les articles suivants :

• 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

Les exemples suivants utilisent le fournisseur JET. Le fournisseur ACE inclus dans Office qui se connecte à des sources de données Excel est destiné à une utilisation interactive côté client, ce qui peut entraîner des résultats inattendus lorsqu’ils sont utilisés de manière non interactive.

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 Configuration du serveur : requêtes distribuées ad hoc.

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

EXECUTE 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.JET.OLEDB.4.0',
    'Excel 8.0; Database=C:\Temp\Data.xls', [Sheet1$]);
GO

Voici le même exemple avec OPENDATASOURCE.

USE ImportFromExcel;
GO

SELECT * INTO Data_dq
FROM OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
    'Data Source=C:\Temp\Data.xls;Extended Properties=Excel 8.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¹
• OPENROWSET (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)

¹ Les requêtes distribuées sont toujours prises en charge dans SQL Server, mais la documentation relative à cette fonctionnalité n’est pas 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 système sp_addlinkedserver, comme indiqué dans l’exemple suivant.

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

-- Set parameter values
SET @server = 'EXCELLINK';
SET @srvproduct = 'Excel';
SET @provider = 'Microsoft.JET.OLEDB.4.0';
SET @datasrc = 'C:\Temp\Data.xls';
SET @provstr = 'Excel 8.0';

EXECUTE
    @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 (Moteur de base de données SQL Server)
• OPENQUERY (Transact-SQL)

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

• Utilisation d’Excel avec les serveurs liés SQL Server et les requêtes distribuées

Prerequisites

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 vers un fichier texte.

Enregistrer les données Excel au format 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 souhaitez exporter plusieurs feuilles de calcul à partir du classeur, sélectionnez chaque feuille, puis répétez cette procédure. La commande Enregistrer en tant que exporte uniquement la feuille active.

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 indiqué précédemment dans la section Prérequis, vous devez exporter vos données Excel au format texte avant de pouvoir les importer à l’aide de 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 que vous pouvez exécuter à 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 indiqué précédemment dans la section Prérequis, vous devez exporter vos données Excel au format texte avant de pouvoir les importer à l’aide de BULK INSERT. BULK INSERT ne peut pas lire directement les fichiers Excel. Avec 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 plus d’informations et des exemples pour SQL Server et Azure SQL Database, consultez les articles suivants :

• Utiliser BULK INSERT ou OPENROWSET(BULK...) pour importer des données dans SQL Server
• BULK INSERT (Transact-SQL)

L’outil de copie en bloc (bcp)

L’outil bcp s’exécute à 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 indiqué précédemment dans la section Prérequis, vous devez exporter vos données Excel au format texte avant de pouvoir les importer à l’aide de bcp. L’outil bcp ne peut pas lire directement les fichiers Excel. 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 bcp, consultez les articles suivants :

• Importer et exporter des données en masse à l’aide de bcp (SQL Server)
• Utilitaire bcp
• Préparer les données pour l’exportation ou l’importation en masse

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 au format texte avant de pouvoir les importer à l’aide d’Azure Data Factory. Data Factory ne peut pas lire les fichiers Excel directement.

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

• Assistant Copie de Data Factory
• Didacticiel : Créer un pipeline avec l’activité Copier à 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 au format texte avant de pouvoir les importer à l’aide d’Azure Data Factory. 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
• Base de données SQL Azure

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

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

Contenu associé

• Commencez avec cet exemple simple de l’Assistant Importation et Exportation
• Importer des données depuis 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é Copier