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

W poprzednim wpisie, pokazałem w jaki sposób przenieść tabelkę w Excel... 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 Database2SQL

Nastę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