☰

Verwenden Sie Excel Power Query, um mehrere Werte aus XML-Dateien zurückzugeben

tag-icon • tag-icon microsoft-excel xml power-query
Verwenden Sie Excel Power Query, um mehrere Werte aus XML-Dateien zurückzugeben

Ich habe etwa 50 XML-Dateien mit gleicher Struktur. Aus jeder dieser Dateien möchte ich mehrere Werte extrahieren, wobei sich die Werte in unterschiedlichen „Zeilen“ der XML-Tabelle befinden.

Momentan kann ich Power Query verwenden, um eine „Zeile“ der XML-Tabelle für eine Datei abzurufen. Das funktioniert bei mir nicht, weil:

  1. Ich muss diese Abfrage für mehrere XML-Dateien durchführen (nicht für jede einzelne manuell).
  2. Die Informationen, die ich zu extrahieren versuche, befinden sich in unterschiedlichen „Zeilen“ der XML-Tabelle. Wenn ich also nur eine Zeile hineinziehe, sind einige der anderen Werte in einer verschachtelten Tabelle in dieser Zeile verborgen.

Kann ich hierfür Power Query verwenden oder muss ich VBA verwenden? Gibt es für diese Art von Projekt Ressourcen?

Antwort1

Die Magie, nach der Sie suchen, ist Table.Combine(MyTable[ColumnOfTables])anstelle von MyTable{0}[ColumnOfTables].

Versuchen Sie, für eine bestimmte XML-Datei (vorausgesetzt, sie haben alle dieselbe Struktur) eine Abfrage zu erstellen, um alles, was Sie benötigen, aus nur einer Datei abzurufen. Ich empfehle, nicht auf das Wort „Tabelle“ in einer Tabellenzelle zu klicken, um zu navigieren. Geben Sie stattdessen jeden Schritt von Hand ein und benennen Sie die gewünschte Zeile, anstatt davon auszugehen, dass sie immer in derselben Reihenfolge sein wird. Beginnen Sie mit einer Abfrage von

let
    Source = Xml.Tables(File.Contents("C:\Temp\YourFile.xml"))
in
    Source

Dann sehen Sie, wie das aussieht. In der XML-Datei, mit der ich gespielt habe, sah ich eine Tabelle mit den Spalten „Name“ und „Tabelle“. Ich wollte nur die eine Zeile, in der der Spaltenwert „Name“ „Text“ war, also änderte ich meine Abfrage in

let
    Source = Xml.Tables(File.Contents("C:\Temp\YourFile.xml")),
    Body = Source{[Name="Body"]}[Table]
in
    Body

Aus einem Grund, den ich noch nicht verstehe, habe ich eine ähnliche Tabelle mit einer einzigen Zeile gesehen, die den Namespace enthält. Ganz einfach. Wiederholen Sie den gleichen Schritt.

let
    Source = Xml.Tables(File.Contents("C:\Temp\YourFile.xml")),
    Body = Source{[Name="Body"]}[Table],
    #"namespace url here" = Body{[Name="namespace url here"]}[Table]
in
    #"namespace url here"

Nehmen wir an, ich habe eine Tabelle mit einer Spalte namens „Fälle“, die jeweils eine Tabelle mit einer Spalte „Teilnehmer“ enthält, und ich möchte eine Liste aller Teilnehmer der Fälle.

let
    Source = Xml.Tables(File.Contents("C:\Temp\YourFile.xml")),
    Body = Source{[Name="Body"]}[Table],
    #"namespace url here" = Body{[Name="namespace url here"]}[Table],
    Cases = Table.Combine(#"namespace url here"[Cases]),
    Participants = Table.Combine(Cases[Participants])
in
    Participants

Wenn Sie so etwas für eine bestimmte Datei erhalten, duplizieren Sie die Abfrage, benennen Sie sie in „fGetParticipants“ (oder einen anderen für Sie sinnvollen Namen) um und ersetzen Sie die ersten beiden Zeilen durch Folgendes:

(record as record) as table =>
let
    FilePath = record[File Path],
    Source = Xml.Tables(File.Contents(FilePath)),

Diese Abfrage gibt eine Funktion zurück, die eine Tabellenzeile als Argument verwendet, den Dateipfad abruft und dann dasselbe wie zuvor tut.

Angenommen, Sie haben eine Abfrage von Dateipfaden oder können eine solche erstellen (entweder beginnen Sie mit einer Abfrage eines Ordners und fügen eine neue Spalte „Dateipfad“ hinzu [Folder Name] & [Name]oder was auch immer, Sie benötigen lediglich eine Abfrage, die eine Spalte namens „Dateipfad“ enthält, die die gewünschten Dateipfade enthält), dann können Sie jetzt eine Abfrage wie

let
    Source = #"Name of your query containing a column called File Path",
    #"Added Participants" = Table.AddColumn(Source, "Participants", fGetParticipants),
    // Not all had participants, so some of the files resulted in errors...
    #"Removed Errors" = Table.RemoveRowsWithErrors("#Added Participants", {"Participants"}),
    // Combine them all into one huge table
    #"All Participants" = Table.Combine(#"Removed Errors"[Participants])
in
    #"All Participants"

Ich bin sicher, dass Sie damit nicht alles bekommen, was Sie brauchen. Sie haben erwähnt, dass es einige Verschachtelungen gibt, Sie haben nicht viele Details angegeben und es gibt wahrscheinlich einige Randfälle in Ihren Daten, die oben nicht behandelt werden. Dies sollte Ihnen jedoch dabei helfen, weiterzukommen, wenn Sie dies über PowerQuery tun möchten.

Antwort2

Gehen Sie zu Daten abrufen -> Aus Datei -> Aus Ordner und wählen Sie den Ordner aus. Klicken Sie auf Öffnen und wählen Sie im Popup-Fenster unter der Menüschaltfläche Kombinieren die Option Daten kombinieren und transformieren. Wählen Sie anschließend den letzten Knoten im linken Bereich aus.

verwandte Informationen