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).

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

Do celów demonstracyjnych najważniejszych parametrów, użyję pliku excel (zapisany w MS Office 2010 jako xlsx). Zawartość pliku demo :

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$]');

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$]');

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'

W wyniku dostaniemy :

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.