Auswertung eines txt-Dateiinhaltes mit Excel
OlliMuc (Level 1) - Jetzt verbinden
22.03.2016, aktualisiert 11:19 Uhr, 1652 Aufrufe, 19 Kommentare, 1 Danke
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
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
19 Antworten
- LÖSUNG colinardo schreibt am 22.03.2016 um 11:42:09 Uhr
- LÖSUNG OlliMuc schreibt am 22.03.2016 um 12:36:48 Uhr
- LÖSUNG colinardo schreibt am 22.03.2016 um 12:40:17 Uhr
- LÖSUNG OlliMuc schreibt am 22.03.2016 um 13:55:45 Uhr
- LÖSUNG colinardo schreibt am 22.03.2016 um 14:02:35 Uhr
- LÖSUNG OlliMuc schreibt am 22.03.2016 um 14:26:50 Uhr
- LÖSUNG OlliMuc schreibt am 22.03.2016 um 14:40:31 Uhr
- LÖSUNG colinardo schreibt am 22.03.2016 um 15:57:57 Uhr
- LÖSUNG OlliMuc schreibt am 23.03.2016 um 07:51:03 Uhr
- LÖSUNG OlliMuc schreibt am 23.03.2016 um 07:52:05 Uhr
- LÖSUNG colinardo schreibt am 23.03.2016 um 08:05:38 Uhr
- LÖSUNG OlliMuc schreibt am 23.03.2016 um 08:10:31 Uhr
- LÖSUNG colinardo schreibt am 23.03.2016 um 08:56:33 Uhr
- LÖSUNG OlliMuc schreibt am 23.03.2016 um 09:20:25 Uhr
- LÖSUNG OlliMuc schreibt am 23.03.2016 um 12:40:41 Uhr
- LÖSUNG colinardo schreibt am 23.03.2016 um 12:50:19 Uhr
- LÖSUNG OlliMuc schreibt am 23.03.2016 um 13:34:10 Uhr
- LÖSUNG colinardo schreibt am 23.03.2016 um 12:50:19 Uhr
- LÖSUNG colinardo schreibt am 23.03.2016 um 08:56:33 Uhr
- LÖSUNG OlliMuc schreibt am 23.03.2016 um 08:10:31 Uhr
- LÖSUNG OlliMuc schreibt am 23.03.2016 um 08:09:29 Uhr
- LÖSUNG colinardo schreibt am 23.03.2016 um 08:13:01 Uhr
- LÖSUNG colinardo schreibt am 23.03.2016 um 08:05:38 Uhr
- LÖSUNG colinardo schreibt am 22.03.2016 um 15:57:57 Uhr
- LÖSUNG OlliMuc schreibt am 22.03.2016 um 14:40:31 Uhr
- LÖSUNG OlliMuc schreibt am 22.03.2016 um 14:26:50 Uhr
- LÖSUNG colinardo schreibt am 22.03.2016 um 14:02:35 Uhr
- LÖSUNG OlliMuc schreibt am 22.03.2016 um 13:55:45 Uhr
- LÖSUNG colinardo schreibt am 22.03.2016 um 12:40:17 Uhr
- LÖSUNG OlliMuc schreibt am 22.03.2016 um 12:36:48 Uhr
LÖSUNG 22.03.2016, aktualisiert 23.03.2016
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).
Grüße Uwe
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
LÖSUNG 22.03.2016, aktualisiert um 12:40 Uhr
Hallo Uwe,
vielen Dank erstmal für deine Arbeit und deine Erläuterungen!
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?
vielen Dank erstmal für deine Arbeit und deine Erläuterungen!
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?
LÖSUNG 22.03.2016, aktualisiert um 14:06 Uhr
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.Im Abschnitt "Inhalt der Datei in Variable speichern" kommt ein Laufzeitfehler:
strContent = fso.OpenTextfile(file.Path, 1).ReadAll()
Sind das eventuell zu viele Dateien?
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.
LÖSUNG 22.03.2016 um 13:55 Uhr
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?
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?
LÖSUNG 22.03.2016, aktualisiert um 14:04 Uhr
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 !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()
' 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.
LÖSUNG 22.03.2016 um 14:26 Uhr
Oh mei .. stimmt, hab zu schnell gelesen. 
Hab aber noch immer keinen Erfolg - wie kann ich dir eine Beispieldatei zur Verfügung stellen?
Hab aber noch immer keinen Erfolg - wie kann ich dir eine Beispieldatei zur Verfügung stellen?
LÖSUNG 22.03.2016 um 14:40 Uhr
Hab ANSI - Zeichensatz und Dateityp UNIX
LÖSUNG 22.03.2016, aktualisiert um 15:58 Uhr
Auch damit hier null issues
Ich schick dir eine PM mit meiner Mail-Adresse.
Ich schick dir eine PM mit meiner Mail-Adresse.
LÖSUNG 23.03.2016 um 07:51 Uhr
Ist gesendet. 
LÖSUNG 23.03.2016 um 07:52 Uhr
Nochmal getestet - heut kam "Einlesen hinter Dateiende"..
LÖSUNG 23.03.2016, aktualisiert um 08:10 Uhr
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.
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.
LÖSUNG 23.03.2016 um 08:09 Uhr
Letzte Ergänzung: Laufzeitfehler "62" steht noch dabei
LÖSUNG 23.03.2016 um 08:10 Uhr
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.
Gibt es dafür eine Lösung? Ich kann die leeren Dateien nicht aussortierne lassen, mangels Schreibrechten.
LÖSUNG 23.03.2016, aktualisiert um 08:13 Uhr
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.
LÖSUNG 23.03.2016 um 08:56 Uhr
Ist oben im Code eingebaut.
Grüße aus dem Flieger nach Madrid
Grüße aus dem Flieger nach Madrid
LÖSUNG 23.03.2016 um 09:20 Uhr
Läuft, alles super!
Uwe, vielen, vielen Dank - das waren deine guten Taten für 2 Tage
... und du hattest hoffentlich einen guten Flug, wenn du das hier liest.
Hab' da nun wirklich was gelernt...
Schließe die Frage hiermit.
Uwe, vielen, vielen Dank - das waren deine guten Taten für 2 Tage
Hab' da nun wirklich was gelernt...
Schließe die Frage hiermit.
LÖSUNG 23.03.2016 um 12:40 Uhr
Ä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?
LÖSUNG 23.03.2016, aktualisiert um 12:51 Uhr
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:Ä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?
If UCase(Left(file.Name, 16)) = "SUCCESS_" & Format(Date, "yyyymmdd") Then
setzt du stattdessen das hier ein DateAdd("d",-1,Date)
If UCase(Left(file.Name, 16)) = "SUCCESS_" & Format(DateAdd("d",-1,Date), "yyyymmdd") Then
Grüße Uwe
LÖSUNG 23.03.2016 um 13:34 Uhr
Läuft! 
Danke und vor allem Danke für den Link.
Hab eine schöne Zeit in Madrid - und pass auf dich auf.
Grüße Oliver
Danke und vor allem Danke für den Link.
Hab eine schöne Zeit in Madrid - und pass auf dich auf.
Grüße Oliver