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
Sie haben das Ende des frei verfügbaren Textes erreicht. Möchten Sie ...
Oder bist Du bereits Abonnent? Dann logge Dich gleich hier ein. Die Zugangsdaten findest Du entweder in der aktuellen Print-Ausgabe auf Seite U2 oder beim Online-Abo in der E-Mail, die Du als Abonnent regelmäßig erhältst:
