Gegevens importeren uit Excel naar SQL Server of Azure SQL Database

van toepassing op:SQL ServerAzure SQL Database-

Er zijn verschillende manieren om gegevens te importeren uit Excel-bestanden naar SQL Server of naar Azure SQL Database. Met sommige methoden kunt u gegevens in één stap rechtstreeks vanuit Excel-bestanden importeren; Voor andere methoden moet u uw Excel-gegevens exporteren als tekst (CSV-bestand) voordat u deze kunt importeren.

In dit artikel vindt u een overzicht van de veelgebruikte methoden en vindt u koppelingen voor meer gedetailleerde informatie. Een volledige beschrijving van complexe hulpprogramma's en services, zoals SSIS of Azure Data Factory, valt buiten het bereik van dit artikel. Volg de opgegeven koppelingen voor meer informatie over de oplossing die u interesseert.

Lijst met methoden

Er zijn verschillende manieren om gegevens uit Excel te importeren. U moet SQL Server Management Studio (SSMS) installeren om een aantal van deze hulpprogramma's te kunnen gebruiken.

U kunt de volgende hulpprogramma's gebruiken om gegevens uit Excel te importeren:

Eerst exporteren naar tekst (SQL Server en Azure SQL Database)	Rechtstreeks vanuit Excel (uitsluitend voor on-premises SQL Server)
wizard Plat bestand importeren	SQL Server Import- en Exportwizard
BULK INSERT-instructie	SQL Server Integration Services (SSIS)
hulpprogramma voor bulksgewijs kopiëren (bcp-)	functie OPENROWSET
wizard Kopiëren (Azure Data Factory)
Azure Data Factory

Als u meerdere werkbladen uit een Excel-werkmap wilt importeren, moet u deze hulpprogramma's meestal één keer uitvoeren voor elk blad.

Zie beperkingen en bekende problemen voor het laden van gegevens naar of van Excel-bestanden voor meer informatie.

Wizard voor importeren en exporteren

Gegevens rechtstreeks uit Excel-bestanden importeren met behulp van de wizard IMPORTEREN en exporteren van SQL Server. U kunt de instellingen ook opslaan als een SSIS-pakket (SQL Server Integration Services) dat u later kunt aanpassen en opnieuw kunt gebruiken.

1. Maak in SQL Server Management Studio verbinding met een exemplaar van de SQL Server Database Engine.

2. Vouw Databasesuit.

3. Klik met de rechtermuisknop op een database.

4. Selecteer taken.

5. Kies ervoor om gegevens te importeren of gegevens te exporteren.

   

Hiermee wordt de wizard gestart:

Zie de volgende artikelen voor meer informatie:

• De Wizard Importeren en Exporteren van SQL Server starten
• Begin met dit eenvoudige voorbeeld van de wizard Importeren en exporteren

Integration Services (SSIS)

Als u bekend bent met SQL Server Integration Services (SSIS) en de wizard SQL Server Importeren en exporteren niet wilt uitvoeren, kunt u in plaats daarvan een SSIS-pakket maken dat gebruikmaakt van de Excel-bron en de SQL Server-bestemming in de gegevensstroom.

Zie de volgende artikelen voor meer informatie:

• Excel-bron
• SQL Server doel

Zie de zelfstudie Een ETL-pakket makenom te leren hoe u SSIS-pakketten maakt.

OPENROWSET en gekoppelde servers

Belangrijk

In Azure SQL Database kunt u niet rechtstreeks vanuit Excel importeren. U moet eerst de gegevens exporteren naar een tekstbestand (CSV)-bestand.

In de volgende voorbeelden wordt de JET-provider gebruikt, omdat de ACE-provider die is opgenomen in Office die verbinding maakt met Excel-gegevensbronnen bedoeld is voor interactief gebruik aan de clientzijde.

Gedistribueerde query's

Importeer gegevens rechtstreeks vanuit Excel-bestanden in SQL Server met behulp van de functie Transact-SQL OPENROWSET of OPENDATASOURCE. Dit gebruik wordt een gedistribueerde querygenoemd.

Belangrijk

In Azure SQL Database kunt u niet rechtstreeks vanuit Excel importeren. U moet eerst de gegevens exporteren naar een tekstbestand (CSV)-bestand.

Voordat u een gedistribueerde query kunt uitvoeren, moet u de configuratieoptie Ad Hoc Distributed Queries server inschakelen, zoals wordt weergegeven in het volgende voorbeeld. Zie Serverconfiguratie: Ad Hoc gedistribueerde query'svoor meer informatie.

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

In het volgende codevoorbeeld wordt OPENROWSET gebruikt om de gegevens uit het Excel Sheet1-werkblad te importeren in een nieuwe databasetabel.

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

Hier volgt hetzelfde voorbeeld met 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

Als u de geïmporteerde gegevens toevoegen aan een bestaande tabel in plaats van een nieuwe tabel te maken, gebruikt u de INSERT INTO ... SELECT ... FROM ... syntaxis in plaats van de SELECT ... INTO ... FROM ... syntaxis die in de voorgaande voorbeelden wordt gebruikt.

Als u een query wilt uitvoeren op de Excel-gegevens zonder deze te importeren, gebruikt u gewoon de standaardsyntaxis SELECT ... FROM ....

Zie de volgende artikelen voor meer informatie over gedistribueerde query's:

• gedistribueerde queries¹
• OPENROWSET (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)

¹ gedistribueerde query's worden nog steeds ondersteund in SQL Server, maar de documentatie voor deze functie wordt niet bijgewerkt.

Gekoppelde servers

U kunt ook een permanente verbinding van SQL Server met het Excel-bestand configureren als een gekoppelde server. In het volgende voorbeeld worden de gegevens uit het Data werkblad op de bestaande gekoppelde Excel-server EXCELLINK geïmporteerd in een nieuwe SQL Server-databasetabel met de naam Data_ls.

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

U kunt een gekoppelde server maken vanuit SQL Server Management Studio (SSMS) of door de door het systeem opgeslagen procedure uit te voeren sp_addlinkedserver, zoals wordt weergegeven in het volgende voorbeeld.

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.JET.OLEDB.4.0';
SET @datasrc = 'C:\Temp\Data.xls';
SET @provstr = 'Excel 8.0';

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

Zie de volgende artikelen voor meer informatie over gekoppelde servers:

• Gekoppelde servers (SQL Server Database Engine) maken
• OPENQUERY (Transact-SQL)

Zie het volgende artikel voor meer voorbeelden en informatie over gekoppelde servers en gedistribueerde query's:

• Excel gebruiken met gekoppelde SQL Server-servers en gedistribueerde query's

Voorwaarden

Als u de rest van de methoden wilt gebruiken die op deze pagina worden beschreven (de BULK INSERT-instructie, het hulpprogramma bcp of Azure Data Factory), moet u eerst uw Excel-gegevens exporteren naar een tekstbestand.

Excel-gegevens opslaan als tekst

Selecteer in Excel -bestand | Sla op als en selecteer tekst (door tabs gescheiden) (*.txt) of CSV (door komma's gescheiden) (*.csv) als doelbestandstype.

Als u meerdere werkbladen uit de werkmap wilt exporteren, selecteert u elk blad en herhaalt u deze procedure. Met de opdracht Opslaan als exporteert u alleen het actieve blad.

Tip

Voor de beste resultaten met hulpprogramma's voor het importeren van gegevens slaat u werkbladen op die alleen de kolomkoppen en de rijen met gegevens bevatten. Als de opgeslagen gegevens paginatitels, lege regels, notities enzovoort bevatten, ziet u mogelijk later onverwachte resultaten wanneer u de gegevens importeert.

Wizard voor het importeren van platte bestanden

Importeer gegevens die zijn opgeslagen als tekstbestanden door de pagina's van de wizard Plat bestand importeren te doorlopen.

Zoals eerder beschreven in de sectie Vereisten, moet u uw Excel-gegevens exporteren als tekst voordat u de wizard Plat bestand importeren kunt gebruiken om deze te importeren.

Zie Flat File importeren in SQL Wizardvoor meer informatie over de wizard Plat bestand importeren.

BULK INSERT-opdracht

BULK INSERT is een Transact-SQL opdracht die u kunt uitvoeren vanuit SQL Server Management Studio. In het volgende voorbeeld worden de gegevens uit het door komma's gescheiden Data.csv-bestand in een bestaande databasetabel geladen.

Zoals eerder beschreven in de sectie Vereisten, moet u uw Excel-gegevens exporteren als tekst voordat u BULK INSERT kunt gebruiken om deze te importeren. BULK INSERT kan Excel-bestanden niet rechtstreeks lezen. Met de opdracht BULK INSERT kunt u een CSV-bestand importeren dat lokaal of in Azure Blob Storage is opgeslagen.

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

Zie de volgende artikelen voor meer informatie en voorbeelden voor SQL Server en Azure SQL Database:

• BULK INSERT of OPENROWSET(BULK...) gebruiken om gegevens te importeren in SQL Server
• BULK INSERT (Transact-SQL)

Het hulpprogramma voor bulksgewijs kopiëren (bcp)

Het hulpprogramma bcp wordt uitgevoerd vanaf de opdrachtprompt. In het volgende voorbeeld worden de gegevens uit het door komma's gescheiden Data.csv-bestand in de bestaande Data_bcp-databasetabel geladen.

Zoals eerder beschreven in de sectie Vereisten, moet u uw Excel-gegevens exporteren als tekst voordat u bcp- kunt gebruiken om deze te importeren. Het hulpprogramma bcp kan Excel-bestanden niet rechtstreeks lezen. Gebruik dit bestand om te importeren in SQL Server of SQL Database vanuit een testbestand (CSV) dat is opgeslagen in lokale opslag.

Belangrijk

Voor een tekstbestand (CSV) dat is opgeslagen in Azure Blob Storage, gebruikt u BULK INSERT of OPENROWSET. Zie voor een voorbeeld BULK INSERT of OPENROWSET(BULK...) gebruiken om gegevens te importeren in SQL Server.

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

Zie de volgende artikelen voor meer informatie over bcp-:

• Bulkgegevens importeren en exporteren met bcp (SQL Server)
• bcp Utility
• gegevens voorbereiden voor bulksgewijs exporteren of importeren

Kopieerwizard (ADF)

Importeer gegevens die zijn opgeslagen als tekstbestanden door stap voor stap de pagina's van de wizard Kopiëren van Azure Data Factory (ADF) te doorlopen.

Zoals eerder beschreven in de sectie Vereisten, moet u uw Excel-gegevens exporteren als tekst voordat u Azure Data Factory kunt gebruiken om deze te importeren. Data Factory kan Excel-bestanden niet rechtstreeks lezen.

Zie de volgende artikelen voor meer informatie over de Kopieerwizard:

• Data Factory-kopieerwizard
• Handleiding: Maak een pijplijn met behulp van de kopieeractiviteit met de kopieerwizard van Data Factory.

Azure Data Factory

Als u bekend bent met Azure Data Factory en u de wizard Kopiëren niet wilt uitvoeren, maakt u een pijplijn met een kopieeractiviteit die kopieert van het tekstbestand naar SQL Server of naar Azure SQL Database.

Zoals eerder beschreven in de sectie Vereisten, moet u uw Excel-gegevens exporteren als tekst voordat u Azure Data Factory kunt gebruiken om deze te importeren. Data Factory kan Excel-bestanden niet rechtstreeks lezen.

Zie de volgende artikelen voor meer informatie over het gebruik van deze Data Factory-bronnen en sinks:

• bestandssysteem
• SQL Server
• Azure SQL Database

Raadpleeg de volgende artikelen om te leren hoe u gegevens kopieert met Azure Data Factory:

• gegevens verplaatsen met behulp van kopieeractiviteit
• Zelfstudie: Een pijplijn maken met kopieeractiviteit met behulp van Azure Portal

Verwante inhoud

• Aan de slag met dit eenvoudige voorbeeld van de wizard Importeren en exporteren
• Gegevens importeren uit Excel of gegevens exporteren naar Excel met SSIS- (SQL Server Integration Services)
• bcp Hulpprogramma
• gegevens verplaatsen met behulp van kopieeractiviteit