Praca z dużymi plikami MS Excel - co zrobić by Excel działał szybciej

Największe firmy mimo deklaracji posiadania "data lake" i podejmowania "data driven" decyzji posługują się często... plikami Excela. Głównymi powodami dlaczego tak robią jest brak niezbędnych umiejętności wśród pracowników. Excel jest znaną aplikacją, zatem jeśli jest do przygotowania raport czy analiza używane jest narzędzie które jest znane. Pozyskanie nowych umiejętności wymaga inwestycji; czy to czasu czy też środków na szkolenie. Excel kusi łatwością tworzenia - szybko możesz stworzyć coś w arkuszu lub kilku. Rezygnacja z takiego stylu pracy wymaga też determinacji i uporządkowana obiegu informacji wewnątrz firmy.

Co "zyskujesz" analizując dane w Excelu? Eksportowanie dużych ilości danych z wielu źródeł jest czasochłonne, nudne i podatne na błędy. Dopiero po zakończeniu raportowania wiesz, co się dzieje. Zazwyczaj jest to tylko raz w tygodniu. Do tego czasu utracono wiele okazji do optymalizacji i podjęcia działań naprawczych. Do czasu wypełnienia raportu jest on prawie nieaktualny. Niezbyt dobra sytuacja.

Konsekwencja pracy z Excelem jest powstawanie "silosów" - dane i rezultaty są bardzo lokalne. Nie musisz rezygnować z Excela całkowicie. Używaj go jednak tam gdzie ma to sens. Najczęściej najlepszym powodem będzie przygotowanie raportu ad hoc na niewielkiej ilości zagregowanych danych, sprawdzenie obliczeń, nadanie raportowi finalnego wyglądu, stworzenie nietypowych wykresów na podstawie przygotowanych danych, użycie go jako formularza do spontanicznego zebrania potrzebnych danych od współpracowników.... Microsoft Excel ma swoje miejsce w firmie ale używaj go z głową.

Jak poradzić sobie z dużymi zbiorami danych w Excelu?

Format xls, xlsx to jedne z najgorszych formatów do przechowywania danych. Pliki Excela mogą pomieścić ograniczoną ilość danych, są wolne w działaniu. Plik Excela może mieć maksymalnie 1048576 wierszy w 16384 kolumnach. Wydaje się że to dużo ale jeśli np. chcesz przeanalizować dane sprzedażowe przeciętnej wielkości firmy za ostatnie 6 miesięcy - taka liczba nie wystarczy. Mówimy o zwykłych wierszach. W Excelu najczęściej masz formuły, kilka skoroszytów. Problemy zaczynają się już od kilkunastu tysięcy wierszy - Excel zaczyna działać tak wolno że prędzej w czajniku skończy się woda na kawę niż doczekasz się obliczeń.

Jeśli chcemy tylko przechować wiersze w pliku, zdecydowanie lepszym formatem będzie plik tekstowy; csv, txt (wszystkie 'csv' są plikami tekstowymi). Jeśli z różnych powodów nie możesz zrezygnować z pracy na plikach, staraj się przechowywać swoje dane w plikach tekstowych - Excela używaj w ostateczności. Jednak by wykonać obliczenia, połączyć dane, uzupełnić dane, usunąć niepotrzebne informacje, zastąp swój ulubiony Excel czymś innym - wiele programów zrobi to szybciej, z mniejszym nakładem pracy.

Programami które idealnie nadają się do pracy z plikami, różnych typów, jest klasa oprogramowania "ETL" (extract transform load). Programy te potrafią pobierać dane z różnych żródeł, w różnym formacie, transformować dane i zapisywać je do różnych zasobów, w różnych formatach., Magiczna różdżka w świecie plików Excela i innych.

Użyj Pentaho by otworzyć jeden lub wiele dużych plików Excela

Programem który potrafi otworzyć najwiekszy nawet plik Excela jest Pentaho PDI lub darmowa jego wersja, Penatho Kettle. Progmam można pobrać z Sourceforge. Program nie wymaga nawet instalacji, wystarczy rozpakować pobrany zip (wymanane posiadanie Javy w wersji 8.x). Jak poruszać się po menu programu Pentaho Kettle nie jest przedmiotem tego artykułu. Krokiem który otwiera plik/pliki Excela jest "Microsoft Excel input". Krok ten otwiera zarówno pliki xls jak i xlsx. Domyślnie ustawienia wskazują na xls, by odczytał xlsx wystarczy zmienić "Spread sheet type (engine)" na "Excel 207".

Jeśli masz więcej niż jeden plik, możesz je dodać wszystkie w tym menu. Pamiętaj że każdy z tych plików musi mieć taką samą strukturę; te same kolumny. O tym jak sprawnie wczytać więcej niż jeden plik później w tym artykule.

Następnym krokiem jest wskazanie Pentaho z którego skoroszytu w pliku pochodzić będą dane. Przejdź do zakładki "!Sheets" i wciśnij "Get sheet name(s)". Pojawi się okno które wyświetli wszystki dostępne skoroszyty w pliku. Wybierz jeden lub wiele z nich. Jeśli odczytujesz plik xlsx i nie wybrał(a)ś we wcześniejszej zakładce typu pliku (Excel 97 czy późniejszy), może pojawić się błąd.

Po wskazaniu skoroszytu domyślnie ustawi się też opcja "Start row" i "Start column" - informacja od którego wiersza i któej kolumny Pentaho będzie czytać skoroszyt. Przejdź do zakładki "!Fields" i kliknij na "Get fields from header row". Pentaho spróbuje odczytać zawartość skoroszutu, zakładając że pierwszy wiersz skoroszytu jest nagłówkiem i zawiera nazwy kolumn. Parametry nagłówka i inne opcje znajdują się w zakładce "Content".

Gdyby Twój plik nie posiadał nagłówka lub nagłówek nie znajdował się w pierwszym wierszu, możesz wyłączyć opcję "Header", wskazać od którego wiersza rozpoczyna się odczyt danych i wpisać nazwy pól oraz określić typy danych ręcznie. Pamiętaj że Pentaho nie zawsze bezbłędnie rozpozna typ danych. Najbezpieczniej jest rozpocząć od określenia kolumny jako "String" i po pomyślnym testowym odczytaniu pliku (opcją "Preview rows"), zmienić typy danych do właściwych.

Nasz testowy plik "sales.xlsx" ma strukturę:

Jeśli potrzebujesz tylko pierwsze kolumny z tego pliku, w zakłądce "Fields" wybierz/wpisz tylko te pola. Jeśli potrzebujesz z tego arkusza np. "client_no" oraz "price" i "quantity", nie możesz pominąć kolumn pomiędzy kolumnami które Ci są niezbędne (możesz je odrzucić później w transformacji).

Odczytujemy wiele plików z katalogu

Krok "Microsoft Excel input" potrafi odczytać w jednym momencie wiele plików Excela które znajdują się we wskazanym miejscu. Pamiętaj że wszystkie te pliki mogą nazywać się różnie ale muszą mieć taką samą strukturę. Jak wskazać na wiele plików? Załóżmy że we wskazanym katalogu codziennie otrzymujesz nowy raport sprzedaży. Jeśli pliki te mają różne nazwy, np nazwa zawiera datę wygenerowania pliku, nierozsądnie byłoby za każdym razem wskazywać który plik chcesz otworzyć. W takim przypadku najlepszym rozwiązaniem będzie użycie wyrażeń regularnych. Oto przyklady:

Wildcard [RegExp]:       Otwarte pliki:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
^.+                      Każdy plik, z każdym rozszerzeniem
.+\.xlsx                 Każda nazwa pliku, rozszerzenie XLSX
plik_[0-9].xlsx          Pliki którch nazwy rozpoczynają się od 'plik_' i kończą numerami, rozszerzenie XLSX

Wyrażenie regularne wprowadzasz w polu Wildcard [RegExp] pozostawiając w polu "File/Directory" jedynie wskazanie gdzie znajduje się katalog. Bardziej uniwersalnie jest posługiwać się zmiennymi w tym wskazaniu. Jeśli Twoja transformacja znajduje się np. w katalogu "c:\uzytkownik\tranformacja" a pliki w "c:\uzytkownik\tranformacja\pliki", Wskazanie w polu "File/Directory będzie mieć następującą wartość: "${Internal.Entry.Current.Directory}/pliki/".

Jeśli chcesz odczytać pliki z podkatalogów, użyj do tego celu kroku "Get file names".

Zapisywanie danych do Excela

Otworzyłe(a)ś plik Excela jednym ze sposobów opisanych powyżej, dane zostały odpowiednio przetworzone i przygotowane. Pora zapisać je do pliku. Oczywiście do Twojego ulubionego Excela :-) Pentaho oferuje dwa kroki do zapisywania danych do plików w formacie MS Excel. Pierwszym krokiem jest "Microsoft Excel output"

Microsoft Excel output

"Microsoft Excel output" oferuje zapisanie danych do formatu "xls" (nie obsługuje nowszych formatów). W opcjach znajdziesz opcje które pozwolą Ci ustawić rodzaj czcionki, jej kolor i wielkość. Możesz to samo zrobić dla nagłówka i dodatkowo ustawić kolor tła. Możliwe jest ustawienie by plik powstawał za każdym razem od początku lub był przyrostowym. Komponent ten oferuje też automatyczny podział pliku na wiele co x wierszy.

Excel Writer

Drugim krokiem jest "Excel Writer". Używając tego kroku możemy mieć pełną kontrolę nad docelowym plikiem. Używając tego komponentu możemy wybrać format pliku ("xls" lub "xlsx"), zapisywać do konkretnych skoroszytów w pliku, używać szablonów. Zapisywanie danych jest też szybsze - co jest widoczne przy dużej ilości wierszy.

Dokładny opis wszystkich opcji tego kroku znajdziesz pod adresem.

Pentaho Kettle daje ogromne możliwości manipulacji plikami Excel'a. Możesz otworzyć każdy plik, czytać z każdego skoroszytu a po wykonaniu pracy na danych zapisać je w formie która jest dla Ciebie odpowiednia. Penatho umożliwia automatyzację odczytu i zapisu; odczyt z katalogu, podkatalogów.. pojedyncze pliki i hurtowo... zapis do dowolnej struktury katalogów.

Najsprawniej będziesz pracować z Excelem... nie używając Excela. Pentaho umożliwia nie tylko odczyt i zapis danych; w Pentaho możesz tworzyć także formuły Excela i formatować dane i zapisywać gotowe wiersze do plików. Zobacz w następnym artykule jak Pentaho robi to szybciej i bez błędów.