Import danych z Excela do bazy SQL Server za pomocą T-SQL

Integracja danych z plików

Czytanie plików Excel za pomocą T-SQL jest bardzo użyteczne, ale potrafi dostarczyć też problemów, szczególnie na etapie przygotowania środowiska. Sam spędziłem parę ładnych godzin na debugowaniu błędu związanego z czytaniem pliku (chodziło o dostęp do katalogów temp), dlatego postanowiłem zebrać wszystko co ważne w jednym miejscu. O ile sama składnia OPENROWSET jest dość łatwa, o tyle potencjalne problemy związane z ustawieniami środowiskowymi czy właściwościami interfejsów OLE DB mogą pożreć nam sporo czasu na ich rozwiązanie.
Znajdziesz tutaj najważniejsze informacje, które mogą się przydać do czytania plików Excel z poziomu T-SQL a także opis najczęściej pojawiających się błędów i ich rozwiązań.


Funkcja OPENROWSET

Do czytania plików Excel z poziomu T-SQL, idealnie nadaje się funkcja OPENROWSET. Szczególnie wtedy gdy chcemy szybko wrzucić zawartość jakiegoś Excela do bazy aby wykonać na informacjach w nim zawartych jakichkolwiek działań lub zależy nam po prostu aby wykonać import za pomocą skryptu T-SQL.
Jest to funkcja tabelaryczna, zwracająca w wyniku zbiór wierszy, który możemy traktować jak zwykłą tabelę. OPENROWSET podobnie jak inne obiekty tabelaryczne (widoki, zmienne tabelaryczne, tabele tymczasowe etc.) może być użyty tylko w klauzuli FROM zapytania.
Aby móc korzystać z OPERNROWSET potrzebujemy włączonej możliwości wykonywania kwerend rozproszonych. Jest to jedno z ‘advanced optinos’ naszego serwera – warunek konieczny do dalszych przykładów :

-- włączenie na serwerze obsługi kwerened rozproszonych
exec sp_configure 'show advanced options',1
reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

Teraz w zależności od wersji w jakiej został zapisany plik Excela treść naszego zapytania może wyglądać następująco :

-- Przykład z excel zapisanym w formacie zgodnym z OFFICE 1997-2003 .xls 
-- stary sterownik OLEDB 4.0. Na wymarciu :) sugeruję stosowanie ACE.

select * FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\EXCEL1997-2003_TO_READ.xls', 
'select * from [Arkusz1$]');

-- ten sam plik zapisany w trybie zgodności 1997-2003 xls czyli OFFICE 2003 
-- nowy uniwersalny provider ACE OLE DB. Odwolanie do Excel 8.0

SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0', 
'Excel 8.0;Database=D:\EXCEL1997-2003_TO_READ.xls', 
'SELECT  * FROM [Arkusz1$] ');

-- plik zapisany w OFFICE 2010 xlsx nowy uniwersalny provider ACE OLE DB
-- istotne jest odwołanie do Excel 12.0;

SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',                      -- provider_name
'Excel 12.0;Database=D:\EXCEL2010_TO_READ.xlsx', -- provider_string
'SELECT * from [Arkusz1$]')                      -- query lub sam [Arkusz1$], wtedy bez '

Powyższe zapytania działają, jeśli środowisko w którym pracujemy jest odpowiednio skonfigurowane. W dalszej części tego artykułu, znajdziesz wszystkie najważniejsze aspekty związane z konfiguracją serwera, składnią OPENROWSET oraz najczęstszymi problemami z jakimi możesz się zetknąć przy próbie czytania z Excela.

Składnia OPENROWSET

W pokazanych przykładach, funkcja OPENROWSET przyjmuje 3 parametry :
OPENROWSET( provider_name, provider_string, query).

Parametr ’provider_name

Tu podajemy nic innego jak nazwę (friendly name) interfejsu OLE DB. W przykładach użyłem dwóch interfejsów OLE DB za pomocą których możliwe jest uzyskanie dostępu do plików Excel. Pierwszy to „stary” JET.OLEDB.4.0 który czyta Excela tylko do wersji 2003 (xls) i którego nie polecam stosować (czasem jednak nie mamy wyboru, więc stąd ten przykład).
Drugi, sugerowany to uniwersalny Microsoft.ACE.OLEDB.12.0 dla plików zapisanych w wersjach 1997-2010. Jeśli nie jesteś pewien czy masz go zainstalowanego – sprawdź sterowniki np. w ODBC (biblioteka ACEODBC.DLL).
Reading_excel_odbc_providers
W moim przypadku mam dostępny Microsoft.ACE.OLEDB.12.0 w wersji 14.
Jeśli nie masz jej dostępnej – można go pobrać i zainstalować np. stąd : Microsoft Access Database Engine 2010 Redistributable.
UWAGA ! Jeśli zainstalowałeś ten interfejs, konieczne jest nadanie dla niego stosowanych uprawnień – opisane jest to poniżej – w paragrafie Problemy.

Parametr ’provider_string

W provider_string, definiujemy zbiór (kolekcję) parametrów tzw. extended properties. Obowiązkowe są dwa – wersja pliku oraz ścieżka. Wersje plików Excel czytane przez Microsoft.ACE.OLEDB.12.0 :

Typ pilkuExtended Properties

Excel 97-2003 Workbook (.xls) „Excel 8.0”
Excel 2007-2010 Workbook (.xlsx) „Excel 12.0 Xml”
Excel 2007-2010 Macro-enabled workbook (.xlsm) „Excel 12.0 Macro”
Excel 2007-2010 Non-XML binary workbook (.xlsb) „Excel 12.0”

Do celów demonstracyjnych najważniejszych parametrów, użyję pliku excel (zapisany w MS Office 2010 jako xlsx). Zawartość pliku demo :
excel_file_example1
Wykonanie kwerendy czytającej tego excela z minimalną wymaganą liczbą parametrów :

SELECT * FROM OPENROWSET(
-- provider_name
'Microsoft.ACE.OLEDB.12.0', 
-- provider_string 
'Excel 12.0;Database=D:\EXCEL2010_TO_READ.xlsx', 
-- query
'SELECT  * FROM [Arkusz1$]');

Reading_excel_01
Jak widać wynik powyżej różni się od faktycznej zawartości pliku – jest to zależne od ustawień parametrów opcjonalnych .

Najważniejsze z opcjonalnych extended properties to :
HDR yes/no – (default = yes) czy ma czytać nazwy kolumn z pierwszego wiersza. Jeśli ustawimy na no – nazwy kolumn będą utworzone jako F1,F2 etc.
IMEX – ImportMixedTypes – interfejsy OLE DB, które wykorzystujemy, mają właściwość „zgadywania” typu danych w kolumnie. Właściwość IMEX odnosi się tylko do kolumn zawierających mieszane typy danych i stara się automatycznie określić typ na podstawie pierwszych N wartości (wartość N określa parametr MaxScanRows). Jeśli na tej podstawie zostanie wybrany np. typ integer, to wszystkie wartości nie pasujące do tego typu, zostaną zamienione na NULL, co zazwyczaj nie jest oczekiwanym zjawiskiem :).

W naszym przykładzie, w kolumnie KolC mamy 2 wartości tekstowe i 3 daty, to w wyniku zapytania otrzymamy kolumnę typu datetime a wszystko co nie pasuje do tego typu zostanie zamienione na NULL (tak jak miało to miejsce w przykładzie powyżej).
W zdecydowanej większości przypadków, gdy chcemy sami określać typ i czyścić dane – ustawiamy parametr IMEX=1, wtedy każda kolumna będzie miała narzucony typ danych jako text :

SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0', 
-- provider_string z IMEX
'Excel 12.0;IMEX=1;Database=D:\EXCEL2010_TO_READ.xlsx', 
'SELECT  * FROM [Arkusz1$]');

Reading_excel_02

Parametr ’query

W tym parametrze określamy zakres danych jakie chcemy wyciągać. Odpytywany plik Excel, może posiadać kilka arkuszy – do konkretnego odwołujemy się po nazwie + $. Aby określić zakres czytanych komórek można to zrobić np. tak Arkusz1$A1:C3 – w wyniku czego otrzymamy tylko dwa wiersze (plus header). Query może przyjmować podstawowe komendy obsługiwane przez danego providera np. grupowanie danych lub być poprostu nazwą arkusza [Arkusz1$] (bez apostrofów).

SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;IMEX=1;Database=D:\EXCEL2010_TO_READ.xlsx', 
-- query jako zapytanie
'SELECT kolC, MAX(KolA) as maxi FROM [Arkusz1$A1:C3] group by KolC'

excel_file_example2
W wyniku dostaniemy :
Reading_excel_03
Warto zwrócić uwagę na fakt konwersji na właściwy typ danych w KolC (datetime), nawet przy ustawionym IMEX=1, ponieważ wartości pobierane z tej kolumny są spójne –same daty (bierzemy pod uwagę tylko dwa pierwsze wiersze z danymi A1:C3).

Problemy

Niestety jak to w życiu bywa, wszystko jest ok., ale nie działa. Akurat w przypadku czytania Excela z poziomu SQL zdarza się to nadzwyczaj często bo istnieje wiele uwarunkowań środowiskowych, które mogą nam skutecznie przeszkodzić w zabawie.
Najczęstsze przyczyny problemów z czytaniem excela za pomocą OPENROWSET:

  • Jeśli korzystamy z OLE DB ACE – konieczne przydzielenie odpowiednich uprawnień dla tego dostawcy czyli po instalacji konieczne jest wykonanie :
    USE [master]  
    GO  
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1  
    GO  
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1  
    GO
  • Plik do którego się odwołujemy musi być zamknięty aby móc go czytać z poziomu T-SQL przez OPENROWSET . Niestety SQL Server potrzebuje pliku na wyłączność. Jeśli jest już otwarty, otrzymamy komunikat :
    Msg 7399, Level 16, State 1, Line 2
    The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 2
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
  • Konto na którym działa usługa SQL Server musi mieć dostęp do zasobów które chcesz czytać, wynika to z konieczności obsługi różnych metod uwierzytelniania (Windows lub SQL Authentication).
  • Problem związany z SQL Server x86 – użytkownik, który wykonuje kwerendę musi mieć dostęp do katalogów tymczasowych używanych przez SQL Server. Jest to podyktowane tym, że tworzona jest kopia tymczasowa pliku do którego się odwołujemy w OPENROWSET w katalogu temp. Potrzebny dostęp r/w do katalogów wskazywanych przez zmienne %TEMP% oraz %TMP% konta na którym działa SQL Server.
    Microsoft sugeruje w tym temacie dwa rozwiązania, które sprowadzają się do tego samego – nadanie uprawnień do aktualnie używanych katalogów TEMP dla użytkowników lub utworzenie nowego, dedykowanego katalogu tymczasowego np. c:\TEMP i ustawienie zmiennych środowiskowych dla konta na którym działa SQL Server, aby %TEMP% i %TMP% wskazywały właśnie na ten nowy zasób.
    W obu przypadkach nadajemy uprawnienia R/W dla wszystkich zainteresowanych użytkowników do tego katalogu oraz full rights dla konta SQL Server.
  • Inne spotykane problemy to kompatybilność sterowników – sprawdź czy zainstalowana wersja OLE DB jest odpowiednia dla twojego serwera x86 / x64.

Niestety obsługa błędów w OLE DB ACE została potraktowana bardzo ogólnie i w większości przypadków tu opisywanych, treść błędu będzie podobna – unknown error.

5 Responses

  • Mam problem ze sterownikiem do excela(mam go w wersji 2016) – „The 32-bit OLE DB provider „Microsoft.ACE.OLEDB.16.0″ cannot be loaded in-process on a 64-bit SQL Server.”

  • Poradnik świetny. Szkoda, że autor nie podał przykładu jak połączyć się z serwerem analitycznym. Niestety ja mam właśnie problem z połączeniem się z serwerem analitycznym SQL Server Analysis Services (SSAS) za pomocą funkcji: OPENROWSET().
    Poniżej zapytanie które próbuję uruchomić:
    SELECT *
    FROM
    OPENROWSET(’MSOLAP.8′, 'Integrated Security=SSPI;Persist Security Info=True;Data Source=host\nazwa_instancji;Initial Catalog=HurtownieDanych’,
    'SELECT Measures.members ON COLUMNS,
    [Towar Wymiar].[Kategoria_Towar].&[GPS] ON ROWS
    FROM [Baza Relacyjna Kostka]’)
    Otrzymuję taki komunikat błędu:
    Msg 7399, Level 16, State 1, Line 54
    The OLE DB provider „MSOLAP.8” for linked server „(null)” reported an error. Access denied.
    Msg 7350, Level 16, State 2, Line 54
    Cannot get the column information from OLE DB provider „MSOLAP.8” for linked server „(null)”.
    Chodzi chyba o brak uprawnień dostępu do zasobów. Próbowałem już różnych sposobów – niestety nie udało się. Serwer relacyjny jak i serwer analitycznej jest w wersji 2017 (Developer Edition)
    Proszę o pomoc.

  • Fajny artykuł. Przez długi czas nie wiedziałem jak szybko i efektywnie pobrać dane z excela, teraz już wiem, dziękuję 🙂
    W moim przypadku artykuł był w 99% kompletny – brakło tylko jednej wzmianki: najlepiej uruchomić SQL jako administrator. Mimo iż u nas w firmie mam uprawnienia administratorskie do konta AD Windows, jeżeli nie uruchomię Management Studio jako Administrator, będzie sypało błędem 7303.
    Pozdrawiam!

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.