Apr 28, 2019

4223

Excel VBA: .xml-Dateien aus einem Ordner auslesen und Zeilen mit Wert in Excel ausgeben

Guten Tag liebe Community!

Folgendes Problem habe ich, das ich gerne gelöst bekommen würde.
Ich habe mehrere Ordner ( in C:/Logs/2019/<Monat>/) in denen XML-Dateien über Testergebnisse gespeichert werden.
Es wird eine XML-Datei erstellt, egal ob der Test erfolgreich oder nicht erfolgreich war.

Die XML-Dateien sehen so aus: (Original Aufbau mit geänderten Variablennamen wegen Datenschutz)

<FILELOG>
<HEADER>
<SER>1234AXWBZT</SER>
<PART>F1_AXCD_994</PART>
<PARTN>PRODUKT</PARTN>
<NUM>11001100</NUM>
<EZ></EZ>
<A>-</A>
<Date>25.04.2019</Date>
<Time>08:50:43</Time>
<LNAME></LNAME>
<PATH></PATH>
<REMARK>Text</REMARK>
<USER>Username</USER>
<TYPE type="TYPE"></TYPE>  
<PPN>Device</PPN>
<PPV>SPS1</PPV>
<PPU></PPU>
</HEADER>

<LOG>
<STEP>
   <ID>
      <MainID>1</MainID>
      <SubID></SubID>
   </ID>
   <Time>08:50:43</Time>
   <TSTEP>Test1</TSTEP>
     <SSTEP>
      <MESS>
         <MESSN>TextStepInfo</MESSN>
         <LIML>Limit1</LIML>
         <MESSV>Measurement</MESSV>
         <LIMH>Limit2</LIMH>
         <MESSU>Unit</MESSU>
         <Result result="OK"></Result>  
      </MESS>
     </SSTEP>
   </STEP>

   <STEP>
   <ID>
      <MainID>2</MainID>
      <SubID></SubID>
   </ID>
   <Time>08:50:43</Time>
   <TSTEP>Test7</TSTEP>
     <SSTEP>
      <MESS>
         <MESSN>TextStepInfo</MESSN>
         <LIML>Limit1</LIML>
         <MESSV>Measurement</MESSV>
         <LIMH>Limit2</LIMH>
         <MESSU>Unit</MESSU>
         <Result result="OK"></Result>  
      </MESS>
     </SSTEP>
   </STEP>

   <STEP>
   <ID>
      <MainID>3</MainID>
      <SubID></SubID>
   </ID>
   <Time>08:52:28</Time>
   <TSTEP>Test72</TSTEP>
     <SSTEP>
      <MESS>
         <MESSN>TextStepInfo</MESSN>
         <LIML>Limit1</LIML>
         <MESSV>Measurement</MESSV>
         <LIMH>Limit2</LIMH>
         <MESSU>Unit</MESSU>
         <Result result="NOK"></Result>  
      </MESS>
     </SSTEP>
   </STEP>

   <STEP>
   <ID>
      <MainID>4</MainID>
      <SubID></SubID>
   </ID>
   <Time>08:50:43</Time>
   <TSTEP>Test99</TSTEP>
     <SSTEP>
      <MESS>
         <MESSN>TextStepInfo</MESSN>
         <LIML>Limit1</LIML>
         <MESSV>Measurement</MESSV>
         <LIMH>Limit2</LIMH>
         <MESSU>Unit</MESSU>
         <Result result="OK"></Result>  
      </MESS>
     </SSTEP>
   </STEP>

</LOG>

<SUM>
<OverallResult result="NOK"></OverallResult>  
</SUM>
</FILELOG>

Das OverallResult am Ende ist die Zusammenfassung ob der Test erfolgreich oder nicht erfolgreich war. (Es kann OK und NOK sein, abhängig ob eine der Messungen NOK war oder nicht)

Nun bräuchte ich nur aus dem Teil bei dem das Result "NOK" war den Wert aus <TSTEP> und alle Informationen die innerhalb dem <MESS> abgelegt wurden (also <MESSN>, <LIML>, <MESSV>, <LIMH>, <MESSU>, <Result>), sowie aus dem Header <SER>, <PART>, <PARTN> und <NUM>. Diese möchte ich in einer Excel-Tabelle ausgeben. Gleichzeitig soll in der Tabelle auch noch der Pfad zur Datei aus der die Daten kommen und das Datum an dem die Datei erstellt wurde erfasst werden.
Hier ist noch die Frage ob man mehrere NOK ausgeben kann, falls vorhanden? Ansonsten wäre auch nur das erste Vorkommen in Ordnung.

Am Ende stelle ich mir die Tabelle in Excel in etwa wie folgt vor:

Leider schaffe ich es mit meinen VBA Kenntnissen nicht zu einer funktionierenden Lösung. Bisher habe ich nur einen Code, der lediglich alle Pfade von den .xml Dateien aus den Ordnern auslesen kann, jedoch auch nach einigen Stunden Google-Suche habe ich noch keine funktionierende Lösung gefunden, wie ich die xml-Dateien auslesen kann und die geforderten Werte ausgeben lassen kann. Und bei der vielzahl an verschiedenen Ansätzen bin ich jetzt eigentlich noch verwirrter als zu Beginn :/

Ich wäre euch sehr dankbar, wenn ihr mir hier unter die Arme greifen könntet, gerne beantworte ich auch weitere Fragen, falls etwas unklar sein sollte.

Mit besten Grüßen
TheMysterion

Please also mark the comments that contributed to the solution of the article

Content-Key: 445825

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

Printed on: June 4, 2024 at 03:06 o'clock

7 Comments

Latest comment

Moin,

einen fertigen Code kann/ werde ich dir hier nicht liefern, aber folgende Links sollten dir sicherlich weiterhelfen:

Ermitteln, welches die letzte, leere Zeile in deinem Excelsheet ist:

XML auslesen und selektierte Attribute und bestimmte Zellen schreiben:
Hierzu musst du z.B. mit .SelectSingleNode(“/STEP/ID/MainID/text()”) den Wert auslesen und in deine Zielzelle schreiben

https://excel-macro.tutorialhorizon.com/vba-excel-read-data-from-xml-fil ...

Gruß
em-pie

Sowas würde ich gleich mit Powershell abfackeln:

$quelle = 'C:\Logs'  
$ziel = 'C:\ausgabe\feddich.csv'  

$log = gci $folder -Filter *.xml -Recurse | ?{!$_.PSIsContainer} | %{
    $file = $_
    $xml = [xml](gc $_.Fullname)
    $xml.SelectNodes('/FILELOG/LOG/STEP[SSTEP/MESS/Result[@result = "NOK"]]') | %{  
        [pscustomobject]@{
            Datum = get-date "$($xml.FILELOG.HEADER.Date) $($xml.FILELOG.HEADER.Time)"  
            Pfad = $file.Fullname
            Part = $xml.FILELOG.HEADER.PART
            PartN = $xml.FILELOG.HEADER.PARTN
            Num = $xml.FILELOG.HEADER.NUM
            Ser = $xml.FILELOG.HEADER.SER
            TStep = $_.TSTEP
            Result = $_.SSTEP.MESS.Result
            MESSN = $_.SSTEP.MESS.MESSN
            LIML = $_.SSTEP.MESS.LIML
            MESSV = $_.SSTEP.MESS.MESSV
            LIMH = $_.SSTEP.MESS.LIMH
            MESSU = $_.SSTEP.MESS.MESSU
        }
    }
}
if ($log){
    $log | export-csv $ziel -Delimiter ";" -NoType -Encoding UTF8  
}
start $ziel

Danke, leider funktioniert das Skript nicht.
Es scheint ein Proble mit dem "-File" in Zeile 4 Zeichen 48 zu geben.
Habe etwas recherchiert und mehrere Probleme gefunden... zum einen braucht man dafür Power Shell 3.0 und zum anderen muss man einen Registry Eintrag ändern um diese Scripte ausführen zu können. Beides ist mir nicht möglich.

zum einen braucht man dafür Power Shell 3.0

Kann ich dir probemlos auf 2.0 umschreiben sind nur ein paar Zeichen. Ist oben im Code schon angepasst

und zum anderen muss man einen Registry Eintrag ändern um diese Scripte ausführen zu können. Beides ist mir nicht möglich.

Nein musst du nicht. Dafür gibt es den Parameter -Executionpolicy ByPass dann läuft auch das out of the box.

leider funktioniert das Skript nicht.

Das Skript funktioniert einwandfrei! Wurde natürlich vorher gestestet! Also den Leuten hier keinen Bullshit erzählen wenn man in dem Bereich wenig Erfahrung besitzt.

Servus @139635 ,
als VBA auch kein großes Problem, Download des Beispiel-Sheets mit Demo-Daten fix und fertig nach deinen Vorgaben : filter_xml_445825.zip

Grüße Uwe

Woah Uwe!
Klasse! Genau das war, was ich mir erhofft hatte, viiiiiieeeeeelen DANK!!!!!

Könntest du mir noch einen kleinen Gefallen dazu tun? Ich wusste nicht, dass es relevant sein würde, aber es führt leider zu einer Fehlermeldung "Objektvariable oder With-Blockvariable nicht festgelegt".
Und zwar haben meine XML folgenden Anfang, den ich nicht ändern kann:

<?xml version="1.0" encoding="ISO-8859-1"?> 
<!-- XML File for #########                    -->
<!--  V1.0 ####### Version                              -->
<!DOCTYPE File SYSTEM "U.dtd"> 
<?xml-stylesheet type="text/xsl" href="FILE.xsl"?> 
<?xml-stylesheet type="text/css" href="FILE.css"?> 

Deine Testdaten sind utf-8 encoded, und vermutlich überprüfst du die Dateien von der 2. Zeile ab, denn sobald ich Testweise das ISO coding einfüge oder die Daten von oben wirft das Script die Fehlermeldung :/

Ansonsten wirklich perfekt umgesetzt!

Einzig eine weitere Funktion würde ich mir noch wünschen, wenn es nicht zu große Umstände macht: Wenn einmal Daten eingelesen wurden, kann man dem Script mitteilen, dass es beim nächsten Mal nicht wieder die gleichen Daten einlesen soll, sondern nur neue? Also praktisch ein Abgleich über die bereits eingetragenen Daten oder evtl kann man das so machen, dass nur Dateien mit neuerem Datum als das vom letzten einlesen eingefügt werden?

Darfst mir auch gerne nochmal so einen Link schicken, geb dir gerne noch ein paar Tassen Kaffee aus ;)

Beste Grüße
Sven

Hallo Sven.

Könntest du mir noch einen kleinen Gefallen dazu tun? Ich wusste nicht, dass es relevant sein würde, aber es führt leider zu einer Fehlermeldung "Objektvariable oder With-Blockvariable nicht festgelegt".

Die DTD führt dazu, dazu muss man vorher eine Property im Object setzen damit der Parser das erlaubt.

Wenn einmal Daten eingelesen wurden, kann man dem Script mitteilen, dass es beim nächsten Mal nicht wieder die gleichen Daten einlesen soll, sondern nur neue?

Kein Problem findest du beides hier drin: filter_xml_445825_2.zip

Viel Spaß
Grüße Uwe

German solved Question Microsoft Office Microsoft