Istnieje kilka sposobów na eksport/eksport danych SQL Server do pliku Excel. W tym artykule zostanie wyjaśnionych kilka sposobów importu i eksportu danych SQL Server do pliku Excel:
- Eksport danych SQL Server do pliku Excel za pomocą
- Kreatora importu i eksportu SQL Server
- T-.Kod SQL
- Eksport do Excela z ApexSQL Complete
- Import danych SQL Server do Excela za pomocą kreatora połączeń danych
Eksport danych SQL Server do pliku Excel za pomocą kreatora importu i eksportu SQL Server
W SQL Server Management Studio (SSMS), dostępna jest funkcja umożliwiająca eksport danych z jednego źródła danych do innego źródła danych; W tym przypadku dane z serwera SQL zostaną skopiowane do pliku Excel za pomocą kreatora eksportu.
Aby rozpocząć korzystanie z tej funkcji, należy przejść do Object Explorer, kliknąć prawym przyciskiem myszy na dowolnej bazie danych (np. AdventureworksDW2016CTP3), w zakładce Zadania wybierz polecenie Eksportuj dane:
W ten sposób otworzy się okno Kreator importu i eksportu danych z serwera SQL:
Aby kontynuować eksport danych z serwera SQL do pliku Excel, kliknij przycisk Dalej. Spowoduje to otwarcie okna Choose a Data Source. W oknie tym można wybrać źródło, z którego mają zostać skopiowane dane:
Z rozwijanego pola Data source należy wybrać pozycję SQL Server Native Client 11.0. Z listy rozwijanej Nazwa serwera wybierz instancję SQL Server, z której mają zostać skopiowane dane. W sekcji Authentication wybierz tryb autentykacji dla połączenia ze źródłem danych, a w sekcji Database wybierz bazę danych, z której dane będą kopiowane. Po ustawieniu wszystkich tych opcji należy kliknąć przycisk Dalej, aby przejść dalej.
W ten sposób zostanie otwarte okno Wybierz miejsce docelowe, w którym można określić, gdzie mają być kopiowane dane ze źródła danych:
W oknie rozwijanym Miejsce docelowe należy wybrać pozycję Microsoft Excel. Pod ścieżką pliku Excel wybierz miejsce docelowe dla skopiowanych danych ze źródła danych SQL Server (SQL Data.xlsx). W polu Wersja programu Excel wybierz wersję arkusza programu Microsoft Excel. Po ustawieniu wszystkich tych parametrów, naciśnij przycisk Next, aby kontynuować. Jednak po naciśnięciu przycisku Dalej może pojawić się następujący komunikat ostrzegawczy:
Operacja nie mogła zostać zakończona.
INFORMACJE DODATKOWE:
Dostawca 'Microsoft.ACE.OLEDB.12.0′ nie jest zarejestrowany na komputerze lokalnym. (System.Data)
Dzieje się tak, ponieważ SSMS jest aplikacją 32-bitową i kiedy uruchamiamy Kreatora eksportu poprzez SSMS, uruchamia on 32-bitową wersję Kreatora eksportu. Z drugiej strony, maszyna (system operacyjny), na której zainstalowany jest SSMS jest wersją 64-bitową, a zainstalowany SQL Server jest wersją 64-bitową.
Aby rozwiązać ten problem, należy zamknąć SSMS, przejść do menu Start i odszukać 64-bitową wersję SQL Server Import and Export Data:
Po uruchomieniu tego kreatora SQL Server Import and Export Wizard, zostanie wyświetlone to samo okno, które jest używane przez SSMS. Po ustawieniu wszystkich wcześniejszych opcji klikamy przycisk Next. Pojawi się okno Specify Table Copy or Query:
W tym oknie można określić, czy kopiować dane z jednej lub wielu tabel i widoków, czy też kopiować wyniki zapytania.
Zaznaczyć przycisk radiowy Copy data from one or more tables or views i kliknąć przycisk Next. Okno Wybierz źródłową tabelę i widoki umożliwia wybranie jednej lub więcej tabel i widoków, z których mają zostać wyeksportowane dane serwera SQL do pliku Excela przez kliknięcie pola wyboru obok nazwy tabeli/widoku:
Jak można zauważyć, gdy tylko pole wyboru obok tabeli/widoku zostanie zaznaczone, nazwa tabeli/widoku zostanie skopiowana w kolumnie Miejsce docelowe. Nazwa ta reprezentuje nazwę arkusza, w którym zostaną umieszczone dane z tabeli, nazwa ta może być zmieniona na dowolną, ale na razie pozostanie taka, jaka jest:
Aby podejrzeć, jakie dane zostaną wygenerowane do pliku Excel, należy kliknąć przycisk Podgląd:
Po wybraniu, które dane tabel/przeglądów zostaną skopiowane do pliku Excel, należy kliknąć przycisk Dalej:
W oknie Zapisz jako pakiet uruchomieniowy należy zaznaczyć pole wyboru Uruchom natychmiast i kliknąć przycisk Dalej:
W oknie Zakończ kreatora widoczne są wszystkie opcje, które zostały wybrane dla operacji eksportu. Aby zakończyć proces eksportu danych SQL Server do pliku Excel, należy kliknąć przycisk Zakończ. Ostatnie okno Kreatora importu i eksportu danych SQL Server pokazuje stan, czy dane zostały pomyślnie wyeksportowane, czy też wystąpiły jakieś błędy podczas eksportu danych:
W naszym przypadku dane zostały pomyślnie wygenerowane do pliku SQL Data.xlsx w arkuszu DimScenario:
Eksport danych SQL Server do pliku Excel za pomocą kodu T-SQL
Do eksportu danych SQL Server do pliku Excel za pomocą SSMS można wykorzystać Transact-SQL OPENROWSET. W edytorze zapytań wpisz i wykonaj następujący kod:
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM ')
SELECT * FROM dbo.DimScenario
Jednak podczas wykonywania powyższego kodu może wystąpić następujący błąd:
Msg 15281, Level 16, State 1, Line 1
SerwerSQL zablokował dostęp do STATEMENTU 'OpenRowset/OpenDatasource' komponentu 'Ad Hoc Distributed Queries', ponieważ komponent ten jest wyłączony w ramach konfiguracji bezpieczeństwa dla tego serwera. Administrator systemu może włączyć obsługę „Ad Hoc Distributed Queries” za pomocą sp_configure. Aby uzyskać więcej informacji na temat włączania 'Ad Hoc Distributed Queries', wyszukaj 'Ad Hoc Distributed Queries' w SQL Server Books Online.
Aby rozwiązać ten błąd, opcja Ad Hoc Distributed Queries powinna być włączona Można to zrobić za pomocą procedury sp_configure i wykonując następujący kod SQL w edytorze zapytań:
EXEC sp_configure 'show advanced options', 1RECONFIGUREEXEC sp_configure 'Ad Hoc Distributed Queries', 1RECONFIGURE
Po wykonaniu powyższego kodu pojawi się następujący komunikat informujący o włączeniu opcji show advanced options oraz Ad Hoc Distributed Queries:
Opcja konfiguracyjna 'show advanced options' zmieniła się z 0 na 1. Uruchom polecenie RECONFIGURE, aby zainstalować.
Opcja konfiguracji „Ad Hoc Distributed Queries” zmieniła się z 0 na 1. Uruchom instrukcję RECONFIGURE, aby zainstalować.
Teraz, gdy ponownie zostanie wykonany kod:
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM ')
SELECT * FROM dbo.DimScenario
Może pojawić się następujący błąd:
OLE DB provider „Microsoft.ACE.OLEDB.12.0” dla połączonego serwera „(null)” zwrócił komunikat „Silnik bazy danych Microsoft Access nie może otworzyć lub zapisać do pliku „. Jest on już otwarty wyłącznie przez innego użytkownika lub wymagane są uprawnienia do przeglądania i zapisywania jego danych.”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider „Microsoft.ACE.OLEDB.12.0” dla serwera połączonego „(null)
Zwykle dzieje się tak z powodu nieodpowiednich uprawnień.
Więcej o tym, jak rozwiązać te problemy, można znaleźć na stronie Jak odpytywać dane Excela przy użyciu serwerów połączonych SQL Server.
Jednym ze sposobów rozwiązania tego problemu jest otwarcie SSMS jako administrator i ponowne wykonanie kodu. Jednak tym razem może pojawić się inny błąd:
Msg 213, Level 16, State 1, Line 1
Nazwa kolumny lub liczba dostarczonych wartości nie pasuje do definicji tabeli.
Aby rozwiązać ten problem, należy otworzyć plik Excela (np. SQL Data.xlsx), w którym planowane jest przechowywanie danych z tabeli SQL Server (np. dbo.DimScenario) i wprowadzić nazwy kolumn, które będą reprezentowały nazwy kolumn z tabeli DimScenario:
Zamknij plik SQL Data.xlsx i jeszcze raz wykonaj kod:
Teraz pojawi się następujący komunikat:
(3 rows affected)
Na koniec dane z tabeli SQL Server są kopiowane do pliku Excela:
Import danych z SQL Server do Excela za pomocą okna dialogowego Kreator połączeń danych
Innym sposobem na skopiowanie danych z tabeli SQL Server do pliku Excela jest użycie okna dialogowego Kreator połączeń danych z Excela.
W tym celu należy otworzyć plik Excela (np. SQL Data.xlsx), do którego chcemy zaimportować dane. Na karcie Dane, w podmenu Z innego źródła, wybierz polecenie Z serwera SQL:
Spowoduje to otwarcie okna dialogowego Kreator połączenia z danymi. W polu Server name należy wpisać nazwę instancji serwera SQL, z której mają być skopiowane dane. W sekcji Poświadczenia logowania wybierz tryb uwierzytelniania dla połączenia ze źródłem danych i kliknij przycisk Dalej:
Z rozwijanego pola Wybierz bazę danych zawierającą dane wybierz bazę danych, z której będą kopiowane dane. W widoku siatki wyświetlone zostaną wszystkie dostępne tabele i widoki. Wybierz żądaną tabelę / widok (np. DimScenario) i kliknij przycisk Dalej, aby kontynuować.
W tym oknie pozostaw wszystko tak jak jest i naciśnij przycisk Zakończ:
W oknie dialogowym Importuj dane wybierz przycisk radiowy Tabela, w sekcji Gdzie chcesz umieścić dane? wybierz przycisk Istniejący arkusz i naciśnij przycisk OK:
Po naciśnięciu przycisku OK dane z tabeli DimScenario zostaną skopiowane do pliku SQL Data.xslx:
Eksport danych z SQL Server do pliku Excel za pomocą funkcji Export to Excel z ApexSQL Complete
Za pomocą funkcji Export to Excel z ApexSQL Complete, add-in dla SSMS i Visual Studio, eksport danych z tabeli SQL Server do pliku Excel może być wykonany za pomocą jednego kliknięcia.
W edytorze zapytań wpisz i wykonaj następujący kod:
SELECT * FROM dbo.DimScenario ds
Z siatki wyników wybierz dane, które chcesz wyeksportować do pliku Excel, kliknij prawym przyciskiem myszy na siatce wyników i z menu kontekstowego wybierz opcję Eksportuj do Excela:
Spowoduje to otwarcie nowego skoroszytu Excela, a wybrane dane z siatki Wyniki zostaną skopiowane w pierwszym arkuszu:
Więcej na temat eksportu do Excela przez ApexSQL Complete znajdziesz na stronie Eksport do Excela.
Zobacz także:
- Tutorial: Importuj dane do Excela i stwórz model danych
- Poznaj prosty przykład działania kreatora importu i eksportu
- Jak odpytywać dane Excela za pomocą serwerów połączonych SQL Server
- Eksport do Excela