Pliki dbf otwieranie i obróbka w Excelu (PowerQuery)

Co to jest PowerQuery? Jak korzystać z PowerQuery do pobierania plików? Jak w Excelu używać źródła ODBC? Jak łączyć Excela z innymi źródłami danych bez VBA? Co to jest źródło danych użytkownika? – to najważniejsze pytania na które odpowiada poniższy artykuł :)

Częstym problemem, z którym spotykam się w trakcie prowadzonych przeze mnie szkoleń jest obsługa plików dbf. Nie mówię tutaj o małych pliczkach, które potrafi otworzyć zwykły Excel ale o wielkich zbiorach danych, często składających się z więcej niż milion wierszy. Jak ugryźć tak dużą porcję danych jeżeli nie jesteśmy specjalistami od języka SQL czy baz danych? A gdyby tak dało się stworzyć tabelę przestawną z takiego dużego dbfa ? :)

No to jedziemy :)

Pliki dbf, chociaż na pierwszy rzut oka mogą wydawać się zupełnie pomieszanymi i nieskładnymi zestawami danych są tak naprawdę plikami bazodanowymi (dBASE, Clipper, Visual FoxPro, xBase) to z kolei oznacza, że mają one konkretną strukturę kolumn i wierszy (rekordów), którą możemy odtworzyć np. w Excelu.

Co w sytuacji, gdy wierszy (rekordów) w pliku dbf jest więcej niż 1048576, czyli więcej niż potrafi przechować arkusz Excela?

Możemy wtedy skorzystać z BEZPŁATNEGO dodatku do Excela, który nazywa się PowerQuery (w Office 2016 jest on już wbudowany na stałe i znajduje się na karcie Daneà”pobieranie i przekształcanie”). Power Query potrafi w łatwy sposób pobierać, przekształcać i rozbudowywać dane z najróżniejszych źródeł. Bardzo istotne jest też to, że nie ma konieczności „zaciągania” danych bezpośrednio do Excela, co ma znaczenie zwłaszcza gdy tych danych jest więcej niż Excel może przechować. Możemy stworzyć tylko połączenie do danych, dodać je do tzw. Modelu danych i korzystać z nich generując np. tabelę przestawną. Oczywiście Power Query ma mnóstwo innych przydatnych funkcji ale o tym napiszę inny artykuł (albo nawet całą serię artykułów ;)

Jak dodać PowerQuery do Excela (Office 2016)?

Jeżeli korzystasz z Excela 2016, dodatek Power Query masz już wbudowany :) znajduje się on na karcie Dane w sekcji „pobierz i przekształć”.

A co w przypadku gdy na naszym komputerze znajduje się Excel 2010 lub 2013?

W takiej sytuacji musimy samodzielnie pobrać i zainstalować dodatek. Znajduje się on na oficjalnej stronie Microsoftu (link poniżej)

https://www.microsoft.com/pl-pl/download/details.aspx?id=39379

Pobieramy, instalujemy i w Excelu pojawi się nam dodatkowa zakładka o nazwie „Power Query”.
No dobrze skoro mamy już wszystkie potrzebne narzędzia możemy wreszcie przejść do połączenia się z naszym wielkim plikiem dbf :)

Po pierwsze musimy przygotować odpowiednie źródło danych użytkownika. Czyli połączenie ODBC (więcej o ODBC możecie przeczytać tutaj https://pl.wikipedia.org/wiki/ODBC), które zawiera nazwę sterownika, bazy danych, użytkownika, hasło i inne potrzebne parametry konieczne do połączenia jednego programu/źródła danych z innym :)

Teoretycznie źródła danych ODBC znajdują się w następującej lokalizacji: panel sterowania –> narzędzia administracyjne –> źródła danych ODBC ale… problem polega na tym, że póki co większość z nas używa Windows 7 i to w wersji 64 bitowej… co to oznacza ? Ano oznacza to, że po wejściu w ścieżkę którą podałem (panel sterowania –> narzędzia administracyjne –> źródła danych ODBC) i kliknięciu w przycisk „dodaj”

Oczom naszym ukaże się tylko jeden jedyny 64bitowy sterownik jaki mamy w systemie czyli sterownik do SQL Servera.

A przypominam, że my chcemy się połączyć z plikiem dbf więc potrzebujemy sterownika który nam to umożliwi. Żeby było śmiesznie ten sterownik jest wbudowany w Windowsa tylko…musimy się jakoś do niego dobrać :) Konieczne jest więc uruchomienie źródeł danych ODBC ale w wersji 32 bitowej… Nazywa się on „odbccad32”.

Żeby to uzyskać musimy wejść do katalogu „SysWOW64” który znajduje się w poniższej lokalizacji:

C:\Windows\SysWOW64

Po wejściu do tego katalogu naciskamy na klawiaturze kilka razy literę „o” co sprawi że zostaniemy przeniesieni w dół gdzie znajdują się pliki zaczynające się na literę „o” i oczom naszym pojawi się tak potrzebny plik „odbccad32”

Uruchamiamy go i oczom naszym ukazuje się program dokładnie taki jak przed chwilą w panelu sterowania :) WIĘC PO CO TO WSZYSTKO ROBILIŚMY ? ano po to że po naciśnięciu w przycisk „dodaj” pojawi się nam lista sterowników 32 bitowych. I tam dopiero znajduje się potrzebny nam driver „Microsoft dbase (dbf)”  :)

Po wybraniu drivera klikamy w zakończ i pojawia się takie okno:

Wpisujemy w nim nazwę źródła danych np. „kalarepa”.

ODZNACZAMY ptaszka przy „użyj bieżącego katalogu” i klikamy w „wybierz katalog” dzięki czemu możemy wyklikać się do katalogu w którym znajduje się nasz plik dbf. (U mnie jest to ścieżka:  „C:\Users\dabek\Desktop\Rb28s”)

Następnie klikamy OK i okno się zamyka. Wtedy możemy też zamknąć program odbccad (już nie będzie nam potrzebny)

WRESZCIE możemy przejść do Excela.

Poniższy przykład będę pokazywał na Excelu 2016. Wchodzę więc w kartę dane –> nowe zapytanie i wybieram „Z innych źródeł” –> „ze źródła ODBC”. Następnie wybieram naszą stworzoną wcześniej kalarepę i klikam OK.

W kolejnym oknie wybieram po lewej stronie „Domyślne lub niestandardowe”

Nie wpisuję nic w polu właściwości tylko klikam „Połącz”

W nawigatorze rozwijam listę ze ścieżką do katalogu gdzie jest mój plik dbf i… już go tam widzę w podglądzie

Klikam w „załaduj do…”

Ze względu na to że mam ponad milion wierszy wybieram opcję „utwórz tylko połączenie”  (bo tych wierszy w pliku jest więcej niż Excel potrafi zaciągnąć do arkusza) i „dodaj do modelu danych” (żeby można było tworzyć z tego pliku tabelę przestawną :)

Klikam w „załaduj” i czekam aż wczytają się wszystkie wiersze do modelu danych (w panelu po prawej stronie)

Jak wszystko się ładnie wczyta, to klikam w tworzenie tabeli przestawnej czyli Wstawianie –> Tabela przestawna.

Automatycznie zaznaczyła się opcja „użyj modelu danych tego skoroszytu” spowoduje to, że nasza tabela przestawna „pociągnie” dane z tych, które się zaczytały poprzez nasze Power Query.

That’s all folks :)  Jak macie jakieś sugestie czy pytania, piszcie na michal@hintmine.com :)