"Sverweis" / "vlookup" mit ADODB

Forumthread: "Sverweis" / "vlookup" mit ADODB

03.07.2024 11:39:53

Theo

Hallo zusammen,

vorneweg, geht es um das Thema SQL und ADODB bin ich noch ein kompletter Noob.

Ich muss eine lange Tabelle (mit ca 1 Mio Datensätzen) mit Daten aus einem Textfile (mit ca. 5 Mio Datensätzen) anreichern. Wären das weniger Datensätze würde ich das Textfile einfach in einen anderen Tab laden, und dann per SVerweis / Vlookup (bzw über dictionaries) die Daten einfach zuordnen.

Da es sich sowohl in der Tabelle als auch im Textfile im um sehr viele Datensätze handelt, verwende ich ADODB um das textfile (faktisch komplett) in ein Array einzulesen.
Dieser Schritt geht auch rasend schnell.

Schwierig wird es dann die Daten aus dem Array auszulesen und in die richtige Zelle zu schreiben.
Im angeängten Beispiel geht das natürlich bedingt durch die geringe Anzahl an Datensätzen schnell, im Orignal ist das aber nicht zumutbar.
https://www.herber.de/bbs/user/170649.xlsb

Die Struktur des Textfiles (inklusive ein paar Beispiel Daten) und der Inhalt der Schema.ini ist in der Arbeitsmappe ersichtlich.

Enable events, Calculation und screenupdating habe ich nur für das Beispiel weggelassen.

Habt Ihr eine Idee wie man das noch beschleunigen könnte?

Theo

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: "Sverweis" / "vlookup" mit ADODB

03.07.2024 13:35:51

JoWE

Hallo Theo,
schon Power Query unter Excel probiert?
Nein? Dann lies dies:
https://excelhero.de/power-query/power-query-ganz-einfach-erklaert/
Gruß
Jochen

AW: "Sverweis" / "vlookup" mit ADODB

03.07.2024 16:02:53

peter

Hallo

Hab mal deine Datensätze nachgespielt (1Mio/5Mio) : auf meinem Rechner 15 Sekunden.

Was hab ich gemacht:

* Ein Globale Varible definiert



Option Explicit

Public LastFound As Long

* Die Daten in der Tabelle nach FileName sortiert (sehr wichtig!)

* Die Datensätze (1Mio) in ein Array eingelesen



    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    myArrRange = ws.Range("A2:C" & LastRow)

* Den Select Befehl modifiziert



    rs.ActiveConnection = cn

    rs.Source = "SELECT FileName, Archive, Folder FROM [archivecontents.txt] Order by FileName"

* Die Schleife modifiziert



For BeispielRow = 1 To LastRow - 1

    

        '----------------------------------------------------------------------------------------------

        '--- FileName aus Spalte A lesen

        '---------------------------------------------------------------------------------------------

        StrAttachment = myArrRange(BeispielRow, 1)

                  

        '----------------------------------------------------------------------------------------------

        '--- Filename in Array finden

        '---------------------------------------------------------------------------------------------

        IRowAr = FindValueInArray(arr, StrAttachment)

        

        '----------------------------------------------------------------------------------------------

        '--- Folder nd Archiv aus Array extraieren

        '---------------------------------------------------------------------------------------------

        If IRowAr > 0 Then

            StrArchive = arr(1, IRowAr)

            StrFolder = arr(2, IRowAr)

            

            '----------------------------------------------------------------------------------------------

            '--- Werte in Datei Schreiben

            '---------------------------------------------------------------------------------------------

            myArrRange(BeispielRow, 2) = StrArchive

            myArrRange(BeispielRow, 3) = StrFolder

        End If

* Die Find... Funktion modifiziert



    For c = LastFound To UBound(arr, 2)

        cc = cc + 1

        If arr(0, c) = val Then

            FindValueInArray = c

            LastFound = c

            Exit Function

        End If

    Next c

* und am Schluss das Array wieder zurückgeschrieben



    On Error GoTo 0

    ws.Range("A2:C" & LastRow) = myArrRange

Peter

AW: "Sverweis" / "vlookup" mit ADODB

03.07.2024 19:05:48

Theo

Peter,

vielen Dank! Das funzt einwandfrei!

Liebe Grüße

Theo

AW: "Sverweis" / "vlookup" mit ADODB

04.07.2024 09:36:04

Theo

Jetzt habe ich doch noch ein Problem!
Offensichtlich sortiert Excel anders als SQL.

Im Recordset aus SQL bekomme ich diese drei exemplarischen Anhänge in dieser Reihenfolge:
PUG00001_fff81.signed.pdf
inv_40_0263_XXXX_XX_0000000000_0999837355_XXXXXX.pdf
inv_40_0263_XXXX_XX_0000000000_0999837356_XXXXXX.pdf

In Excel ist nach dem Sort die Reihenfolge aber:
inv_40_0263_XXXX_XX_0000000000_0999837355_XXXXXX.pdf
inv_40_0263_XXXX_XX_0000000000_0999837356_XXXXXX.pdf
PUG00001_fff81.signed.pdf

Gibt es eine bessere Lösung als das Recordset in einem zwischenschritt in eine excel tabelle zu pasten, per Excel zu sortieren und dann wieder in einen Array zu laden?

Danke

AW: "Sverweis" / "vlookup" mit ADODB : Sortierung

04.07.2024 10:56:41

peter

Hallo

JA



rs.Source = "SELECT FileName, Archive, Folder FROM [archivecontents.txt] Order by Ucase(FileName)"

Peter

Beliebteste Forumthreads (12 Monate)

Forumthread: "Sverweis" / "vlookup" mit ADODB

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread