Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.
Die Tage war es soweit: Schwiegervater, seines Zeichens zweiter Vorsitzender eines Sportvereins, druckste herum: „Die Mitgliederdatei, also … die Excel-Datei – kannst Du uns da nicht mal was Richtiges draus zaubern Also ein Programm, mit dem ich Mitglieder eingeben und auch mal ein paar Auswertungen machen kann“ Na klar kann ich. Also hatte ich ein neues Projekt: Eine Excel-Tabelle mit allen relevanten Daten sollte in einer relationalen Datenbank landen, die nicht nur die Dateneingabe vereinfachte, sondern auch noch verschiedene andere Aufgaben übernehmen sollte. Ein perfektes Beispiel für eine Lösung in Access im Unternehmen! Dieser Teil beschreibt, wie wir die Daten aus der Excel-Tabelle in ein frisch erstelltes Datenmodell überführen.
Die Herausforderung bringt so Einiges mit sich, was man beim überführen einer Excel-Tabelle in ein relationales Datenmodell an Herausforderungen erwarten kann. Alle Daten sind in einer einzigen Tabelle gespeichert, Lookup-Daten etwa zum Auswählen von Anreden, Altersklassen et cetera gibt es nicht, verschiedene Felder wie etwa der Nachname werden zum Hinzufügen zusätzlicher Informationen verwendet. Außerdem gibt es noch verschiedene farbige Markierungen, die einen bestimmten Status des Mitglieds belegen.
Verknüpfung erstellen
Als Erstes wollen wir uns die Daten der Excel-Datei in Form einer verknüpften Access-Tabelle für unsere gewohnten Werkzeuge verfügbar machen. Das ist schon mal nicht allzu einfach, da der bisherige Verwalter dieser Daten ja nicht nur Werte in die Spalten eingetragen hat, sondern auch noch Informationen in Form unterschiedlicher Formatierungen, in diesem Fall etwa farbiger Hintergründe oder fetter Schrift hinterlegt hat. Außerdem beginnen die Zeilen mit den Daten auch nicht gleich in der ersten oder zweiten Zeile, sondern im Kopf befindet sich zunächst noch eine überschrift, die normalweise natürlich im Kopfbereich der Seite landen sollte – genauso wie die Legende im unteren Bereich, die im Fußbereich erscheinen sollte.
Die Lösung für die überflüssigen Zeilen über und unter den relevanten Daten ist relativ einfach: Wir legen dazu einfach einen Bereich in der Excel-Tabelle fest, den wir dann beim Verknüpfen oder Importieren auswählen können. Wie geht das Dazu markieren Sie den gewünschten Bereich und klicken dann in das Namenfeld links oben über der Tabelle und geben dort den Namen für den gewählten Bereich ein – in diesem Fall wollen wir den Bereich Mitglieder nennen (s. Bild 1).
Bild 1: Benennen eines Bereiches
Wenn Sie dann im Ribbon von Excel den Befehl Formeln|Definierte Namen|Namensmanager wählen, erscheint der Dialog aus Bild 2, mit dem Sie die bereits festgelegten Bereiche bearbeiten können. Hier können Sie auch eventuell falsch markierte Bereiche löschen oder bearbeiten.
Bild 2: Verwalten der benannten Bereiche
Farbige Markierungen in Daten umwandeln
Damit haben wir die Voraussetzung geschaffen, dass wir die Daten gleich mit Access verknüpfen können. Nun ist allerdings noch ein weiteres Problem vorhanden: Wie können wir die farbigen Markierungen so darstellen, dass diese auch nach dem Verknüpfen von Access aus gelesen werden können
Im Detail sieht es es aus, dass wir in der ersten Spalte verschiedene farbige Markierungen vorfinden, die nicht etwa aus einer bedingten Formatierung stammen, sondern die vom Benutzer manuell erstellt wurden. In Access können wir diese Markierungen aber nach dem Importieren oder Verknüpfen der Excel-Tabelle nicht mehr erkennen, diese werden ignoriert. Also müssen wir die Markierungen irgendwie in einen Zahlencode oder eine andere Information übertragen.
Wer zu faul ist, kann natürlich einfach dem Auftraggeber sagen, dass er eine neue Spalte anlegen und die Informationen aus den farbigen Markierungen dort eintragen soll. Aber wir sind ja immer an kreativen Lösungen interessiert und nehmen dies daher selbst in die Hand. In diesem Fall wollen wir eine neue Spalte namens Farbcode anlegen und für die Zellen dieser Spalte eine Formel hinterlegen, welche die Hintergrundfarbe der ersten Spalte ausliest und einen entsprechenden Zahlenwert in die Felder der neuen Spalte einträgt.
Um die Hintergrundfarbe in Form eines Zahlencodes in einer neuen Spalte rechts von den eigentlichen Inhalten anzuzeigen, gehen Sie wie folgt vor:
- Wählen Sie den Ribbon-Eintrag Formeln|Definierte Namen|Namen definieren. Dies öffnet den Dialog aus Bild 3.
- Hier geben Sie in das Feld Name den Wert Hintergrundfarbe ein.
- In das Feld Bezieht sich auf geben Sie den folgenden Ausdruck ein: =ZELLE.ZUORDNEN(63;INDIREKT(„ZS(-15)“;))
- Nun schließen Sie den Dialog Name bearbeiten.
- Geben sie in die erste freie Spalte des Excel-Tabellenblatts die folgende Formel ein: =Hintergrundfarbe
Bild 3: Name für eine Formel auf Basis einer Zelle
Nun liefert die hintere Spalte die den Hintergrundfarben entsprechenden Farbwerte (s. Bild 4). Damit können wir beim Import von Access aus doch schon etwas anfangen!
Bild 4: Hinzufügen des Namens mit der Referenz
Was aber haben wir hier gemacht – und wie funktioniert die angegebene Formel genau Die Zuordnen-Funktion erwartet zwei Parameter: der erste erhält einen Zahlencode, der die Eigenschaft repräsentiert, die wir für die im zweiten Parameter angegebenen Bereich ermitteln wollen. In diesem Fall liefert der Zahlencode 63 einen Wert für die Eigenschaft Hintergrundfarbe. Mit dem zweiten Ausdruck geben wir die zu untersuchende Zelle an. In diesem Fall nutzen wir die indirekte Schreibweise: INDIREKT(„ZS(-15)“). Hinter Z befindet sich keine Zahl, also wollen wir in der gleichen Zeile arbeiten, hinter S befindet sich der Wert -15, was bedeutet, dass wir uns auf die Zeile beziehen, die 15 Spalten weiter links liegt.
Dadurch dass wir die Formel =Hintergrundfarbe in die Zeile R schreiben, erhalten wir die Hintergrundfarbe für die Zelle der gleichen Zeile in der Spalte B. Nun wollen wir auch noch zwei weitere Formatierungen in den folgenden beiden Spalten unterbringen, nämlich die Schriftart in der Spalte B und ob der Text in Spalte B fett formatiert ist.
Dazu legen wir zwei neue Einträge im Dialog Namens-Manager hinzu, sodass diese anschließend wie in Bild 5 aussieht. Die Formel für den Namen Schriftfarbe sieht so aus – hier verwenden wir also den Code 24:
Bild 5: Anlegen zweier weiterer Benennungen
=ZELLE.ZUORDNEN(24;INDIREKT("ZS(-17)";))
Die Formel für den Namen Fett gestalten wir mit dem Code 20 wie folgt:
=ZELLE.ZUORDNEN(20;INDIREKT("ZS(-18)";))
Damit haben wir die drei möglichen Informationen, die durch die verschiedenen Formatierungen entstehen, und können diese auch beim Import in die Access-Datenbank berücksichtigen.
Den benannten Bereich für den Import müssen Sie übrigens nicht mehr um die drei neu angelegten Spalten erweitern, da wir dort die kompletten Zeilen als Bereich angegeben haben.
Import/Verknüpfung der Daten
Nun wollen wir die Daten aus der Excel-Tabelle in Access verfügbar machen. Dazu haben wir zwei Möglichkeiten: Entweder wir importieren die Daten oder wir erstellen eine Verknüpfung. In diesem Fall, wo nur eine einmalige Migration der Daten geplant ist, können wir die Daten direkt importieren. Das erledigen wir wie folgt:
- öffnen Sie eine neue Access-Datenbank.
- Wählen Sie den Ribbon-Befehl Externe Daten|Importieren und Verknüpfen|Neue Datenquelle|Aus Datei|Excel aus (s. Bild 6).
- Geben Sie im folgenden Dialog die Quelldatei an, behalten Sie die Einstellung Importieren … bei und klicken Sie auf OK (s. Bild 7).
- Wechseln Sie im folgenden Dialog zur Option Benannte Bereiche anzeigen. Dies liefert unseren weiter oben angelegten Bereich Mitglieder, den wir nun aktivieren. Die Liste darunter zeigt nun den von uns ausgewählten Bereich an (s. Bild 8).
- Aktivieren Sie im folgenden Schritt die Option Erste Zeile enthält Spaltenüberschriften. Dies bringt zunächst die Meldung aus Bild 9 zutage. Dabei handelt es sich um die von uns hinzugefügten Spalten ganz rechts. Die fehlenden Spaltenüberschriften sind kein Problem, so lange wir die Daten nur einmal importieren müssen – Sie können sich also aussuchen, ob Sie diese in der Excel-Tabelle nachtragen und den Import erneut starten oder ob Sie die Feldnamen in Access anpassen.
- Einen Schritt weiter müssen Sie nun festlegen, welche Felder importiert werden sollen und können ein paar Eigenschaften für diese Felder festlegen. Dazu gehören der Feldname, der Datentyp und die Indizierung. Wir behalten alle Werte bei, da wir die Daten aus der Importtabelle ohnehin noch aus weitere Tabellen aufteilen müssen (s. Bild 10).
- Im vorletzten Schritt (s. Bild 11) geben wir noch an, dass wir keinen neuen Primärschlüssel anlegen wollen – dies aus dem gleichen Grund wie zuvor: Die Daten werden ohnehin noch in die Zieltabellen überführt.
- Im letzten Schritt können Sie noch den Namen der Zieltabelle festlegen, wobei wir den Namen Mitglieder beibehalten können.
Bild 6: Starten des Import-Vorgangs
Bild 7: Auswahl von Quelldatei und Import
Bild 8: Festlegen des zu importierenden Bereichs
Bild 9: Es fehlen einige Spaltenüberschriften
Bild 10: Einstellen der Feldeigenschaften
Bild 11: Festlegen eines Primärschlüssels
Im vorliegenden Beispiel traten beim Import ein paar Fehler auf, die der Import-Assistent in der Tabelle Mitglieder_Importfehler gespeichert hat. Die Fehler traten wohl in der Spalte mit der Postleitzahl auf, die entsprechenden Zeilennummern sind ebenfalls in der Tabelle angegeben (s. Bild 12). Ein Blick in die Zieltabelle Mitglieder zeigt, dass das Feld Postlz für die angegebenen Zeilen leer ist. Also werfen wir auch noch einen Blick auf die Daten in der Ausgangsdatei.
Bild 12: Tabelle der Importfehler
Hier zeigt sich dann ein gern gemacht er Fehler: Statt das Land in einer eigenen Spalte zu speichern, wird dieses als Kürzel vor die Postleitzahl geschrieben. In diesem Fall handelt es sich um die Schweiz und Einträge wie etwa CH-8005. Beim Import werden die Datentypen der Zielfelder auf Basis der oberen paar Werte ermittelt. Hier traten noch keine Postleitzahlen mit anderen Zeichen als mit Zahlen auf, daher wurde ein Zahlendatentyp für das Feld festgelegt. In ein solches Feld können wir natürlich keine Werte mit Buchstaben importieren. Auch hier haben Sie die Wahl: ändern Sie die paar Einträge in der Zieltabelle von Hand oder wollen Sie dies in der Quelldatei erledigen und die Daten erneut importieren Wir starten den Import erneut und stellen diesmal den Datentyp für die Spalte PLZ auf Kurzer Text ein – nun gelingt auch der Import der PLZ-Werte mit Länderkennzeichen.
Damit wäre der Import abgeschlossen und wir können uns dem Datenmodell und dem übertragen der Daten aus der Tabelle Mitglieder in die noch zu erstellenden Tabellen der Anwendung konzentrieren. Wir können noch die meisten der in der Zieltabelle Mitglieder angelegten Felder mit den Bezeichnungen F21, F22 und so weiter löschen sowie den drei von uns angelegten Feldern entsprechende Feldnamen zuweisen. Der Entwurf des Datenmodells sieht anschließend wie in Bild 13 aus.
Bild 13: Datenmodell der Tabelle nach dem Umbenennen und Entfernen unnötiger Felder
Erstellen der Zieltabellen
Nun erstellen wir die Zieltabellen für die Daten aus der temporären Tabelle Mitglieder. Als Erstes legen wir die Tabelle tblMitglieder an, welche die Basisdaten eines jeden Mitglieds enthält – also Vorname, Nachname, Mitgliedsnummer und so weiter. Des Weiteren werden wir einige Lookup-Tabellen brauchen – zum Beispiel für die Anrede. Wobei an dieser Stelle auffällt, dass die Excel-Tabelle gar keine Spalte für die Anrede enthält. Eine Spalte für das Geschlecht ist auch nicht vorhanden. Wie wurden denn dann die Serienbriefe mit der korrekten Anrede gefüllt Vielleicht hat man diese einfach weggelassen. Wir werden auf jeden Fall eine Anrede unterbringen. Dazu benötigen wir die Lookup-Tabelle tblAnreden, deren Entwurf wie hier nicht extra vorstellen wollen – sie enthält lediglich die Felder AnredeID, Anrede, Briefanrede und Adressanrede (also für Werte wie Herr, Sehr geehrter Herr und Herrn).
Der Haken an der Tabelle mit den Anreden ist, dass das entsprechende Fremdschlüsselfeld in der Tabelle tblMitglieder manuell gefüllt werden muss, da die Excel-Tabelle ja noch gar keine Informationen bezüglich Anrede oder Geschlecht enthält. Das einzige Feld, dem man eine Information über das Geschlecht entnehmen kann, ist Vorname. Allerdings gibt es auch keinen Algorithmus, der zuverlässig das Geschlecht vom Vornamen ableitet. Wenn wir aber in der Excel-Tabelle etwas weiter nach rechts schauen, findet sich dort eine Spalte mit der überschrift AK/Pa. Was Pa ist, ist aktuell nicht bekannt, aber AK heißt offensichtlich Altersklasse. Dort finden sich dann Werte wie EPM, EPW, WJ oder EAM. Die Gemeinsamkeit ist: Alle Einträge enthalten entweder ein M für Männlich oder ein W für Weiblich. Bingo!
Für das Geschlecht legen wir wie für die Anrede eine eigene Lookup-Tabelle namens tblGeschlechter an. Diese enthält die beiden Felder GeschlechtID und Geschlecht (s. Bild 14).
Bild 14: Die Tabelle tblGeschlechter
Altersklassen
Apropos Altersklassen. Normalerweise ist sehr eindeutig festgelegt, mit welchem Alter man in welcher Altersklasse unterwegs ist. Allerdings gibt es immer Vereine, die in einer Altersklasse zu wenige Spieler haben. Bevor man dann in dieser Altersklasse gar keine Mannschaft meldet, stockt man diese gegebenenfalls mit jüngeren Spielern auf, was in den meisten Sportarten bis zu einem gewissen Alter möglich ist, oder man spielt außer Konkurrenz und nimmt ein paar ältere Spieler hinzu. Was hat das mit dem Datenmodell zu tun Nun: Normalerweise wird jedes Mitglied einer Altersklasse zugeordnet. Das kann man auch automatisiert machen, indem man das Geburtsdatum des Mitglieds ermittelt, den Stichtag hinzunimmt, an dem das Alter für die aktuelle Saison ermittelt wird und dann aus einer entsprechenden Tabelle die Altersklasse ausliest. Aber was, wenn wir oben Mitglieder in mehr als einer Altersklasse geführt werden sollen, weil eben in einer Mannschaft zu wenige Mitglieder sind und diese aufgestockt werden muss An dieser Stelle kommen wir an einer Verfeinerung der Begriffe nicht vorbei: Ein Spieler kann natürlich immer nur einer Altersklasse angehören. Allerdings können durchaus Spieler verschiedener Altersklassen einer Mannschaft zugeordnet werden.
Wir behalten also die Idee bei, die Altersklasse über das Geschlecht und die Tabelle mit den Altersklassen zu ermitteln. Zusätzlich legen wir eine Tabelle an, die alle für die aktuelle Saison gemeldeten Mannschaften enthält. Eine Tabelle zum Herstellen einer m:n-Beziehung erlaubt dann das Zuordnen eines Spielers zu einer oder mehreren Mannschaften.
Tabelle für die Altersklassen
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
Testzugang
eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel
diesen und alle anderen Artikel mit dem Jahresabo