Zur Hauptseite ... Zum Onlinearchiv ... Zum Abonnement ... Zum Newsletter ... Zu den Tools ... Zum Impressum ... Zum Login ...

Achtung: Dies ist nicht der vollständige Artikel, sondern nur ein paar Seiten davon. Wenn Sie hier nicht erfahren, was Sie wissen möchten, finden Sie am Ende Informationen darüber, wie Sie den ganzen Artikel lesen können.

Kompletten Artikel lesen?

Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

E-Mail:

Gedrucktes Heft

Diesen Beitrag finden Sie in Ausgabe 6/2007.

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'!

Diesen Beitrag twittern

Zusammenfassung

Importieren Sie Daten aus Excel, auch wenn diese nicht per TransferSpreadsheet greifbar sind.

Techniken

VBA, Excel

Voraussetzungen

Access 2000, Excel 2000 und höher

Beispieldateien

Import.mdb, Daten.xls

Shortlink

530

Datenimport aus Excel

Michael Zimmermann, Mainz

Es ist eine immer wiederkehrende Aufgabenstellung: Daten werden als Excel-Datei geliefert und sollen in eine Datenbank eingelesen werden. Viele Access-Entwickler verwenden hier gerne die Methode DoCmd.TransferSpreadsheet. Diese stößt allerdings schnell an ihre Grenzen. Dieser Beitrag zeigt, welche Möglichkeiten es jenseits dieser Grenzen gibt - insbesondere dann, wenn die Importdaten im weit verbreiteten Kraut-und-Rüben-Format vorliegen.

Die TransferSpreadsheet-Methode weist im Wesentlichen zwei Einschränkungen auf: Sie steht nur innerhalb von Access-Anwendungen zur Verfügung - wenn Sie eine Anwendung als VB-Projekt entwerfen oder dorthin migrieren möchten, müssen Sie die Sache selbst in die Hand nehmen - und es ist begrenzt auf einzulesende Daten, die bereits ein relationales Format aufweisen, die also so vorliegen, wie sie auch in der Datenbank erscheinen sollen. Diese zweite Einschränkung betrifft auch andere Methoden, wie zum Beispiel den SQL-Zugriff auf Excel über den ISAM-Treiber oder das direkte Abfragen einer Excel-Datei über ein DAO- oder ADODB-Recordset.

Beim Verarbeiten der Importdaten spielen die Modulo- und Integerdivision eine entscheidende Rolle. Falls Sie mit diesen Operatoren nicht gut vertraut sind, finden Sie im folgenden Teil eine kleine Erläuterung. Wenn Sie auf diesem Gebiet jedoch firm sind, können Sie auch direkt zur ersten Aufgabenstellung springen.

Kleine Einführung: Ganzzahldivision

Die Modulo- (Mod) und Integer-Division (\) sind Ganzzahloperationen, die Folgendes leisten:

  • a \ b ergibt die größte ganze Zahl, die multipliziert mit b kleiner oder gleich a ist.
  • a Mod b ergibt den Rest dieser Division.

Einige Beispiele:

5 \ 2 = 2

5 Mod 2 = 1

14 \ 5 = 2

14 Mod 5 = 4

Das ist Division der zweiten Grundschulklasse: zwei passt zweimal in fünf, Rest eins; fünf passt zweimal in 14, Rest vier. Was Sie damit anfangen können, zeigt sich aber viel anschaulicher anhand einer kleinen Schleife:

For i = 0 To 15

Debug.Print i \ 5; i Mod 5

Next i

Für \ ergibt sich die Zahlenreihe 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, für Mod 0, 1, 2, 3, 4, 0, 1, 2, 3, 4, 0, 1, 2, 3, 4, 0. Mit der Basis 3 statt 5 ergäbe sich für \ die Reihe 0, 0, 0, 1, 1, 1, 2, 2, 2, 3, 3, 3 … und für Mod 0, 1, 2, 0, 1, 2, 0, 1, 2 …

Schauen Sie sich nun die Indexpaare eines Arrays aus Tabelle 1 an. Wenn Sie die Zellen zeilenweise durchnumerieren, lauten die Zeilenindizes (1, 1, 1, 2, 2, 2, 3, 3, 3) und die Spaltenindizes (1, 2, 3, 1, 2, 3, 1, 2, 3). Das sind genau die um eins erhöhten Ergebnisse von Zellennummer \ 3 und Zellennummer Mod 3.

1: 1,1

2: 1,2

3: 1,3

4: 2,1

5: 2,2

6: 2,3

7: 3,1

8: 3,2

9: 3,3

Tab. 1: Feldindizes einer Tabelle

Durch Verschiebung und Überlagerung solcher Indexfolgen durch Multiplikation, Addition und mehrfacher Integerdivision mit verschiedenen Basen können Sie aus einem laufenden Zähler beliebige Indexpaare erzeugen, mit denen Sie die Reihenfolge zum Schreiben der Daten aus einem Array in Felder und Datensätze festlegen - Beispiele dazu folgen.

Beispiel 1: Daten aus Kreuztabellen einlesen

Viel öfter als ordentlich angeordnete Daten mit Spalten für Felder und Zeilen für Datensätze liefern Kunden ein datentechnisches Tohuwabohu, das Sie nur per Excel-Automation umsetzen können (s. Abb. 1).

missing image file

Abb. 1: So sehen die zu importierenden Daten aus.

Die Daten sollen die Entwicklung der Quartalsumsätze in verschiedenen Kategorien (Konten, Vertriebsbereiche) von mehreren Filialen darstellen. Die eigentlichen Kategorien (Kategorie##) sind noch einmal Oberkategorien (Kategorie#) untergeordnet, die jeweils die Summe der untergeordneten Kategorien darstellen.

Sie konnten dem Kunden folgende Zusicherungen abringen:

  • In Zeile 1 stehen immer die Filialnamen. Für jede Filiale werden immer Zahlen für alle vier Quartale geliefert. Zwischen je zwei Filialnamen sind also sicher drei Leerzellen.
  • In Zeile 2 stehen die Quartalsbezeichnungen. Diese sind nicht fix, sondern können ohne Vorankündigung auch 1Q, 1.Quartal, Q1, Quartal eins et cetera lauten. Die Importroutine soll das klaglos verdauen und in der DB in jedem Fall die Zahlen 1, 2, 3 und 4 fürs Quartal ausgeben.

Bekannte Zahlen für abgelaufene Quartale, die je Filiale bereits vollständig verarbeitet wurden, sind durch einen grünen Hintergrund in der Quartalsbezeichnung gekennzeichnet. Für in der Zukunft liegende beziehungsweise noch nicht verarbeitete Quartale werden dennoch Zahlen geliefert; es handelt sich um extrapolierte Schätzungen der Controlling-Abteilung. Solche Werte sind an einem roten Hintergrund erkennbar. In der Datenbank sollen die Werte durch Flags wie Gemessen oder Geschätzt gekennzeichnet werden.

Spalte A enthält die Kategorienamen. Oberkategorien werden als berechnete Werte nicht in die Datenbank übernommen. Oberkategorien sind an Fettschrift erkennbar.

Das Zielformat in der Datenbank sieht also aus wie in Abb. 2 - hier dargestellt unter Excel.

missing image file

Abb. 2: Zielformat der Tabelle aus Abb. 1

Diese Aufgabe ist in keiner Weise mit TransferSpreadsheet oder anderen relationalen Methoden lösbar, zumal einige der abzubildenden Informationen in Form von Zellformatierungen geliefert werden.

Um die Tabelle im Beispiel zu verarbeiten, ist zunächst ein wenig Kenntnis der Objekthierarchie von Excel nötig.

Zuoberst steht das Application-Objekt, das die Anwendung darstellt. Darunter findet sich ein WorkBook-Objekt - das ist die zu verarbeitende Excel-Mappe. In dieser wiederum müssen Sie das Tabellenblatt, das die Daten enthält, identifizieren, was mit einem WorkSheet-Objekt geschieht. Hier sind jetzt noch der genaue Zellbereich beziehungsweise die einzelnen Zellen, welche die Daten liefern, zu identifizieren. Letzteres geschieht durch ein Range-Objekt. Die Hierarchie sieht also wie folgt aus:

Application

    Workbooks("Mappename")

        WorkSheets("Blattname")

            Range(<Zellbereich>)

Importieren mit der Cells-Eigenschaft

Nachfolgend sehen Sie zunächst die benötigten Deklarationen (den kompletten Code finden Sie im Modul ImportPivot der Beispieldatenbank, die importierte Tabelle heißt Pivot und ist in der Excel-Datei Daten.xls zu finden). Wenn Sie hier das Application-Objekt vermissen: Da beim Import keine visuelle Interaktion des Benutzers mit der einzulesenden Excel-Datei erforderlich ist, wird Ihnen hier ein Verfahren vorgestellt, das mit einem Minimum an Code alle Vorgänge komplett im Hintergrund ausführt.

Dim xlWbk As Excel.Workbook

Dim xlSht As Excel.Worksheet

Dim xlRng As Excel.Range

Dim Path As String

Dim LastCol As Long

Dim LastRow As Long

Dim c As Long, r As Long

Dim rcs As DAO.Recordset

Zunächst müssen Sie natürlich den Pfad der einzulesenden Datei bekannt geben, was je nach Anforderung relativ zur Programmdatei mit einem festen Pfad oder einem Datei-Öffnen-Dialog geschehen kann. Damit können Sie direkt und ohne Umstände ein Workbook-Objekt, das die Mappe darstellt, erzeugen. Davon leiten Sie ein Worksheet-Objekt zur Darstellung des Tabellenblatts mit den Daten ab:

Path = "Pfad\Daten.xls”

Set xlWbk = CreateObject(Path)

Set xlSht = _

     xlWbk.Worksheets("Pivot”)

Die folgenden Anweisungen ermitteln die letzte beschriebene Spalte und Zeile des Datenbereichs, damit Sie bezüglich der Datenmenge völlig flexibel sind. Das dort aufgebaute Recordset identifiziert die Zieltabelle und dient dazu, die Daten dort einzutragen:

LastRow = xlSht.Cells(&H10000, 1).End(xlUp).Row

LastCol = xlSht.Cells(2, &H100&).End(xlToLeft).Column

Die eigentliche Arbeit leistet die Schleife aus Listing 1. Die Variable c durchläuft die Spaltenindizes, r die Zeilenindizes. Mit der Cells-Methode des Worksheet-Objekts wird dynamisch jeweils ein Range, der nur eine Zelle enthält, identifiziert. Die Schleife beginnt mit c = 2 und r = 3, also der Zelle B3, weil dort die Nutzdaten anfangen (s. Abb. 2).

missing image file

Abb. 3: Weiteres Beispiel eines Importformats - diesmal ohne durch Formatierungen definierte Informationen

Listing 1: Auslesen von Daten aus einer pivot-artigen Tabelle

Set rcs = CurrentDb.OpenRecordset _
    ("SELECT * FROM tblImport WHERE ID Is Null”)

With xlSht

    For c = 2 To LastCol

        For r = 3 To LastRow

            If .Cells(r, 1).Font.Bold = False Then

                rcs.AddNew

                rcs.Fields("Kategorie”).Value = .Cells(r, 1).Value

                rcs.Fields("Quartal”).Value = ((c - 2) Mod 4) + 1

                rcs.Fields("Filiale”).Value = _
                    .Cells(1, 4 * ((c - 2) \ 4) + 2).Value

Sie haben das Ende des frei verfügbaren Teils des Artikels erreicht. Lesen Sie weiter, um zu erfahren, wie Sie den vollständigen Artikel lesen und auf viele hundert weitere Artikel zugreifen können.

Sind Sie Abonnent?Jetzt einloggen ...
 

Kompletten Artikel lesen?

Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

E-Mail:

© 2003-2015 André Minhorst Alle Rechte vorbehalten.