Importowanie danych z programu Excel do programu SQL Server lub usługi Azure SQL Database

Dotyczy:programu SQL ServerAzure SQL Database

Istnieje kilka sposobów importowania danych z plików programu Excel do programu SQL Server lub do usługi Azure SQL Database. Niektóre metody umożliwiają importowanie danych w jednym kroku bezpośrednio z plików programu Excel; inne metody wymagają wyeksportowania danych programu Excel jako tekstu (pliku CSV), zanim będzie można je zaimportować.

Ten artykuł zawiera podsumowanie często używanych metod i zawiera linki do bardziej szczegółowych informacji. Pełny opis złożonych narzędzi i usług, takich jak SSIS lub Azure Data Factory, wykracza poza zakres tego artykułu. Aby uzyskać więcej informacji na temat rozwiązania, które Cię interesuje, skorzystaj z podanych linków.

Lista metod

Istnieje kilka sposobów importowania danych z programu Excel. Aby użyć niektórych z tych narzędzi, należy zainstalować program SQL Server Management Studio (SSMS).

Do importowania danych z programu Excel można użyć następujących narzędzi:

Najpierw eksportuj do tekstu (SQL Server i Azure SQL Database)	Bezpośrednio z programu Excel (tylko lokalny program SQL Server)
Kreator importowania plików prostych	Kreator importowania i eksportowania programu SQL Server
instrukcja BULK INSERT	Usługi Integracyjne SQL Server (SSIS)
narzędzia do kopiowania zbiorczego (bcp)	funkcja OPENROWSET
Asystent Kopiowania (Azure Data Factory)
Azure Data Factory

Jeśli chcesz zaimportować wiele arkuszy ze skoroszytu programu Excel, zazwyczaj trzeba uruchomić dowolne z tych narzędzi raz dla każdego arkusza.

Aby uzyskać więcej informacji, zobacz ograniczenia i znane problemy dotyczące ładowania danych do lub z plików programu Excel.

Kreator importu i eksportu

Importowanie danych bezpośrednio z plików programu Excel przy użyciu Kreatora importu i eksportu programu SQL Server. Możesz również zapisać ustawienia jako pakiet usług SQL Server Integration Services (SSIS), który można dostosować i użyć ponownie później.

1. W programie SQL Server Management Studio połącz się z wystąpieniem silnika bazy danych SQL Server.

2. Rozwiń bazy danych .

3. Kliknij prawym przyciskiem myszy bazę danych.

4. Wybierz zadania .

5. Wybierz opcję importuj dane lub Eksportuj dane:

   

Spowoduje to uruchomienie kreatora:

Aby uzyskać więcej informacji, zobacz następujące artykuły:

• Uruchamianie Kreatora importowania i eksportowania programu SQL Server
• Rozpocznij od tego prostego przykładu Kreatora importu i eksportu

Integration Services (SSIS)

Jeśli znasz usługi SQL Server Integration Services (SSIS) i nie chcesz uruchamiać Kreatora importu i eksportu programu SQL Server, możesz utworzyć pakiet usług SSIS, który używa źródła programu Excel i miejsca docelowego programu SQL Server w przepływie danych.

Aby uzyskać więcej informacji, zobacz następujące artykuły:

• Źródło programu Excel
• SQL Server Destination

Aby rozpocząć tworzenie pakietów usług SSIS, zobacz samouczek How to Create an ETL Package( Jak utworzyć pakiet ETL).

OPENROWSET i połączone serwery

Ważny

W usłudze Azure SQL Database nie można importować bezpośrednio z programu Excel. Najpierw należy wyeksportować dane do pliku tekstowego (CSV).

W poniższych przykładach użyto dostawcy JET, ponieważ dostawca ACE dołączony do pakietu Office łączący się ze źródłami danych programu Excel jest przeznaczony do interaktywnego użycia po stronie klienta.

Zapytania rozproszone

Zaimportuj dane bezpośrednio do programu SQL Server z plików programu Excel przy użyciu funkcji Transact-SQL OPENROWSET lub OPENDATASOURCE. To użycie jest nazywane zapytaniem rozproszonym .

Ważny

W usłudze Azure SQL Database nie można importować bezpośrednio z programu Excel. Najpierw należy wyeksportować dane do pliku tekstowego (CSV).

Przed uruchomieniem zapytania rozproszonego należy włączyć opcję konfiguracji serwera Ad Hoc Distributed Queries, jak pokazano w poniższym przykładzie. Aby uzyskać więcej informacji, zobacz Konfiguracja serwera: Ad Hoc Distributed Queries.

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

Poniższy przykładowy kod używa OPENROWSET do importowania danych z arkusza Sheet1 programu Excel do nowej tabeli bazy danych.

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

Oto ten sam przykład z 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

Aby dołączyć zaimportowanych danych do istniejącej tabeli zamiast tworzenia nowej tabeli, użyj składni INSERT INTO ... SELECT ... FROM ... zamiast składni SELECT ... INTO ... FROM ... używanej w poprzednich przykładach.

Aby wykonywać zapytania dotyczące danych programu Excel bez ich importowania, wystarczy użyć standardowej składni SELECT ... FROM ....

Aby uzyskać więcej informacji na temat zapytań rozproszonych, zobacz następujące artykuły:

• zapytań rozproszonych¹
• OPENROWSET (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)

¹ zapytania rozproszone są nadal obsługiwane w programie SQL Server, ale dokumentacja tej funkcji nie jest aktualizowana.

Połączone serwery

Możesz również skonfigurować trwałe połączenie z SQL Server do pliku programu Excel jako serwer połączony. Poniższy przykład importuje dane z arkusza Data na istniejącym serwerze połączonym programu Excel EXCELLINK do nowej tabeli bazy danych programu SQL Server o nazwie Data_ls.

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

Możesz utworzyć serwer połączony z programu SQL Server Management Studio (SSMS) lub uruchamiając systemową procedurę składowaną sp_addlinkedserver, jak pokazano w poniższym przykładzie.

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;

Aby uzyskać więcej informacji na temat serwerów połączonych, zobacz następujące artykuły:

• Tworzenie serwerów połączonych (aparat bazy danych programu SQL Server)
• OPENQUERY (Transact-SQL)

Aby uzyskać więcej przykładów i informacji na temat serwerów połączonych i zapytań rozproszonych, zobacz następujący artykuł:

• Jak używać programu Excel z serwerami połączonymi z programem SQL Server i zapytaniami rozproszonymi

Warunki wstępne

Aby użyć pozostałych metod opisanych na tej stronie (instrukcja BULK INSERT, narzędzie bcp lub Azure Data Factory), najpierw musisz wyeksportować dane programu Excel do pliku tekstowego.

Zapisywanie danych programu Excel jako tekstu

W programie Excel wybierz pozycję plik | Zapisz jako, a następnie wybierz tekst (rozdzielany tabulatorami) (*.txt) lub CSV (rozdzielany przecinkami) (*.csv) jako typ pliku docelowego.

Jeśli chcesz wyeksportować wiele arkuszy ze skoroszytu, zaznacz każdy arkusz, a następnie powtórz tę procedurę. Polecenie Zapisz jako eksportuje tylko aktywny arkusz.

Wskazówka

Aby uzyskać najlepsze wyniki przy użyciu narzędzi do importowania danych, zapisz arkusze zawierające tylko nagłówki kolumn i wiersze danych. Jeśli zapisane dane zawierają tytuły stron, puste wiersze, notatki i tak dalej, podczas importowania danych mogą pojawić się nieoczekiwane wyniki.

Kreator importu plików prostych

Zaimportuj dane zapisane jako pliki tekstowe, przechodząc krok po kroku przez strony Kreatora importu plików prostych.

Jak opisano wcześniej w sekcji Wymagania wstępne, musisz wyeksportować dane programu Excel jako tekst, zanim będzie można zaimportować je za pomocą Kreatora importu plików prostych.

Aby uzyskać więcej informacji na temat Kreatora importu plików prostych, zobacz Import Flat File to SQL Wizard.

POLECENIE BULK INSERT

BULK INSERT to polecenie Transact-SQL, które można uruchomić z programu SQL Server Management Studio. W poniższym przykładzie dane są ładowane z pliku rozdzielanego przecinkami Data.csv do istniejącej tabeli bazy danych.

Jak opisano wcześniej w sekcji Wymagania wstępne, musisz wyeksportować dane programu Excel jako tekst, zanim będzie można go zaimportować za pomocą BULK INSERT. BULK INSERT nie potrafi bezpośrednio odczytywać plików Excela. Za pomocą polecenia BULK INSERT można zaimportować plik CSV przechowywany lokalnie lub w usłudze Azure Blob Storage.

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

Aby uzyskać więcej informacji i przykładów dla programu SQL Server i usługi Azure SQL Database, zobacz następujące artykuły:

• Użyj BULK INSERT lub OPENROWSET(BULK...) do importowania danych do SQL Server
• MASOWE WSTAWIANIE (Transact-SQL)

Narzędzie do kopiowania zbiorczego (bcp)

Narzędzie bcp jest uruchamiane z poziomu wiersza polecenia. W poniższym przykładzie dane są ładowane z Data.csv pliku rozdzielanego przecinkami do istniejącej tabeli bazy danych Data_bcp.

Jak opisano wcześniej w sekcji Wymagania wstępne, musisz wyeksportować dane programu Excel jako tekst, zanim będzie można użyć bcp, aby je zaimportować. Narzędzie bcp nie może bezpośrednio odczytywać plików programu Excel. Służy do importowania do programu SQL Server lub usługi SQL Database z pliku testowego (CSV) zapisanego w magazynie lokalnym.

Ważny

W przypadku pliku tekstowego (CSV) przechowywanego w usłudze Azure Blob Storage użyj BULK INSERT lub OPENROWSET. Aby zapoznać się z przykładem, zobacz Użyj BULK INSERT lub OPENROWSET(BULK...), aby zaimportować dane do SQL Server.

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

Aby uzyskać więcej informacji na temat bcp, zobacz następujące artykuły:

• importowanie i eksportowanie danych zbiorczych przy użyciu programu bcp (SQL Server)
• narzędzie bcp
• Przygotowywanie danych do eksportu zbiorczego lub importowania

Kreator kopiowania dokumentów (ADF)

Zaimportuj dane zapisane jako pliki tekstowe, przechodząc przez strony Kreatora kopiowania usługi Azure Data Factory (ADF).

Jak opisano wcześniej w sekcji Wymagania wstępne, musisz wyeksportować dane programu Excel jako tekst, zanim będzie można zaimportować je za pomocą usługi Azure Data Factory. Usługa Data Factory nie może bezpośrednio odczytywać plików programu Excel.

Aby uzyskać więcej informacji na temat Kreatora kopiowania, zobacz następujące artykuły:

• Kreator kopiowania w Data Factory
• Poradnik: Utwórz pipeline z funkcją kopiowania przy użyciu Kreatora kopiowania z Data Factory.

Azure Data Factory

Jeśli znasz usługę Azure Data Factory i nie chcesz uruchamiać Kreatora kopiowania, utwórz potok z działaniem kopiowania, które kopiuje z pliku tekstowego do serwera SQL Server lub usługi Azure SQL Database.

Jak opisano wcześniej w sekcji Wymagania wstępne, musisz wyeksportować dane programu Excel jako tekst, zanim będzie można zaimportować je za pomocą usługi Azure Data Factory. Usługa Data Factory nie może bezpośrednio odczytywać plików programu Excel.

Aby uzyskać więcej informacji na temat używania tych źródeł i celów usługi Data Factory, zobacz następujące artykuły:

• System plików
• SQL Server
• Azure SQL Database

Aby rozpocząć naukę kopiowania danych za pomocą usługi Azure Data Factory, zobacz następujące artykuły:

• Przenoszenie danych przy użyciu działania kopiowania
• Samouczek: Tworzenie potoku z wykorzystaniem funkcji kopiowania w portalu Azure

Powiązana zawartość

• Rozpocznij od tego prostego przykładu Kreatora importu i eksportu
• importowanie danych z programu Excel lub eksportowanie danych do programu Excel przy użyciu usług SQL Server Integration Services (SSIS)
• narzędzie bcp
• Przenoszenie danych przy użyciu działania kopiowania