ollimuc
Goto Top

Auswertung eines txt-Dateiinhaltes mit Excel

Hallo die Damen und Herren Spezialisten,

ich hoffe heute zum 3. Mal auf Eure geschätzte Hilfe...

Die gegebnene Fakten:

1. Ich habe mehrer txt-Dateien unterschiedlichen Datums in einem Ordner.
2. Die Dateinamen sind variabel - beginnen aber immer mit "SUCESS_" (Bsp: SUCCESS_20160322075527.txt).
3. Die Dateien enthalten Einträge (Zeilen) die immer mit den selben Buchstaben (MS) beginnen.

Was möchte ich:

Ich möchte (am liebsten) ein Makro in Excel, dass mir von allen Dateien mit aktuellem Datum in Spalte A den Dateinamen und daneben in Spalte B die Anzahl der in der Datein enthaltenen Zeilen die mit "MS" beginnen ausgibt - Bsp:

-------------------------- A ------------------------------------------------- B
SUCCESS_20160322075527.txt -------------------------- 12

Ich habe bereits Funktionen, die mir die Anzahl aktueller Dateien ermitteln - aber ich bekomme das nicht angepasst.

Dann habe ich folgendes, das mir aber jede Zeile zurückgibt (ich benötige aber nur die Anzahl):


Sub Anzahl_Verbuchte_ermitteln()

Dim sWord As String, sPath As String, sSearchPath As String, FileName As String, InputData
Dim AnzFound As Integer
Dim Anzahl As String
AnzFound = 0


'Wort nach dem gesucht werden soll

sWord = "MSCONS_"


'Suche nach allen Textdateien im Verzeichnis

sSearchPath = ""C:\Beispiel\SUCCESS_*.txt"
sPath = "C:\Beispiel\"
FileName = Dir(sSearchPath)
If FileName <> "" Then
Do While FileName <> ""
Open sPath & FileName For Input As #1
Do While Not EOF(1)
Line Input #1, InputData
If InStr(1, InputData, sWord) > 0 Then


'Zeile mit Suchwort gefunden
AnzFound = AnzFound + 1
Sheets("Verbuchung").Cells(AnzFound, 1) = FileName
Sheets("Verbuchung").Cells(AnzFound, 2) = InputData

End If
Loop
Close #1


'nächste Datei
FileName = Dir
Loop
End If
End Sub

Ich bin etwas verzweifelt und gespannt, ob meine Idee umsetzbar / anpassbar ist.

Gruß
Olli

Content-Key: 299757

Url: https://administrator.de/contentid/299757

Ausgedruckt am: 29.03.2024 um 02:03 Uhr

Mitglied: colinardo
colinardo 22.03.2016, aktualisiert am 23.03.2016 um 08:55:49 Uhr
Goto Top
Hallo Olli,
das gewünschte erreichst du mit folgendem Code.
Die Zeilen sind für dein Verständnis jeweils kommentiert.
Für das Zählen der Zeilen die mit MS beginnen benutze ich eine Regular-Expressions-Variante, die ist schneller als jede Zeile einer Datei mit einer FOR-Schleife durchlaufen zu müssen, zusätzlich hat man wesentlich mehr Möglichkeiten bestimmte Zeichenfolgen zu finden. Das ^ im Pattern bedeutet suche den Begriff nur am Anfang einer Zeile. Bitte beachte eventuell verwendete Sonderzeichen die in einem Regex-String besondere Bedeutungen haben (s. verlinktes Tutorial oben).
Sub Anzahl_Verbuchte_ermitteln()
    'Ordner in dem die Dateien liegen  
    Const FOLDER = "C:\Beispiel"  

    'Variablen  
    Dim fso as object, regex as object, rngOut as Range, strContent as String, matches as Object, file as Object
  
    'Objekte initialisieren  
    Set fso = CreateObject("Scripting.FileSystemObject")  
    Set regex = CreateObject("vbscript.regexp")  
    
    'Regular Expression Objekt-Eigenschaften festlegen  
    regex.Global = True: regex.IgnoreCase = False: regex.MultiLine = True
    'Regex Pattern zum suchen der Zeilen die mit MS beginnen festlegen  
    regex.Pattern = "^MS"  
    
    'Ausgabe erfolgt im aktuell aktiven Sheet  
    With ActiveSheet
        ' Überschriften setzen  
        .Range("A1:B1").Value = Array("Dateiname", "Anzahl")  
        .Range("A1:B1").Font.Bold = True  
        
        'nächste leere Anfangszelle in Spalte A ermitteln  
        Set rngOut = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)  
        ' Für jede Datei im Ordner ...  
        For Each file In fso.GetFolder(FOLDER).Files
            ' Wenn Dateiname SUCCESS_<AKTUELLES DATUM> entspricht  
            If UCase(Left(file.Name, 16)) = "SUCCESS_" & Format(Date, "yyyymmdd") Then  
                strContent = ""  
                On Error Resume Next
                'Inhalt der Datei in Variable speichern  
                strContent = fso.OpenTextfile(file.Path, 1, False, -2).ReadAll()
                'Anzahl der Zeilen die mit 'MS' beginnen zählen  
                Set matches = regex.Execute(strContent)
                'Dateiname zusammen mit der Anzahl in das Sheet schreiben  
                rngOut.Resize(1, 2).Value = Array(file.Name, matches.Count)
                'Ausgabebereich eine Zeile nach unten verschieben  
                Set rngOut = rngOut.Offset(1, 0)
            End If
        Next
    End With
End Sub
Grüße Uwe
Mitglied: OlliMuc
OlliMuc 22.03.2016 aktualisiert um 12:40:18 Uhr
Goto Top
Hallo Uwe,

vielen Dank erstmal für deine Arbeit und deine Erläuterungen! face-smile

Im Abschnitt "Inhalt der Datei in Variable speichern" kommt ein Laufzeitfehler:

strContent = fso.OpenTextfile(file.Path, 1).ReadAll()

Sind das eventuell zu viele Dateien? Stand jetzt liegen dort 16 Dateien - allerdings nur mit 39 KB insgesamt, an der Größe dürfte es wohl nicht scheitern :D

Einen Syntaxfehler kann ich, mangel Erfahrung, leider so schnell nicht sehen...

Ist es eventuell ein Schreibfehler bei "OpenTextfile" oder eine fehlende Definition?
Mitglied: colinardo
colinardo 22.03.2016 aktualisiert um 14:06:26 Uhr
Goto Top
Zitat von @OlliMuc:
Im Abschnitt "Inhalt der Datei in Variable speichern" kommt ein Laufzeitfehler:

strContent = fso.OpenTextfile(file.Path, 1).ReadAll()

Sind das eventuell zu viele Dateien?
Nein, das liegt an der Kodierung der Dateien, ob ASCII oder Unicode.

Nehme stattdessen mal diese Zeile:
strContent = fso.OpenTextfile(file.Path, 1, False, -2).ReadAll()

Der letzte Parameter legt die Kodierung deiner Dateien fest:
 -2 = Opens the file using the system default.
 -1 = Opens the file as Unicode.
0 = Opens the file as ASCII.

Mitglied: OlliMuc
OlliMuc 22.03.2016 um 13:55:45 Uhr
Goto Top
Habe getestet:

strContent = fso.OpenTextfile(file.Path, 1, False, -2).ReadAll()
strContent = fso.OpenTextfile(file.Path, 2, False, 0).ReadAll()
strContent = fso.OpenTextfile(file.Path, -2, False, 0).ReadAll()
strContent = fso.OpenTextfile(file.Path, 0, False, -2).ReadAll()


Ohne Erfolg...

Kannst du nochmal helfen? face-smile
Mitglied: colinardo
colinardo 22.03.2016 aktualisiert um 14:04:57 Uhr
Goto Top
Zitat von @OlliMuc:

Habe getestet:

strContent = fso.OpenTextfile(file.Path, 1, False, -2).ReadAll()
strContent = fso.OpenTextfile(file.Path, 2, False, 0).ReadAll()
strContent = fso.OpenTextfile(file.Path, -2, False, 0).ReadAll()
strContent = fso.OpenTextfile(file.Path, 0, False, -2).ReadAll()

Nein, nicht den zweiten Parameter ändern X) ich hatte ja extra geschrieben nur den letzten Parameter ändern !
' für Default  
strContent = fso.OpenTextfile(file.Path, 1, False, -2).ReadAll()
' oder für Unicode  
strContent = fso.OpenTextfile(file.Path, 1, False, -1).ReadAll()
' oder für ASCII  
strContent = fso.OpenTextfile(file.Path, 1, False, 0).ReadAll()

Funktioniert hier einwandfrei auch mit Unicode-Files. Ansonsten musst du mal eine deiner Dateien zum Download bereitstellen, vermutlich haben sie eine seltene oder unübliche Kodierung.
Mitglied: OlliMuc
OlliMuc 22.03.2016 um 14:26:50 Uhr
Goto Top
Oh mei .. stimmt, hab zu schnell gelesen. face-smile

Hab aber noch immer keinen Erfolg - wie kann ich dir eine Beispieldatei zur Verfügung stellen?
Mitglied: OlliMuc
OlliMuc 22.03.2016 um 14:40:31 Uhr
Goto Top
Hab ANSI - Zeichensatz und Dateityp UNIX
Mitglied: colinardo
colinardo 22.03.2016 aktualisiert um 15:58:26 Uhr
Goto Top
Zitat von @OlliMuc:

Hab ANSI - Zeichensatz und Dateityp UNIX
Auch damit hier null issues

Ich schick dir eine PM mit meiner Mail-Adresse.
Mitglied: OlliMuc
OlliMuc 23.03.2016 um 07:51:03 Uhr
Goto Top
Ist gesendet. face-smile
Mitglied: OlliMuc
OlliMuc 23.03.2016 um 07:52:05 Uhr
Goto Top
Nochmal getestet - heut kam "Einlesen hinter Dateiende"..
Mitglied: colinardo
colinardo 23.03.2016 aktualisiert um 08:10:17 Uhr
Goto Top
Zitat von @OlliMuc:

Nochmal getestet - heut kam "Einlesen hinter Dateiende"..
Das kann nur kommen wenn in deinem Ordner leere Dateien vorhanden sind. Ist das der Fall?


Es scheinen bei die einfach ein paar Files aus der Masse herauszufallen bei denen es Probleme gibt.

p.s. mit deinem File gibt es hier keinerlei Probleme bei der Auswertung. Kann ich dir gerne zeigen.
Mitglied: OlliMuc
OlliMuc 23.03.2016 um 08:09:29 Uhr
Goto Top
Letzte Ergänzung: Laufzeitfehler "62" steht noch dabei
Mitglied: OlliMuc
OlliMuc 23.03.2016 um 08:10:31 Uhr
Goto Top
Ja, es kann zu leeren Dateien kommen - dann wird das wohl der Fehler sein :/

Gibt es dafür eine Lösung? Ich kann die leeren Dateien nicht aussortierne lassen, mangels Schreibrechten.
Mitglied: colinardo
colinardo 23.03.2016 aktualisiert um 08:13:38 Uhr
Goto Top
Zitat von @OlliMuc:

Letzte Ergänzung: Laufzeitfehler "62" steht noch dabei

Wie gesagt, es scheinen bei dir einfach ein paar Files aus der Masse herauszufallen mit denen es Probleme gibt.

Ein On Error Resume Next im Kopf des Skripts kann diese überspringen, aber die Ursache bei deinen Dateien kann ich von hier aus leider nicht feststellen, sorry.
Mache das schon ewig damit und noch nie Probleme gehabt. Also liegt es an einigen eurer Files.
Mitglied: colinardo
colinardo 23.03.2016 um 08:56:33 Uhr
Goto Top
Ist oben im Code eingebaut.

Grüße aus dem Flieger nach Madrid face-wink
Mitglied: OlliMuc
OlliMuc 23.03.2016 um 09:20:25 Uhr
Goto Top
Läuft, alles super!

Uwe, vielen, vielen Dank - das waren deine guten Taten für 2 Tage face-wink ... und du hattest hoffentlich einen guten Flug, wenn du das hier liest.

Hab' da nun wirklich was gelernt...

Schließe die Frage hiermit.
Mitglied: OlliMuc
OlliMuc 23.03.2016 um 12:40:41 Uhr
Goto Top
Ähem .. eine Frage hätte ich doch noch .. wenn ich in einem zweiten Modul diesselbe Auswertung machen will - aber für Dateien die älter sind als heute? Muss ich dort ein eigenen Objekt definieren, dass ich mit "Date" abgleiche?
Mitglied: colinardo
colinardo 23.03.2016 aktualisiert um 12:51:05 Uhr
Goto Top
Zitat von @OlliMuc:

Ähem .. eine Frage hätte ich doch noch .. wenn ich in einem zweiten Modul diesselbe Auswertung machen will - aber für Dateien die älter sind als heute? Muss ich dort ein eigenen Objekt definieren, dass ich mit "Date" abgleiche?
In dieser Zeile:
If UCase(Left(file.Name, 16)) = "SUCCESS_" & Format(Date, "yyyymmdd") Then 
Kannst du das Date durch ein anderes Datums-Objectr ersetzen´, z.B. für den gestrigen Tag:
setzt du stattdessen das hier ein DateAdd("d",-1,Date)
If UCase(Left(file.Name, 16)) = "SUCCESS_" & Format(DateAdd("d",-1,Date), "yyyymmdd") Then 
Siehe http://www.techonthenet.com/excel/formulas/dateadd.php

Grüße Uwe
Mitglied: OlliMuc
OlliMuc 23.03.2016 um 13:34:10 Uhr
Goto Top
Läuft! face-smile

Danke und vor allem Danke für den Link.

Hab eine schöne Zeit in Madrid - und pass auf dich auf.

Grüße Oliver