(Nie)dozwolone związki Excela i bazy danych - część 1

Wstęp

W tym wpisie (lub wpisach, bo co najmniej jeszcze jeden temat mi po głowie chodzi) chciałbym przybliżyć różnego rodzaju zabawy, aby MS SQL i Excel wzajemnie się uzupełniały.

Przykładowy problem

Załóżmy, że klient prowadzi sobie "bazę danych" w postaci tabeli Excela. Do pewnego momentu było to dla niego wystarczające, ale plik cały czas się rozrasta, korzystają z niego różne osoby...trzeba coś zrobić, żeby było wygodniej i bezpieczniej.

Jeśli rekordów w tabeli nie jest dużo to nie ma problemu - tworzymy bazę danych, tabelki, relacje, wklepujemy dane i po sprawie. Co jednak zrobić, gdy danych jest 1 000, 10 000, 1 000 000??

Jako przykład weźmy sobie tabelkę pracowników: Lista przykładowa

Rozwiązania

Mogę zaproponować dwa rozwiązania. Do każdego jest potrzebny MS SQL oraz Excel. Do pierwszego (prostszego) sposobu jest wymagany oprócz tego Access, a przy drugim nic więcej nie będziemy potrzebować.

Rozwiązanie 1: Access - niech się w końcu weźmie do roboty

Posiadając Office'a w wersji co najmniej Professional lub samodzielnego Accessa nie będziemy mieli z tą operacją żadnego problemu. Otwieramy go i wybieramy Pusta baza dancyh, następnie przechodzimy na dane zewnętrzne i wybieramy Excela. W oknie które się pojawi wskazujemy plik z którego chcemy pobrać dane, domyślnie jest zaznaczona prawidłowa opcja:

Importuj dane źródłowe do nowej tabeli w bieżącej bazie danych.

W następnym oknie zobaczymy dane z Excela w nowej tabeli Accessa. Po kliknięciu "Dalej" możemy ujrzeć taki komunikat:

Pierwszy wiersz zawiera pewne dane, które nie mogą być użyte jako prawidłowe nazwy pól programu Microsoft Access. W tych przypadkach kreator automatycznie przypisze prawidłowe nazwy pól.

Pierwszy wiersz zawiera nagłówki kolumn

Następne okno zaproponuje nam zdefiniowanie klucza podstawowego. W naszym przypadku w tabeli mamy Nr ID, więc użyjemy tego jako klucza podstawowego - Wybieram własny klucz podstawowy i wskazujemy odpowiednie pole.

W następnym oknie podajemy nazwę tabeli i klikamy Zakończ.

Mamy już przerobioną tabelę Excela do postaci bazy danych. Jednak to jeszcze nie koniec. Jeszcze musimy doprowadzić tabelę do III postaci normalnej - Postać normalna bazy danych. Trzeba pozbyć się redundancji (nadmiarowości). Oczywiście w tym celu również nam pomoże Access.

Wchodzimy w zakładkę Narzędzia bazy danych, a następnie analizuj tabelę. Otworzy się nam kreator analizy tabel, dzięki któremu doprowadzimy bazę do III postaci normalnej.

Klikamy dwa razy dalej, wybieramy odpowiednią tabelę, a następnie wybieramy, albo samodzielne wybranie relacji, albo pozwolimy kreatorowi się wykazać (proponuje to drugie rozwiązanie, tym bardziej, że później będziemy mieć możliwość korekty zmian).

Kreator zaproponował (moim zdaniem) dobry podział na tabelę oraz odpowiednie relacje:

Zmieniamy nazwy tabel, klikamy dalej. Kreator pyta czy pogrubione pola unikatowo identyfikują każdy z rekordów, klikamy znowu dalej, pojawiło się nam okno do poprawy błędów pisowni kreatora:

Klikamy dalej, potwierdzamy, że nie dokonujemy zmian. W następnym oknie wybieramy "Nie, nie twórz kwerendy" i klikamy dalej:

Po kliknięciu zakończ mamy już gotową bazę danych. Jeżeli nie potrzebujemy wydajnego systemu, to możemy zakończyć na tym etapie, w przeciwnym razie trzeba przenieść bazę do dużego systemu bazodanowego - w tym przykładzie MS SQL 2008 R2 Express. Zamykamy wszystkie tabele, wchodzimy w zakładkę (cały czas w Accessie) Narzędzia bazy danych oraz klikamy SQL Server.

Wybieramy "Utwórz nową bazę danych" i klikamy dalej (proces może troszkę potrwać). Następnie pojawi się okno w którym wybieramy serwer bazodanowy:

Klikamy dalej i wybieramy jakie tabele chcemy przenieść:

Po kliknięciu dalej, wybieramy co ma być wyeksportowane (w naszym przypadku wystarczą relacje - użwyamy DRI)

Klikamy dwa razy dalej oraz zakończ - i...praca wykonana:) Na zakończenie dostajemy raporcik wykonania przeniesienia.

W następnej części przedstawie sposób bez użycia Accessa.