Strona używa cookies (ciasteczek). Dowiedz się więcej o celu ich używania i zmianach ustawień. Korzystając ze strony wyrażasz zgodę na używanie cookies, zgodnie z aktualnymi ustawieniami przeglądarki.    X

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

Wstęp

Należę do osób zajmujących się komputerami zawodowo. Najróżniejszych zleceń jestem w stanie się podjąć (nawet jeśli ja nie potrafię, to wuj Google pomoże). Dzięki temu można zarobić, a oprócz tego rozwinąć swoje umiejętności. Tyle tytułem wstępu.

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.
To jest dla nas informacja, że niektóre nazwy są niezgodne z Accessem. Klikamy OK i Dalej. W następnym oknie zostawiamy zaznaczoną opcję
Pierwszy wiersz zawiera nagłówki kolumn
. W następnym oknie sprawdzamy i zatwierdzamy nazwy pól i typy danych (w tym przykładzie pierwszego pola nie importujemy).
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. 

porady

Komentarze

0 nowych
kubut   17 #1 12.03.2012 09:33

A czy wiesz może jak wygląda współpraca Accessa z SQL Server? I Obsługa tychże baz za pośrednictwem PHP? Bo niedługo będę musiał tworzyć system obsługi baz danych, którego założeniem ma być dostęp do nich z poziomu Accessa i witryny internetowej równolegle...

parasite85   7 #2 12.03.2012 14:04

@kubut:
Czyli chcesz mieć dostęp do bazy danych w MS SQL z poziomu Accessa?? Z witryny rozumiem - ale po co mieszać do tego Accesa - nie lepiej napisać aplikację w środowisku .net?

kamil_w   10 #3 12.03.2012 14:47

@parasite85
Moim zdaniem również lepiej napisać program kliencki.

StawikPiast   10 #4 12.03.2012 15:24

dane z excela mozna pobrac uzywajac management studio i narzedzia import and export data
Klikamy prawym klawiszem na bazie, wybieramy tasks i import danych.
Tam wskazujemy plik excela i gotowe.

kubut   17 #5 12.03.2012 16:24

Niestety nie ja tu decyduję, klient do tej pory używał Accesa i nie ma zamiaru tego zmienić (wymagałoby to licznych szkoleń itp...)

parasite85   7 #6 12.03.2012 17:07

Nie do końca się z Tobą zgodzę. Używał Accessa, ponieważ ktoś przygotował odpowiednie formatki właśnie w tym programie, a co stoi na przeszkodzie, żebyś interfejs programu zrobił taki sam? Poza tym Access ma możliwość stworzenie tzw. tabeli połączonej - w samym Accessie będzie wyłącznie łącze do tabeli źródłowej. O coś takiego Ci chodzi?

parasite85   7 #7 12.03.2012 18:42

@StawkikPiast:
Po części masz rację, ale nie rozwiązujesz w ten sposób problemu redundancji. Polecam mój kolejny (przed chwilą opublikowany wpis) w którym również krok po kroku rozwiązuje problem: http://www.dobreprogramy.pl/parasite85/Niedozwolone-zwiazki-Excela-i-bazy-danych...

molexor   6 #8 12.03.2012 19:53

z php bez większego problemu obslużysz mssqla oczywiście. z accesa możesz wysylać/wysylać pobierać dane również a pomocą VBA - można dopisać parę makr. Aczkolwiek dziwne połączenie.

kubut   17 #9 12.03.2012 20:16

@parasite85 - Bez owijania w bawełnę, na przeszkodzie stoi kasa ;) Początkowo w planach było całkiem wyparcie Accessa z firmy, jednak szefostwo uparło się przy nim ;)

parasite85   7 #10 12.03.2012 20:47

@kubut:
To musisz sprawdzić, tą opcję o której Ci mówiłem. Testowy Access też jest.

kubut   17 #11 12.03.2012 20:51

Ok, dzięki, spróbuję się tym pobawić na dniach

acabose   2 #12 18.03.2012 23:04

@kubut ..... wtrącę się pomimo że nie jestem żadnym ekspertem i teraz nie pracuję "głębiej" z produktami MS ..... Access ma możliwość tworzenia formatek (nie wiem czy poprawnie nazywam) do wprowadzania danych, przy tej okazji jak się poklika można "poprosić" MS-A o utworzenie czegoś takiego jako strony internetowej .... do pracy w wewnętrznej sieci firmowej wystarcza ..... oczywiście dobrze jest mieć całą infrastrukturę (z domeną) wg MS i na produktach MS .... pokazany wyżej sposób eksportu działa i w drugą stronę - nie za bardzo rozbudowane bazy, które jest w stanie otworzyć MS-A można obsługiwać za pośrednictwem właśnie stworzonych w Accesie formatek (kiedyś wyeksportowałem do formatu chyba DBase III czy IV i obsługiwałem przez taki formularz w sieci wewnętrznej). Czy to się nadaje do szerszego zastosowania to nie wiem - trzeba by pogrzebać jak wygląda kod takiej strony www wygenerowanej przez MS-A (pod kątem "poprawnego" działania w IE), ale php na pewno da się coś nasztukować. Niestety dokładniej nie mogę opisać bo kiedyś raz eksperymentowałem i później nie było mi już potrzebne (Access 2002/XP). Takie rozwiązanie sprawia że nawet nie znając programowania można stworzyć to o czym piszesz, jednak jeśli potrzebna jest weryfikacja kto ma mieć dostęp lub parę innych funkcji (specyficzna obróbka danych przed wprowadzeniem do bazy) których w takich formularzach MS nie przewidział opisane rozwiązanie może stać się problematyczne. Nie wiem czy to co wypisuję ma sens ale oceńcie sami, jeśli nie trafiłem to przepraszam za spam i wygłupy.

p.s. jeśli opisanym sposobem w 15-20 min. nie rozwiążesz swojego problemu to poszukaj innego rozwiązania.