 | Unser Angebot für Sie! Lesen Sie diesen Beitrag und 500 andere sofort im Onlinearchiv, und erhalten Sie alle zwei Monate brandheißes Access-Know-how auf 72 gedruckten Seiten! Plus attraktive Präsente, zum Beispiel das bald erscheinende Buch 'Access 2010 - Das Grundlagenbuch für Entwickler'! |
| | | | | |
Zusammenfassung
Erfahren Sie, wie Sie Daten aus Text- oder ähnlichen Dateien in eine Access-Datenbank importieren.
Techniken
VBA, Tabellen
Voraussetzungen
Access 2000 und höher
Beispieldateien
TextdateienImportieren.mdb
Shortlink
www.access-im-unternehmen.de/707
Importieren von Textdateien
André Minhorst, Duisburg
Gelegentlich importiert oder verknüpft man eine Textdatei, eine Excel-Tabelle oder auch eine Tabelle einer anderen Datenbank, um sich der darin enthaltenen Daten zu bemächtigen. Je nachdem, welche Quelle zum Einsatz kommt, wie die Daten beschaffen sind und ob die Daten nur einmal oder regelmäßig importiert werden sollen, wählt man unterschiedliche Strategien. Wir stellen einige davon vor und liefern Tipps und Tricks rund um den Import von Daten.
Beschaffenheit und Quelle der Daten
Daten können sehr unterschiedlich aussehen: So liefern Tabellen aus Textdateien oder von Excel meist eher nicht normierte Daten, während dies beim Import von Daten aus bestehenden Datenbanken schon eher der Fall ist. Auch XML-Dokumente kommen immer öfter für den Datenaustausch zum Einsatz, wobei die enthaltenen Daten nicht unbedingt besser strukturiert sein müssen als in anderen Datenformaten.
Die Struktur der Daten fremder Datenquellen ist aber fast nie so gestaltet, dass Sie diese direkt in die Zieltabellen übernehmen können.
Ein direkter Import ist daher nur dann möglich, wenn Sie selbst Daten aus eigenen Datenbanken konsolidieren möchten - beispielsweise, wenn Außendienstler mit den Daten ihrer Kunden unterwegs sind und zu bestimmten Zeiten, also beispielsweise kurz vor Feierabend oder dem Wochenenhde, ihre Daten zur Zentrale schicken, wo diese der zentralen Datenbank einverleibt werden sollen.
Wenn Sie Daten wie Adressen aus Quellen wie Exceltabellen oder Textdateien erhalten, sind diese meist nicht normiert, was bedeutet, dass diese beispielsweise redundante Daten enthalten (für die weitere Beschreibung gehen wir davon aus, dass wir es mit einer Excel- oder Textdatei zu tun haben, die aus einer oder mehreren Zeilen besteht und einzelne Felder durch Trennzeichen wie das Semikolon oder Tabs voneinander getrennt sind).
Das beste Beispiel hierfür sind die Anreden in einer Adressentabelle: Die zu importierenden Tabellen enthalten dafür meist ein eigenes Feld mit Werten wie Herr, Herrn oder Frau. Unter Access würde man eine eigene Tabelle anlegen, die alle Anreden enthält, wobei jeder Anrede ein eindeutiger Schlüssel zugeordnet ist (beispielsweise 1 für Herr, 2 für Frau).
In die eigentliche Adressentabelle braucht man dann im einfachsten Fall statt der Anrede nur noch den der Anrede entsprechenden Wert einzutragen. Wenn die Daten dann in Formularen oder Berichten bearbeitet oder angezeigt werden sollen, sorgen eine Verknüpfung zwischen den beiden Tabellen und eine entsprechende Abfrage dafür, dass die Adresse wieder mit dem in der Anredentabelle enthaltenen Ausdruck verknüpft wird.
Lange Rede, kurzer Sinn: Die Daten aus der Quelltabelle müssen beim Import so auf die Adressen- und die Anreden-Tabelle aufgeteilt werden, dass anschließend jede Adresse über die Verknüpfung zwischen Adresse und Anrede wieder die richtige Anrede erhält.
Importhäufigkeit
Wichtig für den Import ist auch der Zeitpunkt: Soll dieser nur einmal geschehen, um Daten aus verschiedenen Datenquellen zu konsolidieren? Oder laufen regelmäßig Daten aus verschiedenen Quellen auf, die einer Hauptdatenbank zugeführt werden sollen?
Bei einem einmaligen Import weniger Daten wird man sich wesentlich weniger Mühe geben, einen möglichst vollautomatischen Import zu kreieren. In diesem Fall legt man vielleicht sogar hier und da selbst Hand an, um Daten geradezubiegen.
Ein Beispiel hierfür sind Adressen aus Excel-Tabellen, deren Felder zwar theoretisch einem bestimmten Datentyp entsprechen sollten (etwa Datum beim Geburtstag), aber tatsächlich nicht nur ein Datum, sondern auch noch weitere Informationen enthalten (zum Beispiel 23.1.1971 - wichtig!).
Wenn der Import jedoch regelmäßig geschieht, steckt man wahrscheinlich viel mehr Zeit und Mühe in einen sauberen und automatischen Import, bei dem man am besten nur noch eine Schaltfläche betätigen muss und die Daten dann auf einen Rutsch in die Zieltabellen gelangen.
Der Import-Assistent von Access
In einigen Fällen leistet der Import-Assistent von Access wertvolle Dienste - man sollte ihn einfach ausprobieren, bevor man sich an die Programmierung eines Imports macht. Außerdem lassen sich die Parameter eines Imports auch speichern, um diesen später erneut auszuführen.
Die Import-Assistenten für verschiedene Importformate rufen Sie unter Access 2007 über das Ribbon-Tab Externe Daten auf (s. Abb. 1). Bei älteren Access-Versionen finden Sie diese Befehle unter Datei|Externe Daten|Importieren... Im dortigen Importieren-Dialog wählen Sie die Datenquelle aus, während dies unter Access 2007 bereits über die Auswahl der entsprechenden Ribbon-Schaltfläche geschieht.
Abb. 1: Auswahl des Import-Formats im Ribbon von Access 2007
Verknüpfen oder importieren?
Je nach Vorgehensweise importieren Sie die Daten nicht gleich, sondern machen diese zunächst in Form einer verknüpften Tabelle verfügbar. In diesem Fall wählen Sie unter Access 2003 und älter nicht den Menüeintrag Datei|Externe Daten|Importieren..., sondern Datei|Externe Daten|Tabellen verknüpfen... aus.
Unter Access 2007 haben Sie noch einen Schritt länger Zeit, sich zwischen einem Import oder einer Verknüpfung zu entscheiden: Hier legen Sie dies erst im folgenden Dialog fest.
Das Resultat beider Vorgänge sieht indes ähnlich aus: Beim Import entsteht eine Tabelle, die Sie wie die übrigen Tabellen über das Datenbankfenster beziehungsweise den Navigationsbereich öffnen können, beim Verknüpfen erscheint ebenfalls ein neuer Eintrag neben den übrigen Tabellen.
Dieser ist allerdings durch ein spezielles Symbol als Verknüpfung gekennzeichnet. Auch nach dem Öffnen einer importierten oder verknüpften Tabelle lassen sich kaum Unterschiede feststellen, denn Access zeigt die Daten unabhängig von der Quelle in der für Access-Tabellen üblichen Datenblattansicht an.
Verknüpfte Textdateien
Textdateien sind ein probates Mittel, um Inhalte zwischen Anwendungen zu transferieren. XML bietet zwar aufgrund seiner Strukturierung mehr Möglichkeiten, aber nicht alle Anwendungen erlauben den Export und Import von Daten in diesem Format.
Nehmen wir also an, Sie erhalten Daten im Textformat, wobei jede Zeile einen Datensatz enthält und die einzelnen Felder durch Trennzeichen wie ein Semikolon oder ein Tabulator-Zeichen voneinander getrennt sind.
Nehmen wir weiterhin an, dass die Daten regelmäßig neu geliefert werden - beispielsweise einmal pro Tag, und zwar nachts.
Damit Sie die Daten bequem über eine verknüpfte Tabelle anpacken können, brauchen Sie nur einmalig eine Verknüpfung zu erstellen und können die dahinter verborgene Textdatei beliebig austauschen - Access greift immer wieder auf die richtige Textdatei zu, wenn die Verknüpfung richtig definiert und die Datei an gleicher Stelle unter gleichem Namen zu finden ist.
Nachdem Sie dem Importassistenten mitgeteilt haben, dass Sie eine Verknüpfung erstellen möchten und auf welcher Textdatei diese basiert, zeigt dieser die enthaltenen Daten an (s. Abb. 2).
Abb. 2: Erstellen einer Verknüpfung mit einer Textdatei
Im folgenden Schritt legen Sie fest, ob die erste Zeile der Datei Spaltenüberschriften enthält und welches Trennzeichen die einzelnen Feldinhalte voneinander trennt (s. Abb. 3).
Abb. 3: Sind Spaltenüberschriften vorhanden? Welches Trennzeichen wird verwendet?
Schließlich legen Sie Details für jede einzelne Spalte fest: Welchen Feldnamen soll das Feld erhalten? Welchen Datentyp besitzen die darin gespeicherten Werte? Soll das Feld gegebenenfalls gar nicht in der verknüpften Tabelle erscheinen (s. Abb. 4)?
Abb. 4: Festlegen der Eigenschaften für die verknüpften Felder
In einem weiteren Dialog, den Sie über die Schaltfläche Weitere... öffnen, finden Sie alle eingestellten Parameter in einer Übersicht. Den Parametersatz für diesen Import können Sie mit der Schaltfläche Speichern unter... speichern und später wieder herstellen (s. Abb. 5).
Die Verknüpfung finden Sie anschließend zwischen den übrigen Tabellen der Datenbank im Datenbankfenster beziehungsweise im Navigationsbereich der Anwendung. Abb. 6 zeigt einige Beispiele für verknüpfte Tabellen, so zum Beispiel mit einer Access-Tabelle (tblBeispielAccess), einer Excel-Tabelle (tblBeispielExcel) und einer Textdatei (tblBeispielText) als Quelle.
Abb. 5: Übersicht der Parameter für eine Verknüpfung mit einer Textdatei
Abb. 6: Navigationsbereich mit einigen Verknüpfungen
Verknüpfung zu Textdatei erneuern
Möglicherweise wird die Quell-Textdatei einmal unter einem anderen Dateinamen geschickt oder sie landet in einem anderen Verzeichnis. Das Öffnen der Verknüpfung in Access führt dann zu einem Fehler, der besagt, dass Access das Objekt mit dem betreffenden Dateinamen nicht finden kann.
Da Sie typischerweise nicht direkt über den Eintrag im Datenbankfenster oder im Navigationsbereich auf die Daten zugreifen, sondern dies über ein Formular oder eine VBA-Routine geschieht, sollten Sie diesen Fehler abfangen und dem Benutzer die Gelegenheit bieten, die benötigte Datei erneut auszuwählen - am besten über einen Datei öffnen-Dialog.
Die Prüfung auf Funktionstüchtigkeit der Verknüpfung erledigt die folgende kleine Funktion:
Public Function CheckLinkedTable(strTable As _
String) As Boolean
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
On Error Resume Next
Set rst = db.OpenRecordset("SELECT * FROM " _
& strTable, dbOpenDynaset)
If Err.Number = 0 Then
CheckLinkedTable = True
Else
CheckLinkedTable = False
End If
rst.Close
Set rst = Nothing
Set db = Nothing
End Function
Wenn diese Funktion den Wert False zurückliefert, konnte die Verknüpfung nicht geöffnet werden. Das kann verschiedene Gründe haben: Es kann beispielsweise gar keine Verknüpfung mit dem als Parameter angegebenen Namen vorhanden sein, oder sie ist vorhanden, aber Access kann nicht auf die angegebene Datenquelle zugreifen. Da die Funktion CheckLinkedTable keine Fehlerursache zurückliefert, müssen Sie selbst sicherstellen, dass die Verknüpfung zumindest vorhanden ist - wenn auch ohne entsprechende Datenquelle.
Oberflächlich betrachtet sollte es reichen, wenn man irgendwo in den Eigenschaften der Verknüpfung den Pfad beziehungsweise den Dateinamen ändert, um diese auf eine andere Datei umzubiegen.
Schauen wir uns also an, wo diese Informationen liegen. Eine ganze Reihe finden wir gleich in der Systemtabelle MSysObjects, die Sie entweder im Datenbankfenster beziehungsweise Navigationsbereich finden, wenn die Anzeige von Systemobjekten aktiviert ist.
Falls nicht, behelfen Sie sich schnell mit der Eingabe des folgenden Befehls im Direktfenster:
DoCmd.OpenTable "MSysObjects"
Im Access-Fenster finden Sie nun diese Tabelle vor, die Informationen zu allen Access-Objekten enthält. Verknüpfungen erkennen Sie hier schnell daran, dass die ersten beiden Felder Connect und Database gefüllt sind (s. Abb. 7).
Abb. 7: Informationen zu verknüpften Datenquellen finden Sie in der Systemtabelle MSysObjects.
Für unsere Zwecke ist das erste Feld Connect besonders interessant. Es enthält die Verbindungszeichenfolge, die für unsere Textdatei so aussieht:
Text;DSN=_Beispiel Verknüpfungsspezifikation;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=1252;ACCDB=YES;
Im Feld Database ist der Pfad der Quelldatei gespeichert, und ForeignName enthält den Dateinamen, wobei der Punkt zwischen Name und Endung durch eine Raute (#) ersetzt wurde. Das Feld Name enthält den Namen der Verknüpfung im Navigationsbereich.
Also, warum ändern wir nicht einfach die entsprechenden Inhalte dieser Tabelle, wenn eine Quelldatei einen neuen Dateinamen erhält? Ganz einfach: Die Systemtabellen von Access sind sämtlich schreibgeschützt.
Wir kommen jedoch über das TableDef-Objekt an die Definition einer Verknüpfung heran. Den Inhalt der beiden Felder Connect und Database liefert beispielsweise die folgende Anweisung, wenn Sie diese im Direktfenster absetzen:
? CurrentDB.TableDefs("tblBeispielText").Connect
Text;DSN=_Beispiel Verknüpfungsspezifikation;
FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=1252;
ACCDB=YES;DATABASE=C:\Daten\Fachartikel\AccessImUnternehmen\2010\02\ImportierenUndVerknüpfen
Auch den Dateinamen können Sie abfragen, und zwar über die Eigenschaft SourceTableName:
? CurrentDb.TableDefs("tblBeispielText").
SourceTableName
Beispiel.txt
Leider lässt sich diese Eigenschaft nicht per VBA einstellen. Der Versuch, dies zu tun, ruft einen Fehler mit der Nummer 3268 und dem Meldungstext Die Eigenschaft kann nicht festgelegt werden, wenn das Objekt Teil einer Auflistung ist auf den Plan.
Genau genommen will Access uns damit sagen, dass die Eigenschaft für bereits bestehende Tabellen schreibgeschützt ist.
Wie also können wir den Dateinamen einer Quelldatei ändern? Ganz einfach: Wir erstellen einfach ein neues TableDef-Objekt, weisen diesem die Kombination aus vorhandenen und neuen Eigenschaften zu, löschen das Original-TableDef-Objekt und geben der neu erstellten Verknüpfung den Namen der ursprünglichen Verknüpfung. Diese Aufgabe erledigt die Prozedur aus Listing 1.
Public Function ReconnectLink(strTable As String) As Boolean
Dim db As DAO.Database
Dim tdfOld As DAO.TableDef
Dim tdfNew As DAO.TableDef
Dim strConnect() As String
Dim i As Integer
Dim strFilePathAndName As String
Dim strFilepath As String
Dim strFileExtension As String
Dim strFileName As String
MsgBox "Die Datenquelle der Tabelle '" & strTable & "' wurde nicht gefunden. Bitte wählen Sie
im folgenden Dialog die Quelldatei aus."
strFilePathAndName = OpenFileName(CurrentProject.Path, "Quelldatei auswählen",
"Quelldatei (*." & strFileExtension & ")|Alle Dateien (*.*))")
If Len(strFilePathAndName) > 0 Then
strFileName = Mid(strFilePathAndName, InStrRev(strFilePathAndName, "\") + 1)
strFilepath = Mid(strFilePathAndName, 1, InStrRev(strFilePathAndName, "\"))
Set db = CurrentDb
Set tdfOld = db.TableDefs(strTable)
strConnect = Split(tdfOld.Connect, ";")
For i = LBound(strConnect) To UBound(strConnect)
Select Case Split(strConnect(i), "=")(0)
Case "Database"
strConnect(i) = Split(strConnect(i), "=")(0) & "=" & strFilepath
End Select
Next i
Set tdfNew = db.CreateTableDef(strTable & "_TEMP")
tdfNew.Connect = Join(strConnect, ";")
tdfNew.SourceTableName = strFileName
db.TableDefs.Append tdfNew
db.TableDefs.Delete strTable
db.TableDefs(strTable & "_TEMP").Name = strTable
End If
End Function
Die Routine erwartet als Parameter den Namen der Verknüpfung mit der Textdatei. Sie teilt dem Benutzer zunächst mit, dass er die Quelle für die Verknüpfung neu auswählen muss, und öffnet dann einen Dialog zum Auswählen der neuen Quelldatei (die dazu verwendete Funktion OpenFileName finden Sie im Modul mdlTools der Beispieldatenbank).
Wenn der Benutzer eine Datei ausgewählt hat, teilt die Prozedur deren Bezeichnung in Dateipfad und Dateiname auf.
Sie erzeugt einen Verweis auf die aktuelle Datenbank und speichert diesen in der Variablen db. Diese ermöglicht wiederum den Zugriff auf die TableDef-Objekte der Datenbank, wobei wir erstmal einen Verweis auf die zu ändernde Verknüpfung in tdfOld speichern.
Nun gilt es, die Connect-Zeichenkette so zu ändern, dass diese für den Parameter Database nicht mehr das vorherige, sondern das neue Verzeichnis enthält. Dies könnte man mit Zeichenkettenfunktionen erledigen, es geht aber auch eleganter.
Dabei machen wir uns zunutze, dass die einzelnen Name-Wert-Paare der Connect-Zeichenkette durch Semikola voneinander getrennt sind. Mit der Split-Funktion können wir so leicht das Array strConnect() mit den einzelnen Name-Wert-Paaren aus der Connect-Eigenschaft des TableDef-Objekts tdfOld füllen.
Die folgende Schleife durchläuft alle Elemente des Arrays, die ja wiederum über die Split-Funktion aufgeteilt werden können - nur, dass diesmal das Gleichheitszeichen (=) als Trennzeichen dient.
Dort, wo das Element vor dem Gleichheitszeichen den Wert Database besitzt, ändern wir den Wert hinter dem Gleichheitszeichen in das Verzeichnis der neu ausgewählten Datei.
Dann erstellt die CreateTableDef-Methode ein neues TableDef-Objekt und weist diesem mit ihrem ersten Parameter den Namen zu, der aus dem Namen der anzupassenden Verknüpfung und dem Anhängsel _TEMP besteht (also beispielsweise tblBeispielText_TEMP).
Der Connect-Eigenschaft weist die Prozedur das mit der Join-Funktion wieder in eine Zeichenkette zurückverwandelte Array aus strConnect() zu. Da das neue TableDef-Objekt noch nicht gespeichert wurde, können wir an dieser Stelle gleich noch die SourceTableName-Eigenschaft mit dem Dateinamen füllen.
Nun hängen wir das noch im freien Raum schwebende TableDef-Objekt an die TableDefs-Auflistung der aktuellen Datenbank an. Schließlich löschen wir noch die ursprüngliche Verknüpfung und ändern den Namen der neuen Verknüpfung entsprechend.
Dieses Procedere ist übrigens nur notwendig, wenn sich der Dateiname der Datenquelle geändert hat. Wenn diese bloß verschoben wurde, reicht es aus, per Connect-Eigenschaft den neuen Pfad anzugeben und die Verknüpfung mit RefreshLink zu aktualisieren.
Weitere Verarbeitung der Daten
Normalerweise liegen die Daten nach dem Verknüpfen oder Importieren noch nicht in der Form vor, in der Sie diese benötigen. Aber Sie haben schon einen großen Schritt geschafft: Die Daten liegen, egal ob in einer verknüpften oder importieren Tabelle, in einer Form vor, auf die Sie mit den Access-eigenen Mitteln zugreifen können. Nun haben Sie die Qual der Wahl:
- Selektieren Sie die Daten und schreiben Sie diese mit einer SELECT INTO-Aktionsabfrage in eine gleichzeitig erstellte Zieltabelle.
- Selektieren Sie die Daten und schreiben Sie diese mit einer INSERT INTO-Aktionsabfrage in eine bestehende Tabelle.
- Öffnen Sie per VBA ein Recordset auf Basis der verknüpften oder importierten Daten und kopieren Sie diese in so kleinen Schritten wie nötig in die Zieltabelle.
- Vielleicht aber müssen Sie die von der Textdatei importierten Daten auch gar nicht mehr bearbeiten oder können die verknüpften Daten gleich für Ihre Aufgabe benutzen, ohne diese zu importieren - dann haben Sie an dieser Stelle schon gewonnen.
Für die anderen Fälle gehen wir in einem weiteren Beitrag in der folgenden Ausgabe von Access im Unternehmen auf die Weiterverarbeitung importierter Daten in die endgültige Form ein.
|
