jak eksportować dane z SQL Server do Excela

jak eksportować dane z SQL Server do Excela

w poprzednim artykule, SQL i Excel: dlaczego potrzebujesz obu, pokazaliśmy, że dla wielu firm wykorzystanie zarówno SQL Server, jak i Excel jest optymalnym podejściem do zarządzania danymi. W przypadku organizacji, które intensywnie używają SQL Server i Excel, często istnieje potrzeba eksportu danych z SQL Server do Excela. Najczęstszymi przyczynami uzyskania danych w programie Excel są:

• twórz wizualizacje danych, takie jak wykresy i wykresy do wykorzystania w raportach
• agreguj lub analizuj dane na wykresach przestawnych
• zestawiaj dane z wielu źródeł w celu dalszej transformacji
• Udostępnij dane użytkownikom spoza bazy danych, aby mogli je przeglądać lub przetwarzać

w tym artykule przyjrzymy się jak eksportować dane z SQL Server do Excela. Pierwsza metoda wykorzystuje Kreator importu i eksportu SQL Server, który może być używany do eksportowania danych do wielu typów docelowych, a nie tylko Excela. Druga metoda wykorzystuje natywną funkcjonalność w programie Excel. Trzecia metoda wykorzystuje dodatek SQL Spreads dla programu Excel, który zapewnia funkcjonalność wykraczającą poza proces eksportu. Istnieją plusy i minusy każdej metody, które podsumujemy na końcu artykułu.

Metoda (1): eksportowanie przy użyciu Kreatora importu i eksportu SQL Server

Kreator importu i eksportu SQL Server można uruchomić z poziomu SQL Server Management Studio lub jako samodzielna aplikacja.

z poziomu SQL Server Management Studio kliknij bazę danych, z której chcesz wyeksportować dane. Używamy bazy danych demo w naszym przykładzie, więc kliknij na to, a następnie kliknij prawym przyciskiem myszy i wybierz zadania > Eksportuj dane.

zostanie wyświetlona strona powitalna, a następnie możesz kliknąć „Dalej”.

na następnym ekranie Wybierz źródło danych. Aby wyeksportować z SQL Server, wybierz 'SQL Server Native Client’.

po wybraniu opcji 'SQL Server Native Client’ szczegóły dotyczące źródła danych zostaną wypełnione przez kreatora.

kliknij „Dalej”, aby przejść do następnego ekranu, gdzie wybierasz miejsce docelowe eksportu, którym dla nas jest Excel.

po wybraniu programu Excel jako miejsca docelowego należy podać lokalizację pliku Excel. Po kliknięciu przycisku „Dalej” może pojawić się następujący komunikat:

operacja nie została zakończona.
dodatkowe informacje:
” Microsoft.As.OLEDB.16.0 ’ dostawca nie jest zarejestrowany na maszynie lokalnej. (System.Dane)

powodem, dla którego pojawia się ten Komunikat, jest to, że po uruchomieniu Kreatora importu i eksportu SQL Server z SSMS uruchamiana jest 32-bitowa wersja kreatora (ponieważ SSMS jest 32-bitowy), a system operacyjny jest 64-bitowy. Więcej informacji na ten temat można znaleźć tutaj.

aby to obejść, musisz zamknąć SSMS i przejść do menu Start systemu Windows i wyszukać 64-bitową wersję Kreatora importu i eksportu serwera SQL z menu Start. Należy pamiętać, że ta wersja zostanie zainstalowana tylko na komputerze, jeśli masz zainstalowany SQL Server.

po uruchomieniu samodzielnej wersji kreatora można przejść przez opisane dotąd kroki, które polegały na określeniu źródła i miejsca docelowego kopii danych. Teraz musimy zdefiniować co chcemy skopiować i (opcjonalnie) skonfigurować operację kopiowania.

po kliknięciu „Dalej” na ekranie „Wybierz miejsce docelowe” określasz, czy chcesz wyeksportować jedną lub więcej kompletnych tabel lub widoków lub podzbiór danych za pomocą zapytania SQL. W naszym przypadku eksportujemy z jednej tabeli, więc możemy pozostawić zaznaczoną opcję domyślną.

następnie edytujemy mapowania kolumn, klikając przycisk ” Edytuj mapowania…”. Ekran mapowania kolumn umożliwia wprowadzanie zmian w nazwie i typie danych kolumn, które zostaną utworzone w programie Excel przez operację kopiowania. W naszym przykładzie zmienimy typ danych dla kolumny 'order_date’ z VarChar na DateTime. Pozostałe pozostawimy jako domyślne ustawienia kreatora. Kliknij „Dalej”. Następny ekran kreatora podsumowuje, że źródłem jest tabela SQL o nazwie sales, a miejscem docelowym będzie arkusz o nazwie „Sprzedaż” w skoroszycie programu Excel. W razie potrzeby można zmienić nazwę arkusza.

po zakończeniu kliknij „Ok”, aby zamknąć okno dialogowe, a następnie „dalej”.

jeśli którekolwiek z mapowań, które określiliśmy, może nie odnieść sukcesu w procesie eksportu, Kreator importu i eksportu serwera SQL wyświetli stronę mapowanie typu danych przeglądu. Ta strona podświetli konwersje, które Kreator musi wykonać, aby obsłużyć określone mapowania. Wskazuje również, co zrobi Kreator, jeśli podczas konwersji wystąpi błąd. Może zignorować błąd lub spowodować niepowodzenie eksportu. W naszym przykładzie kilka kolumn zostało oznaczonych: te, w których jest konwersja z nvarchar(max) na jego odpowiednik w Excelu, długi tekst, a także konwersja z VarChar na DateTime dla kolumny 'order_date’. Domyślnym ustawieniem jest to, że kreator nie powiedzie się, jeśli wystąpi błąd, więc zostawimy tę opcję na miejscu.

Uwaga: Ten kreator importu i eksportu SQL używa SSIS pod maską; innymi słowy, za każdym razem, gdy uruchamiasz kreatora, tworzysz pakiet SSIS. Dlatego masz możliwość zapisania go do wykorzystania w przyszłości.Kliknij „Dalej”. Kreator oferuje teraz możliwość zapisania konfiguracji kreatora jako pakietu SSIS oprócz domyślnej opcji natychmiastowego uruchomienia kopii.

po prostu zostawiamy zaznaczoną opcję domyślną, a następnie klikamy przycisk „Dalej”.

Kreator wyświetla teraz podsumowanie skonfigurowanych kroków.

kliknij „Zakończ”, a kreator wyświetli teraz końcowy ekran, który jest wynikiem wykonania. W naszym przypadku wykonanie zakończyło się sukcesem i widzimy, że 5484 rekordów zostało skopiowanych do arkusza „zapytanie” w naszym skoroszycie programu Excel.

możemy sprawdzić, czy dane zostały wyeksportowane z SQL Server do Excela, otwierając plik, który podaliśmy i sprawdzając arkusz 'sales’.

teraz, gdy dane są w programie Excel, możemy tworzyć wizualizacje lub agregacje lub dowolne inne przetwarzanie, które chcemy wykonać. Na przykład, możemy utworzyć wykres przestawny, aby wyświetlić sumę na element na region.

Metoda (2): Eksportowanie przy użyciu natywnej funkcjonalności w programie Excel

chociaż sama metoda jest podobna, Microsoft wprowadził ulepszenia w ostatnich kilku wersjach sposobu pobierania danych z innych źródeł do programu Excel. Główną zmianą było dodanie kreatorów i narzędzi importu danych Power Query jako natywnego komponentu w programie Excel 2016. Są one dostępne z Edytora zapytań mocy, chociaż nie jest on wyświetlany jako taki na Wstążce programu Excel-zamiast tego jest oznaczony jako Get & Transform Data group na karcie Dane wstążki programu Excel.

kroki opisane poniżej są ważne dla programu Excel 2016 i Później.

aby rozpocząć eksport z SQL Server do Excela, kliknij Dane > Pobierz dane > z bazy danych > z bazy danych SQL Server.

następnie zostaniesz poproszony o wejście na serwer bazy danych, aby się połączyć i opcjonalnie określić bazę danych. Jeśli nie określisz bazy danych, możesz wybrać jedną z dostępnych na następnym ekranie.

Kliknij „Ok”. Następnie zostaniesz poproszony o podanie szczegółów uwierzytelniania. Domyślnie jest to System Windows, więc możesz śmiało kliknąć „Połącz”.

następny ekran umożliwia nawigację po bazach danych i tabelach na podanym serwerze baz danych. Kliknij bazę danych „demo”, aby ją rozwinąć, a następnie kliknij tabelę „sprzedaż”. Dane dla wybranej tabeli są wyświetlane w prawym oknie. Możemy teraz kliknąć przycisk „Załaduj”, aby natychmiast pobrać dane do programu Excel. Alternatywnie możemy kliknąć na 'Transform Data’, aby wykonać transformacje danych (np. zmienić typy danych, wyczyścić niektóre dane, wykonać obliczenia). W tym przykładzie po prostu załadujemy dane tak, jak są, więc śmiało i kliknij „Załaduj”.

dane są teraz eksportowane z tabeli w SQL Server i ładowane do nowego arkusza w programie Excel.

to wszystko, co musimy zrobić, aby wykonać prosty eksport pełnej tabeli z SQL Server do Excela. Oprócz prostej procedury, którą tu opisaliśmy, kreatory i narzędzia importu danych Power Query zapewniają sposoby „kształtowania” danych ze źródeł zewnętrznych. Na przykład możesz usunąć kolumnę, zmienić typ danych, scalić tabele, aby spełnić określone potrzeby.

Metoda (3): Eksportowanie za pomocą dodatku SQL Spreads dla programu Excel

jeśli nie masz jeszcze zainstalowanego dodatku SQL Spreads dla programu Excel, możesz pobrać kopię tutaj.

po zainstalowaniu spreadów SQL zobaczysz, że został dodany jako nowa karta wstążki; przejdź tutaj i kliknij przycisk Tryb projektowania.

w panelu projektant spreadów SQL po prawej stronie kliknij przycisk Edytuj, aby otworzyć okno dialogowe połączenia z serwerem SQL.

wprowadź nazwę serwera SQL w polu Nazwa serwera SQL. Musisz również wybrać metodę uwierzytelniania: Windows-logowanie (uwierzytelnianie Windows) lub uwierzytelnianie SQL Server (przy użyciu nazwy i hasła ustawionego w SQL Server). Uwierzytelnianie systemu Windows jest bezpieczniejszą z dwóch opcji.

kliknij OK. Spready SQL spróbują połączyć się z bazą danych. Jeśli połączenie się powiedzie, Twoje bazy danych pojawią się w Panelu projektant spreadów SQL.

możemy teraz rozwinąć bazę demo i wybrać tabelę sprzedaży.

po kliknięciu tabeli dane są natychmiast eksportowane z serwera SQL i kopiowane do arkusza o nazwie „Sprzedaż” w programie Excel.

to jest to! Udało nam się wyeksportować dane z SQL Server do Excela za pomocą zaledwie kilku kliknięć.

istnieje jeszcze kilka opcji dostępnych dla nas w SQL Spreads Designer. Na przykład możemy:

• filtruj dane eksportowane z SQL Server
• ustaw, czy określone kolumny mają być wyświetlane i/lub tylko do odczytu
• użyj wartości wyszukiwania, aby wyświetlić tekst zamiast id kolumny
• Sortuj dane
• dokonuj aktualizacji w programie Excel i zapisz zmiany w SQL Server

przyjrzymy się niektórym z tych opcji w przyszłym poście na blogu.

podsumowanie

możliwość eksportu danych z SQL Server do Excela jest powszechnym wymogiem w większości firm. Przyjrzeliśmy się trzem metodom wykonywania tego codziennego zadania. Pierwszy korzysta z Kreatora importu i eksportu SQL Server, który jest natywną funkcjonalnością w SQL Server. Druga metoda wykorzystuje natywne narzędzia „Get & Transform” w programie Excel. Trzeci korzysta z dodatku SQL Spreads dla programu Excel.

więc jakiej metody powinieneś użyć?

jeśli normalnie pracujesz z bazami danych, a SSMS jest twoim drugim domem, prawdopodobnie poczujesz, że Kreator importu i eksportu SQL Server jest najlepszym rozwiązaniem dla ciebie. Jeśli jesteś użytkownikiem programu Excel, warto pracować z poziomu samego programu Excel, aby eksportować dane z serwera SQL. Obie dwie opcje programu Excel, na które spojrzeliśmy, są solidne i łatwe w użyciu, ale spready SQL mają wyraźną zaletę, ponieważ pozwalają użytkownikom aktualizować dane bezpośrednio w programie Excel, a następnie zapisywać dane z powrotem na serwerze SQL. Jest to ogromna zaleta dla tych organizacji, które lubią to, co najlepsze z obu światów: łatwość obsługi i znajomość programu Excel, Moc i integralność danych związanych z SQL Server.

Wypróbuj SQL Spreads, aby zobaczyć, jak szybko i łatwo jest eksportować dane z SQL Server do Excela; pobierz bezpłatną wersję próbną już dziś.