<\/p>\n
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.<\/b><\/p>\n<\/div>\n Die TransferSpreadsheet<\/b>-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.<\/p>\n 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.<\/p>\n Kleine Einführung: Ganzzahldivision<\/p>\n Die Modulo- (Mod) und Integer-Division (\\) sind Ganzzahloperationen, die Folgendes leisten:<\/p>\n Einige Beispiele:<\/p>\n 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:<\/p>\n Für \\ <\/b>ergibt sich die Zahlenreihe 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, für Mod <\/b>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 \\ <\/b>die Reihe 0, 0, 0, 1, 1, 1, 2, 2, 2, 3, 3, 3 … und für Mod <\/b>0, 1, 2, 0, 1, 2, 0, 1, 2 …<\/p>\n 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 <\/b>und Zellennummer Mod 3<\/b>.<\/p>\n Tab. 1: Feldindizes einer Tabelle<\/span><\/b><\/p>\n<\/td>\n 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.<\/p>\n Beispiel 1: Daten aus Kreuztabellen einlesen<\/b><\/p>\n 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 (siehe Bild 1).<\/p>\n Bild 1: So sehen die zu importierenden Daten aus.<\/span><\/b><\/p>\n<\/div>\n Die Daten sollen die Entwicklung der Quartalsumsätze in verschiedenen Kategorien (Konten, Vertriebsbereiche) von mehreren Filialen darstellen. Die eigentlichen Kategorien (Kategorie##<\/b>) sind noch einmal Oberkategorien (Kategorie#<\/b>) untergeordnet, die jeweils die Summe der untergeordneten Kategorien darstellen.<\/p>\n Sie konnten dem Kunden folgende Zusicherungen abringen: <\/p>\n 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 <\/b>oder Geschätzt<\/b> gekennzeichnet werden.<\/p>\n Spalte A enthält die Kategorienamen. Oberkategorien werden als berechnete Werte nicht in die Datenbank übernommen. Oberkategorien sind an Fettschrift erkennbar.<\/p>\n Das Zielformat in der Datenbank sieht also aus wie in Bild 2 – hier dargestellt unter Excel.<\/p>\n Bild 2: Zielformat der Tabelle aus Bild 1<\/span><\/span><\/b><\/p>\n<\/div>\n Diese Aufgabe ist in keiner Weise mit TransferSpreadsheet <\/b>oder anderen relationalen Methoden lösbar, zumal einige der abzubildenden Informationen in Form von Zellformatierungen geliefert werden.<\/p>\n Um die Tabelle im Beispiel zu verarbeiten, ist zunächst ein wenig Kenntnis der Objekthierarchie von Excel nötig.<\/p>\n Zuoberst steht das Application<\/b>-Objekt, das die Anwendung darstellt. Darunter findet sich ein WorkBook<\/b>-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<\/b>-Objekt geschieht. Hier sind jetzt noch der genaue Zellbereich beziehungsweise die einzelnen Zellen, welche die Daten liefern, zu identifizieren. Letzteres geschieht durch ein Range<\/b>-Objekt. Die Hierarchie sieht also wie folgt aus:<\/p>\n Importieren mit der Cells-Eigenschaft<\/p>\n Nachfolgend sehen Sie zunächst die benötigten Deklarationen (den kompletten Code finden Sie im Modul ImportPivot <\/b>der Beispieldatenbank, die importierte Tabelle heißt Pivot <\/b>und ist in der Excel-Datei Daten.xls <\/b>zu finden). Wenn Sie hier das Application<\/b>-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.<\/p>\n 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<\/b>-Dialog geschehen kann. Damit können Sie direkt und ohne Umstände ein Workbook<\/b>-Objekt, das die Mappe darstellt, erzeugen. Davon leiten Sie ein Worksheet<\/b>-Objekt zur Darstellung des Tabellenblatts mit den Daten ab:<\/p>\n 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:<\/p>\n Die eigentliche Arbeit leistet die Schleife aus Listing 1. Die Variable c <\/b>durchläuft die Spaltenindizes, r <\/b>die Zeilenindizes. Mit der Cells<\/b>-Methode des Worksheet<\/b>-Objekts wird dynamisch jeweils ein Range<\/b>, der nur eine Zelle enthält, identifiziert. Die Schleife beginnt mit c = 2 <\/b>und r = 3<\/b>, also der Zelle B3<\/b>, weil dort die Nutzdaten anfangen (siehe Bild 2).<\/p>\n Bild 3: Weiteres Beispiel eines Importformats – diesmal ohne durch Formatierungen definierte Informationen<\/span><\/b><\/p>\n<\/div>\n Listing 1: Auslesen von Daten aus einer pivot-artigen Tabelle<\/p>\n Set rcs = CurrentDb.OpenRecordset _ With xlSht<\/p>\n For c = 2 To LastCol<\/p>\n For r = 3 To LastRow<\/p>\n If .Cells(r, 1).Font.Bold = False Then<\/p>\n rcs.AddNew<\/p>\n rcs.Fields(„Kategorie“).Value = .Cells(r, 1).Value<\/p>\n rcs.Fields(„Quartal“).Value = ((c – 2) Mod 4) + 1<\/p>\n rcs.Fields(„Filiale“).Value = _ rcs.Fields(„Wert“).Value = .Cells(r, c).Value<\/p>\n If .Cells(2, c).Interior.Color = 65280 _<\/p>\n Then rcs.Fields(„Erhebungsart“).Value = „Erhoben“<\/p>\n If .Cells(2, c).Interior.Color = 255 _<\/p>\n Then rcs.Fields(„Erhebungsart“).Value = „Geschätzt“<\/p>\n rcs.Update<\/p>\n End If<\/p>\n Next r<\/p>\n Next c<\/p>\n End With<\/p>\n rcs.Close<\/p>\n Set rcs = Nothing<\/p>\n<\/div>\n <\/p>\n .Cells(r, 1).Font.Bold = False<\/b> prüft, ob ein echter Wert oder eine Summe vorliegt – Letztere wird Ihnen ja als Fett gekennzeichnet und nicht importiert -, die Kategoriebezeichnung steht als Zeilenkopf immer in Spalte 1, daher .Cells(r, 1).Value<\/b>.<\/p>\n Der Quartalswert 1 <\/b>bis 4 <\/b>wird nicht ausgelesen – Sie wollen von der verwendeten Bezeichnung unabhängig sein -, sondern aus der Spaltennummer berechnet, ausgehend von der ersten Datenspalte und der Regel, dass sich der Wert alle vier Spalten wiederholt: ((c – 2) Mod 4) + 1<\/b><\/p>\n Die Filialbezeichnungen stammen aus der ersten Zeile, stehen aber immer nur über dem ersten Quartal. Dem trägt die Formel .Cells(1, 4 x ((c – 2) \\ 4) + 2).Value <\/b>Rechnung.<\/p>\n Richtig einfach haben Sie es mit den Nutzdaten – die Formel lautet: .Cells(r, c).Value<\/b><\/p>\n Das Feld Erhebungsart <\/b>ermitteln Sie aus den Hintergrundfarben, also .Cells(2, c).Interior.Color = 65280<\/b> beziehungsweise .Cells(2, c).Interior.Color = 255<\/b>. Der restliche Code betrifft das Recordset und dürfte selbsterklärend sein.<\/p>\n Diese Methode, bei der mittels Cells(r, c) <\/b>der Reihe nach die relevanten Zellen identifiziert und darüber sowohl deren Werte als auch Formatierungen gelesen werden können, ist sehr anschaulich und für kleinere Datenmengen völlig ausreichend. Es geht aber auch noch erheblich schneller, wie die folgende fiktive Anforderung zeigt.<\/p>\n Beispiel 2: Daten im Kreuzschema einlesen ohne Formatierungen<\/b><\/p>\n Im zweiten Beispiel liegen die Daten zwar immer noch in einem Kreuzschema, aber wenigstens in Textform vor. Dadurch brauchen Sie keine Zellformatierungen mehr auszulesen, sondern müssen nur die Zellinhalte bestimmen. Dadurch können Sie eine Methode einsetzen, die die benötigte Laufzeit um einen Faktor 15 bis 25 (bei vielen Daten noch erheblich mehr) reduziert. <\/p>\n Excel bietet die Möglichkeit, alle Werte eines beliebig großen Zellbereichs einer Variant-Variablen zuzuweisen. Dabei wird automatisch ein zweidimensionales Array erzeugt, das man dann ähnlich wie eben die Tabelle selbst mit einer Schleife durchlaufen kann. Da Arrays auf Grund ihres Speicherdesigns schnelle Strukturen sind, ist ein solcher Durchlauf erheblich zügiger als einer durch die mit der Cells<\/b>-Eigenschaft immer wieder aufgebaute Objekt-Struktur.<\/p>\n Die Deklarationen dieses Beispiels stimmen im wesentlichen mit denen aus dem ersten Beispiel überein, wir beschreiben daher an dieser Stelle nur die wichtigsten Elemente der Routine – das komplette Listing finden Sie im Modul ImportBlock<\/b>.<\/p>\n Zunächst belegen Sie eine Range<\/b>-Variable mit dem einzulesenden Zellbereich. Hier ist Cells(1, 1)<\/b>, also A1<\/b>, die Startzelle. Statt eine Endzelle anzugeben, vergrößern Sie den Bereich ganz bequem mit der Methode Resize(Zeilen, Spalten) <\/b>des Range<\/b>-Objekts auf die erforderliche Größe, was sich ausgehend von (1, 1) <\/b>mit LastRow <\/b>und LastCol <\/b>deckt. Um diesen gesamten Zellbereich auszulesen, genügt der Einzeiler v = xlRng.Value<\/b>.<\/p>\n Die Excel-Mappe wird nun nicht mehr benötigt und daher mit Close <\/b>geschlossen. Da zum (unsichtbaren) öffnen der Mappe ein Start von Excel unumgänglich ist, wird es hierbei ganz automatisch wieder geschlossen – ein Segen der impliziten Anwendungsinstanzierung. Durch diese Maßnahme geben Sie je nach Version 20 bis 30 Megabyte Arbeitsspeicher für die laufende Excel-Anwendung frei; im Speicher verbleibt nur ein kompaktes Array, das alle Daten enthält.<\/p>\n Nach dem üblichen Erstellen eines Recordsets mit der Zieltabelle arbeitet Access die nachfolgend vorgestellte Schleife ab, die nun über das Array läuft. Dieses automatisch generierte Array, das in der Variant-Variablen v <\/b>enthalten ist, wird von Excel immer einsbasiert und zweidimensional erzeugt, verhält sich also so, als sei es wie folgt deklariert worden:<\/p>\n Daran können Sie nichts ändern und Sie müssen sich im Code darauf einstellen.<\/p>\n Diese alternative Methode vermeidet eine Doppelschleife. Zunächst durchläuft die Routine die Zellen von (1, 1) <\/b>bis (LastRow, LastCol)<\/b>, was dem Produkt aus LastRow <\/b>x LastCol <\/b>Zellen entspricht. Die benötigten Zeilen- und Spaltenindizes ergeben sich aus der Spaltenzahl – hier sind es insgesamt 13 für zwölf Monate und die Kopfspalte, was aber auch dynamisch aus dem Abstand der ersten Datenspalte und LastCol <\/b>berechnet werden kann. Wenn sich die Spalten alle 13 Zellen wiederholen und sich entsprechend die Zeilennummer alle 13 Zellen um eins erhöht, bekommen Sie die entsprechenden Indizes mit den folgenden Formeln der Modulo- und Integerdivision:<\/p>\n Da die Nutzdaten in der zweiten Zeile und zweiten Spalte beginnen, brauchen Sie eine zusätzliche Prüfung:<\/p>\n Schöner, da nur mit Mathematik auskommend, ist die folgende Variante:<\/p>\n Noch einen obendrauf setzt diese Zeile:<\/p>\n Die Zuweisungen an die Felder des Recordsets sehen so aus:<\/p>\n Der Zeitgewinn der Array<\/b>-Methode gegenüber der Cells<\/b>-Methode ist erheblich. Wollten Sie auch das erste Beispiel darauf umstellen, gingen Sie wegen der teilweise als Formatierungen vorliegenden Daten folgendermaßen vor: Bedeutungstragende Formatierungen kommen in der Kopfspalte und zwei Kopfzeilen vor.<\/p>\n Durchlaufen Sie diese Bereiche mit Cells <\/b>und legen Sie die relevanten Informationen in Hilfs-Arrays ab. Lesen Sie den eigentlichen Datenbereich mit Value <\/b>in ein Variant-Array ein. Bei großen Datenmengen ist das trotz des höheren Code-Aufwands immer noch schneller, als alles mit Cells <\/b>durchzugehen.<\/p>\n Angenommen, Sie lesen 50 Spalten á 2.000 Zeilen ein. Mit dem Vorgehen aus dem ersten Beispiel wenden Sie 100.000 Mal Cells <\/b>an. Mit der hier vorgeschlagenen Kombination wenden Sie 2.050 Mal Cells <\/b>an und einmal Value<\/b>.<\/p>\n Noch besser ist es natürlich, Sie reden dem Kunden aus, Formate als Informationsträger zu benutzen, dann wenden Sie in jedem Fall nur einmal Value <\/b>an.<\/p>\n Immer sollten Sie aber berücksichtigen, wie oft der Import tatsächlich stattfindet – geschieht dies nur einmal, können Sie die Performance natürlich eher vernachlässigen, als wenn regelmäßig Daten importiert werden.<\/p>\n Import aus Tabellen mit redundanten Der Import soll jetzt noch etwas schwieriger werden. Ausgehend vom vorherigen Beispiel mit seinen zwölf Monatswerten sieht die Quelltabelle nun wie in Bild 4 aus und enthält nach jeweils drei Monatsspalten noch eine zusätzliche Spalte zur Anzeige der Quartalssumme. Diese soll natürlich nicht importiert werden, da ihre Daten von den jeweils links daneben befindlichen Spalten abhängig sind und somit in der Datenbank redundant wären.<\/p>\n Bild 4: Tabellenblatt für den Import, das zu überspringende Spalten enthält<\/span><\/b><\/p>\n<\/div>\n Statt umständlich auf Beschriftungen wie Q1 <\/b>in der Kopfzeile zu prüfen, die sich unter Umständen auch ändern, müssen Sie für diesen Fall nur den Ausdruck anpassen, der die Spaltenindizes berechnet:<\/p>\n Beachten Sie den Ausdruck (17 – 4)<\/b>, der etwa den Ausdruck 13 <\/b>ersetzt: Eine Kopfspalte plus zwölf Monate plus vier Quartalssummen (der Ausdruck 17 – 4 <\/b>steht hier natürlich nur zur Verdeutlichung: 17 Spalten, von denen vier wegfallen). Das funktioniert natürlich noch nicht, da so nach wie vor die Zahlenreihe von 1 bis 13, also 1, 2, 3 … 13, erzeugt wird. <\/p>\n Damit alle 17 Spalten unter Auslassung der Summenspalten berücksichtigt werden, ergänzen Sie den Ausdruck wie folgt:<\/p>\n Der so geänderte Code ergibt für c <\/b>die sich wiederholende Zahlenreihe (1, 2, 3, 4, 6, 7, 8, 10, 11, 12, 14, 15, 16). Ein Blick in Bild 4 zeigt: Es fehlen genau die Spalten, die Sie nicht importieren wollen, nämlich 5 (E, Q1), 9 (I, Q2), 13 (M, Q3) und 17 (Q, Q4). Davon abgesehen verwenden Sie die gleiche Routine wie im vorherigen Beispiel.<\/p>\n Im Detail passiert hier Folgendes: Der Bereich erstreckt sich nun über 17 statt wie vorher 13 Spalten. Aber Sie wollen nach wie vor nur 13 von diesen 17 Spalten importieren, vier werden übergangen. Daher sind immer noch lastRow x 13 <\/b>Schritte nötig. Das gilt sowohl für den Schleifenkopf, lastCol – 4 <\/b>ist ja 17 – 4<\/b>, als auch für die Ausdrücke für r <\/b>und c<\/b>.<\/p>\n Der c<\/b>-Ausdruck erzeugt natürlich dieselbe Ausgabe, wie er das auf Basis 13 auch schon tat, nämlich (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13).<\/p>\n Sie brauchen aber stattdessen die Reihe (1, 2, 3, 4, 6, 7, 8, 10, 11, 12, 14, 15, 16). Das erreichen Sie durch Addition von (0, 0, 0, 0, 1, 1, 1, 2, 2, 2, 3, 3, 3). Diese Zahlenreihe erzeugt der Ausdruck (c – 2) \\ 3<\/b>, der deshalb addiert wird.<\/p>\n Einlesen von Daten in Blockdarstellung<\/b><\/p>\n Nun setzt Ihnen Ihr Kunde noch einen drauf. Die Daten sollen aus einer Excel-Tabelle in Blockdarstellung importiert werden (siehe Bild 5).<\/p>\n Bild 5: Excel-Tabelle im Blockformat<\/span><\/b><\/p>\n<\/div>\n Ein Datensatz kommt hier als Block der Tiefe 3 <\/b>und der Breite 2 <\/b>daher. Die Bedeutung ist durch die Anordnung festgelegt:<\/p>\n Auch diese Aufgabenstellung lösen Sie mit einer kleinen Modifikation der vorherigen Beispiele.<\/p>\n Da ein Datensatz drei Zeilen und zwei Spalten belegt, errechnet sich die Anzahl der Schleifendurchläufe wie folgt:<\/p>\n Die 4 x 12 = 48 Zellen im Beispiel entsprechen 48 \/ 6 = 8 Datensätzen. Sie müssen also in einer Schleife von 1 bis 8 aus der Zählvariablen je 6 Indexpaare erzeugen. Der relevante Code dazu sieht wie in Listing 2 aus.<\/p>\n Listing 2: Auslesen von Daten en bloc<\/p>\n Set xlWbk = CreateObject(Path)<\/p>\n Set xlSht = xlWbk.Worksheets(„Blöcke“)<\/p>\n LastRow = xlSht.Cells(&H10000, 2).End(xlUp).Row<\/p>\n LastCol = xlSht.Cells(2, &H100&).End(xlToLeft).Column<\/p>\n Set xlRng = xlSht.Cells(2, 2).Resize(LastRow, LastCol)<\/p>\n v = xlRng.Value<\/p>\n xlWbk.Close xlDoNotSaveChanges<\/p>\n Set rcs = CurrentDb.OpenRecordset _ With rcs<\/p>\n For i = 1 To (LastRow – 1) * (LastCol – 1) \/ 6<\/p>\n r = (i – 1) \\ 2 + 1<\/p>\n c = (i – 1) Mod 2 + 1<\/p>\n .AddNew<\/p>\n .Fields(„Anrede“).Value = v(3 * r – 2, 2 * c – 1)<\/p>\n .Fields(„Vorname“).Value = v(3 * r – 1, 2 * c – 1)<\/p>\n .Fields(„Nachname“).Value = v(3 * r, 2 * c – 1)<\/p>\n .Fields(„GebDat“).Value = v(3 * r – 2, 2 * c)<\/p>\n .Fields(„Gewicht“).Value = v(3 * r – 1, 2 * c)<\/p>\n .Fields(„Groesse“).Value = v(3 * r, 2 * c)<\/p>\n .Update<\/p>\n Next i<\/p>\n End With<\/p>\n rcs.Close<\/p>\n Set rcs = Nothing<\/p>\n<\/div>\n Da im zweiten Beispiel die Datensätze nebeneinander stehen, wird analog zu den bisherigen Beispielen der Zeilen- und Spaltenindex mit Modulo- beziehungsweise Integerdivision über der Basis 2 erzeugt. Durch die Blockbildung hält hier aber ein r-c<\/b>-Pärchen für sechs Indexwerte her.<\/p>\n Aus dem Zeilenindex 1 wird durch Multiplikation 3, aus dem Zeilenindex 2 wird 6 und so weiter. Es ergeben sich also Dreierschritte. Durch die Subtraktion von 2, 1 und 0 wird so aus 1, 2, … (1, 2, 3), (4, 5, 6) … <\/p>\n Für die Spalten gilt das Gleiche – nur mit zwei statt drei Werten -, womit sich durch Kombination alle sechs benötigten Indexpaare ergeben.<\/p>\n Zwischenfazit<\/b><\/p>\n Das Prinzip, das hinter dem Linearisieren der Variant-Arrays steht, ist immer dasselbe. Die logische Struktur, die in der Anordnung der Daten steckt, zeigt sich in regelmäßigen Wiederholungen und Iterationen von Zeilen- und Spaltennummern, die Sie mittels Modulo- und Integerdivision sowie einigen einfachen Grundrechenoperationen nachbilden. Mit etwas übung können Sie so beliebig komplexe Regeln der Datengeometrie abbilden.<\/p>\n Alternative ADODB<\/b><\/p>\n An Stelle des gezeigten Codes mit DAO wollen oder müssen Sie vielleicht mit ADO arbeiten. Hier sind passende Angaben zu LockType<\/b>, CursorLocation <\/b>und CursorType <\/b>eines Recordsets von entscheidender Bedeutung, da Ihnen sonst die Performance ganz erheblich in die Knie gehen kann. Ein Beispiel für sinnvolle Einstellungen zeigt Listing 3.<\/p>\n Listing 3: Excel-Daten einlesen per ADO<\/p>\n Set rsa = New ADODB.Recordset<\/p>\n With rsa<\/p>\n Set .ActiveConnection = cnn<\/p>\n .LockType = adLockOptimistic<\/p>\n .CursorLocation = adUseServer<\/p>\n .CursorType = adOpenKeyset<\/p>\n .Source = SQL<\/p>\n .Open<\/p>\n For i = 1 To LastRow * LastCol<\/p>\n c = (i – 1) Mod 13 + 1<\/p>\n r = (i – 1) \\ 13 + 1<\/p>\n If (c – 1) * (r – 1) > 0 Then<\/p>\n .AddNew<\/p>\n .Fields(„BG“).Value = V(r, 1)<\/p>\n .Fields(„Monat“).Value = V(1, c)<\/p>\n .Fields(„dtWert“).Value = V(r, c)<\/p>\n .Update<\/p>\n End If<\/p>\n Next i<\/p>\n .Close<\/p>\n End With<\/p>\n Set rsa = Nothing<\/p>\n<\/div>\n Die schnellste einstellbare ADO-Variante ist beim Jet-Zugriff immerhin nur noch doppelt so langsam wie das DAO-Pendant – Sie können ADO aber mit falschen Einstellungen problemlos bis zur Unerträglichkeit ausbremsen.<\/p>\n Wenn Sie das Daten-Backend nicht auf der lokalen Maschine, auf der der Import durchgeführt wird, sondern auf einem Netzwerkserver liegen haben, sollten Sie Ihr Recordset nicht auf der CurrentDb <\/b>(DAO) oder CurrentProject.Connection <\/b>(ADO) und somit auf eingelinkten Tabellen aufbauen, sondern eine direkte Verbindung zum Backend herstellen.<\/p>\n Bei DAO läuft Ihr Code dann etwa drei- bis viermal so schnell, bei ADO ist der Unterschied viel größer: Ein mit den Eigenschaften LockType<\/b> und CursorLocation <\/b>optimistisch eingestelltes Recordset auf verknüpfte Tabellen braucht bis zu 300 bis 400 Mal länger als ein pessimistisch eingestelltes auf dem Server.<\/p>\n Da ADO längst nicht alle Kombinationen von LockType<\/b>, CursorLocation <\/b>und CursorType <\/b>unterstützt – viele davon wären in sich unlogisch -, sollten Sie sich vorsichtshalber mit folgender Zeile davon überzeugen, was da jetzt eigentlich herausgekommen ist.<\/p>\n Wenn Sie nämlich eine unsinnige oder keine Kombination angeben, nimmt ADO selbst eine Einstellung vor, was keineswegs immer optimal ist.<\/p>\n Performance<\/b><\/p>\n Zunächst ein Vergleich der Gesamtlaufzeit des Importvorgangs mit der Methode des zellenweisen Durchlaufens gegen Verwendung eines Arrays. Bei 6.000 einzufügenden Datensätzen ergab sich auf dem Testsystem:<\/p>\n Die Cells<\/b>-Methode ist damit außer für geringe Datenmengen klar deklassiert. Die Array<\/b>-Methode gliedert sich in drei entscheidende Schritte – jeweils mit verbrauchter Zeit:<\/p>\n Die ersten beiden Punkte sind bei ADO natürlich identisch; statt der 170 Millisekunden im dritten Schritt dauert dieser aber immerhin 590 Millisekunden.<\/p>\n Zum Vergleich DAO – ADO noch einige Zahlen, gemessen mit 600 Datensätzen übers Netz auf ein entferntes Backend. Die angegebenen Zeiten beziehen sich immer nur auf den letzten Schritt, also das Wegschreiben des Arrays mit einem Recordset in einer Schleife.<\/p>\n Die folgenden Werte beziehen sich auf ADO:<\/p>\n Mit DAO haben wir folgende Werte ermittelt:<\/p>\n\n\n
5 \\ 2 = 2\r\n5 Mod 2 = 1\r\n14 \\ 5 = 2\r\n14 Mod 5 = 4<\/pre>\n
For i = 0 To 15\r\n Debug.Print i \\ 5; i Mod 5\r\nNext i<\/pre>\n
\n\n\n
\n \n<\/td>\n \n<\/td>\n \n<\/td>\n<\/tr>\n \n \n<\/td>\n \n<\/td>\n \n<\/td>\n<\/tr>\n \n \n<\/td>\n \n<\/td>\n \n<\/td>\n<\/tr>\n \n \n \n <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n \n ![\"missing](\"..\/fileadmin\/_temp_\/2007_06\/Excel-web-images\/Bild363_opt.jpeg\")
\n<\/div>\n\n\n
\n ![\"missing](\"..\/fileadmin\/_temp_\/2007_06\/Excel-web-images\/Bild376_opt.jpeg\")
\n<\/div>\n\nApplication\r\n Workbooks(\"Mappename\")\r\n WorkSheets(\"Blattname\")\r\n Range(<Zellbereich>)<\/pre>\n
Dim xlWbk As Excel.Workbook\r\nDim xlSht As Excel.Worksheet\r\nDim xlRng As Excel.Range\r\nDim Path As String\r\nDim LastCol As Long\r\nDim LastRow As Long\r\nDim c As Long, r As Long\r\nDim rcs As DAO.Recordset<\/pre>\n
Path = \"Pfad\\Daten.xls\"\r\nSet xlWbk = CreateObject(Path)\r\nSet xlSht = _\r\n xlWbk.Worksheets(\"Pivot\")<\/pre>\n
LastRow = xlSht.Cells(&H10000, 1).End(xlUp).Row\r\nLastCol = xlSht.Cells(2, &H100&).End(xlToLeft).Column<\/pre>\n
\n ![\"missing](\"..\/fileadmin\/_temp_\/2007_06\/Excel-web-images\/Bild382_opt.jpeg\")
\n<\/div>\n\n\n
(„SELECT * FROM tblImport WHERE ID Is Null“)<\/p>\n
.Cells(1, 4 * ((c – 2) \\ 4) + 2).Value<\/p>\nSet xlRng = _
xlSht.Cells(1, 1).Resize(LastRow, LastCol)\r\nv = xlRng.Value\r\nxlWbk.Close xlDoNotSaveChanges<\/pre>\nDim v(1 To Zeilen, 1 To Spalten)<\/pre>\n
For i = 1 To LastRow * LastCol\r\n c = (i - 1) Mod 13 + 1\r\n r = (i - 1) \\ 13 + 1\r\n If (c - 1) * (r - 1) > 0 Then\r\n .AddNew\r\n .Fields(\"BG\").Value = v(r, 1)\r\n .Fields(\"Monat\").Value = v(1, c)\r\n .Fields(\"dtWert\").Value = v(r, c)\r\n .Update\r\n End If\r\nNext i<\/pre>\n
c = (i - 1) Mod 13 + 1\r\n r = (i - 1) \\ 13 + 1<\/pre>\n
If c > 1 And r > 1<\/pre>\n
If (c - 1) * (r - 1) > 0 <\/pre>\n
If r * c > r + c - 1<\/pre>\n
\n
Informationen<\/p>\n\n ![\"missing](\"..\/fileadmin\/_temp_\/2007_06\/Excel-web-images\/Bild388_opt.jpeg\")
\n<\/div>\n\nc = (i - 1) Mod (17 - 4) + 1\r\nr = (i - 1) \\ (17 - 4) + 1<\/pre>\n
For i = 1 To LastRow * (LastCol - 4)\r\n r = (i - 1) \\ (17 - 4) + 1\r\n c = (i - 1) Mod (17 - 4) + 1\r\n c = c + (c - 2) \\ 3<\/pre>\n
\n ![\"missing](\"Excel-web-images\/Bild394_opt.jpeg\")
\n<\/div>\n\n\n
(Bereichstiefe * Bereichsbreite) \/ (Blocktiefe * Blockbreite)<\/pre>\n
\n
(„SELECT * FROM tblPerson WHERE ID Is Null“)<\/p>\n\nDebug.Print .LockType; .CursorLocation; .CursorType<\/pre>\n
\n
\n
\n