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ęść 2

W poprzednim wpisie, pokazałem w jaki sposób przenieść tabelkę w ... w tym wpisie chciałem pokazać inną metodę, do której Access nie będzie nam potrzebny.

Do dzieła...:)

Tym razem również użyjemy takiej samej tabelki przykładowej:Lista przykładowa

Jeśli chcemy/musimy pominąć udział Accessa, najpierw musimy zamienić jedną tabelkę na 4 (w tym przykładzie, jak pamiętamy była Lista, oraz 3 tabele słownikowe - Stanowiska, Działy, Sekcje).
Najpierw zaznaczamy całą kolumnę Stanowiska, kopiujemy w wolnym miejscu obok, sortujemy od A do Z, następnie powtarzające się stanowiska zaznaczamy i klikamy prawym, usuń i przesuń komórki do góry. Dodajemy u góry wiersz nagłówkowy (id_stanowiska i stanowiska). Analogicznie robimy z działami i sekcjami. Wyglądać to powinno mniej więcej tak:

Teraz dodajemy obok stanowisk, działów i sekcji dodatkowe kolumny oraz nazywamy je odpowiedni id_stanowiska, id_działu, id_sekcji
Żeby utworzyć "relacje" w tabeli Lista, posłużymy się formułą Excela WYSZUKAJ.PIONOWO. Tak powinny wyglądać formuły w poszczególnych kolumnach:WYSZUKAJ.PIONOWO(E4;$O$4:$P$15;2) WYSZUKAJ.PIONOWO(G4;$O$18:$P$23;2) WYSZUKAJ.PIONOWO(I4;$O$26:$P$28;2) Po skopiowaniu formuł w całej tabelce będzie ona miała mniej więcej taką postać:
Musimy jeszcze zrobić jedną ważną rzecz: nazwać tabelki: zaznaczamy jakąkolwiek komórkę w tabeli, naciskamy CTRL+A i w lewym górnym rogu definiujemy nazwę tego: ja nazwałem to Lista, i tak z każdą tabelką trzeba zrobić. Zapisujemy taki arkusz i zamykamy go.
Otwieramy Microsoft SQL Server Management Studio, klikamy "New Query" i tworzymy nową bazę danych:CREATE DATABASE Database2SQLNastępnie klikamy prawym przyciskiem na naszą bazę w drzewku po lewej, task i import data:
Otworzy nam się takie okno:
Klikamy next, jako źródło wybieramy Microsoft Excel, zaznaczamy wersję Excela, zostawiamy zaznaczenie "First row has column names" (pierwszy wiersz zawiera nazwy kolumn) i klikamy Next
W następnym oknie nic nie musiałem zmieniać - stosuję logowanie za pomocą danych systemu, odpowiednia baza danych została zaznaczona - klikamy Next
Zostawiamy pierwszą opcję - kopiowanie danych z istniejących tabel lub widoków.
W kolejnym okienku wybieramy tabele które mają być importowane, możemy posprawdzać (ewentualnie pozmieniać rodzaje pól). Wprowadziłem następujące zmiany:

W tym momencie się troszkę zatrzymałem. Chciałem importować całość, ale w taki sposób, aby od razu na poziomie importu utworzyło odpowiednie relacje. Da się, ale trzeba to zrobić na raty. Najpierw importujemy tabele słownikowe, modyfikując wcześniej kod: zamieniłem kolumny kolejnością (nie jest to konieczne, ale lubię porządek:P), po drugie ustawiłem klucz główny. Robimy to wchodząc na tabelę którą importujemy i klikamy "Edit Mappings".
następnie klikamy w "Edit SQL" i tam wklejamy odpowiedni kod. Czynność powtarzamy z każdą tabelą.
CREATE TABLE [dbo].[Działy] ( [id_działu] int not null primary key, [Dział] nvarchar(255) ) CREATE TABLE [dbo].[Sekcje] ( [id_sekcji] int not null primary key, [Sekcja] nvarchar(255) ) CREATE TABLE [dbo].[Stanowiska] ( [id_stanowiska] int not null primary key, [Stanowisko] nvarchar(255) ) Klikamy Next, w następnym oknie zostawiamy zaznaczoną opcję "Run immediately" - znowu Next, teraz widzimy podsumowanie naszego importu.
Klikamy Finish, SQL trochę popracuje i wyświetli nam ładny raporcik:
Teraz powtarzamy czynność importu, wybierając tabelę lista. Modyfikujemy kod SQL, aby ustawić klucz główny i klucze obce. CREATE TABLE [dbo].[Lista] ( [Nr_Id] int, [Nazwisko] nvarchar(255), [Imię] nvarchar(255), [Stanowisko] nvarchar(255), [id_stanowiska] int foreign key references Stanowiska(id_stanowiska), [Dział] nvarchar(255), [id_działu] int foreign key references Działy(id_działu), [Sekcja] nvarchar(255), [id_sekcji] int foreign key references Sekcje(id_sekcji), [Pensja] float, [Pracuje_od] datetime, [Data_ur] datetime ) Jeszcze tylko usuwamy zbędne kolumny (te które przenieśliśmy do tabeli słownikowych) - klikamy prawym przyciskiem myszy na tabelę Lista i wybieramy "Design", klikamy na wybranej tabeli i "Delete column"
Uzyskujemy bazę danych w III postaci normalnej, bez przepisywania danych (co oprócz oszczędności czasu, pozwala uniknąć pomyłek które zdarzają się przy żmudnym przepisywaniu
 

porady

Komentarze

0 nowych
ttomas   10 #1 14.03.2012 08:43

Rzuciła mi się w oczy funkcja WYSZUKAJ.PIONOWO, wychodziły cuda jeśli np komórki miały ustawiony różny typ danych, a części tabeli były przeklejane z np OpenOffice i do tego w excelu tak szybko nie da się faktycznie zmienić typu komórki przy zaznaczeniu całego zakresu, będzie zaznaczony ale w tej funkcji nie zadziała, prze edytowanie każdej komórki zadziała (F2+Enter) aby było szybciej....

Skrypt VBA który porówna dane wydał się rozwiązaniem tego problemu, jednak nie wierzyłem oczom kiedy dwa identyczne wpisy w komórkach pobrane do zmiennych nie był równe.... Sztuczka z dodaniem pojedynczego apostrofu na początku każdej komórki przekazywanej do zmiennej rozwiązała sprawę.

Tak tylko zwracam uwagę na to porównanie danych w excelu, można się oszukać, zwłaszcza przy dużej ilości wierszy.

parasite85   7 #2 14.03.2012 09:33

Dlaczego nie da się? Zaznaczasz cały obszar i zmieniasz typ danych

ttomas   10 #3 14.03.2012 21:12

@parasite85, zgadza się ale dalej nie działa to w tej funkcji wyszukaj.pionowo dopóki nie przeedytujesz komórki, piszę o kolumnach gdzie czasami jest tekst, liczba, data, itp co kilka wierszy inne dane i to idzie do porównania, chociaż w teorii jest ustawiony właściwy format i tak jest w właściwościach to wyżej wspomniana funkcja 'wyszukaj' wstawiona komórce obok nie zwraca wyniku do momentu edycji,
myślę excel interpretuje to po swojemu, jest wiele możliwości np. awk ale danie tego userowi co ma co chwilę do porównania dużo różnych danych nie wchodzi w grę, a sztuczka z apostrofem w vba sprawdza się idealnie przy excelu z makrem i się nie oszuka na czasami gigantyczne liczby wpisów.