Office365 mehrere CSV zusammenführen mit VBA

[rAm8185]

Hallo Community,

folgende Situation: Via HTML-Suchformular werden die Daten von einen Scanserver als CSV Dateien abgespeichert. Die CSV Dateien liegen alle im selben Ordner (Die erste Zeile sind immer Überschriften. Trennzeichen sind Semikolons.). Bisher wird der Inhalt mittels markieren, kopieren und einfügen in eine große Excel Datei gespeichert und gefiltert (Datum nach Alter aufsteigend; Uhrzeit nach Größe absteigend) und Duplikate gelöscht. Die Datei hat am Ende meist über 30.000 Zeilen und ist über 9 MB groß. Auf diese Excel Datei wird später via SVERWEIS der Inhalt abgerufen.

Ziel: Die einzelnen CSV Dateien in einen Excel-Arbeitsblatt zusammenführen.
Optional: Das einbinden der Filter und löschen von Duplikaten (Spalte 1: PSN)
Wunschdenken: Das abrufen der Daten vom Scanserver automatisieren und zur Weiterverarbeitung an Excel übergeben.

Bisher versucht:
Die CSV Dateien wurden mit Excel' "Power Query" zusammengeführt und verarbeitet. Leider war die erstellte Tabelle daraus nicht via SVERWEIS zu verwenden. Ganz egal ob man die Daten unformatiert in Excel lud oder Daturm, Uhrzeit mäßig formatierte. Auch kommt es vor ,das im Scanserver fehlerhafte Handerfassungen drin sind. So steht dann einfach mal bei Uhrzeit eine PLZ drin, was dann zu Fehlermeldungen führte

Zusammenführen der CSV via Batch. Dafür sind es zu viele Zeilen. Das erstellen schlug nach ca 51% fehl.

Bisher erreicht: Die einzelnen CSV-Dateien werden jeweils in ein Blatt in einer Arbeitsmappe gespeichert. Das ist das Skript:

Sub Einlesen()
Dim strPfad$, csvName$, sFile$, i%
    strPfad = "C:\Excel Rohdaten"
    csvName = Dir$(strPfad & "/*.csv", vbNormal)
    Do Until LenB(csvName) = 0
        i = i + 1
        If i > Worksheets.Count Then Sheets.Add After:=Sheets(i - 1)
        With Sheets(i)
            .Activate
            sFile = strPfad & "\" & csvName
            .Name = Left(csvName, Len(csvName) - 4)
            With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & sFile, Destination:=.Range("A1"))
                .Name = csvName
                .FieldNames = True
                .AdjustColumnWidth = True
                .TextFileParseType = xlDelimited
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = False
                .TextFileSemicolonDelimiter = True
                .TextFileCommaDelimiter = False
                .TextFileSpaceDelimiter = False
                .TextFileOtherDelimiter = ""
                .Refresh BackgroundQuery:=False
            End With
        End With
        csvName = Dir$
    Loop
End Sub

So sieht eine gefilterte und von Duplikaten befreite Arbeitsmappe aus:

Die "Hyroglyphen" in Spalte A (PSN) sind nicht störend. Die Nummern die dahinter stecken, sind immer 14 stellig. Fangen aber sehr oft mit einer 0 an. In der Bearbeitungsleiste steht zwar nur eine 13 stellige Zahl, doch es sind 14 Ziffern. Damit hatte Power Query auch Probleme. So waren die Hyroglyphen der tatsächliche Zeileninhalt.

Kann mir einer bei den Skript helfen? Mir raucht ein wenig der Kopf von Excel und co.

Vielen lieben Dank schon mal vor ab.

 

[GunMum]

Moin,
ich denke, dass Power Query schon der richtige Weg ist. Und PQ hat übrigens seinen eigenen "SVERWEIS" (Ersatz/Funktionalität), allerdings leistungsfähiger. Und wenn der Scanner Fehler macht, was kann PQ dafür? Eventuell kannst du aber solche Zeilen korrigieren.

Ach ja, mit der Spalte_A hat gewiss nicht Power Query die Probleme, eher die Person, welche damit arbeitet.
Ich setze seitdem ich gut mit PQ umgehen kann nur noch 15-20% VBA ein und meine Kunden freuen sich, dass ich denen per TeamViewer oder sogar nur per Telefon weiter helfen kann ... (Die meisten Anwender sprechen kein VBA, was ich durchaus nachvollziehen kann).

 

[rAm8185]

Abend GunMum,

es freut mich das du PQ beherrscht und deinen Kunden dadurch leichter helfen kannst. Aber kannst du oder jemand mir bei dem Skript helfen?

 

[GunMum]

Moin,
dass ich Power Query beherrsche, das würde ich nicht unterschreiben. Aber ich kann gut damit umgehen und finde fast immer eine zufriedenstellende Lösung (soweit PQ mir überhaupt die Möglichkeit bietet). -PQ erleichtert durch die grafische Oberfläche vieles, entbindet aber niemanden, sich damit intensiv zu beschäftigen, wenn solide Ergebnisse herauskommen sollen. (Ist nicht böse gemeint.)

PQ-Tipp: Die Spalte PSN hat den falschen Datentyp. Vermutlich passierte das in der 2. Zeile des Protokolls im rechten Seitenfenster. Als Text lässt sich damit prima arbeiten.

... und btw: Mein Excel kann mit netten Bildchen nicht viel anfangen und mein Photoshop kann nicht kalkulieren .

 

[rAm8185]

PQ probiere ich morgen früh auf Arbeit nochmal. Ich habe hier zu Hause nicht solche CSV Dateien. Die Formatierung als Text hat das Problem, das es plötzlich diese Hyroglyphen waren. Das abändern auf "ganze Zahlen" oder Dezimalzahlen veränderte die 14 stellige Nummer die dahinter steckt.

 

[GunMum]

Also, was du als "Hyroglyphen" bezeichnest (was in Spalte_A im Bild sichtbar ist) sind Zahlen. Wenn auch in der sog. wissenschaftlichen Schreibweise. Wenn eine Zahl zu groß ist, wandelt Excel (und auch Power Query) diese in die entsprechend Notation um, daher diese Darstellung. Und da fliegen dann natürlich auch die führenden Nullen raus, da es ja Zahlen und keine Ziffernfolgen sind. Übrigens in A2 steht der Wert 9,44 * 10^12 (-> E=Exponential-Schreibweise)

Klick im rechten Seitenfenster auf den 2. Eintrag (Geänderter Typ) und ändere den Datentyp der Spalte PSN auf Text. Dann sind die führenden Nullen wieder da und du kannst sortieren, vergleichen, ...

 

[rAm8185]

Thread bitte schließen. Statt über VBA, bekommen ich nun eine große CSV Datei via Batch:

"del DB_komplett.csv
copy *.csv DB_komplett.csv"

Das reicht für meine Bedürfnisse.