☰

使用 Excel Power Query 从 XML 文件返回多个值

tag-icon • tag-icon microsoft-excel xml power-query
使用 Excel Power Query 从 XML 文件返回多个值

我有大约 50 个结构相同的 XML 文件。我想从每个文件中提取多个值，这些值位于 XML 表的不同“行”中。

目前，我可以使用 Power Query 提取一个文件的 XML 表的一个“行”。但这对我来说不起作用，因为：

  1. 我需要对多个 XML 文件执行此查询（不是手动对每个文件执行）
  2. 我尝试提取的信息位于 XML 表的不同“行”中，因此如果我只拉入一行，则其他一些值将隐藏在该行的嵌套表中。

我可以使用 Power Query 来实现这一点吗？还是必须使用 VBA？是否有可用于此类项目的资源？

答案1

您正在寻找的魔法是Table.Combine(MyTable[ColumnOfTables])而不是MyTable{0}[ColumnOfTables]。

对于给定的 XML 文件（假设它们都是相同的结构），尝试构建查询以从一个文件中获取所需的所有内容。我建议不要单击表格单元格中的“表格”一词来导航。相反，手动输入每个步骤并命名所需的行，而不是假设它总是按相同的顺序排列。从查询开始

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

然后看看它是什么样子。在我使用的 XML 文件中，我看到一个包含“名称”和“表”列的表格。我只想要“名称”列值为“正文”的一行，所以我将查询更改为

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

出于某种我还不明白的原因，我看到的是一个类似的表，其中只有一行包含命名空间。很简单。重复相同的步骤。

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"

假设给了我一个表格，其中有一列名为“案例”，每个表格都包含一个包含“参与者”列的表格，而我想要的是所有案例参与者的列表

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

一旦您获得特定文件的类似内容，请复制查询，将其重命名为“fGetParticipants”（或任何对您有意义的名称），然后将前两行替换为以下内容：

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

该查询将返回一个以表行作为参数的函数，获取文件路径，然后执行与之前相同的操作。

假设您有或可以创建文件路径查询（从文件夹查询开始并添加新的“文件路径”列[Folder Name] & [Name]，或者其他任何方式，您只需要有一个包含名为“文件路径”的列的查询，其中包含您想要的文件路径），现在您可以进行如下查询

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"

我确信这不会满足您的所有需求。您提到有一些嵌套，但您没有提供太多细节，并且您的数据中可能存在一些上述内容未涉及的极端情况。但是，如果您想通过 PowerQuery 执行此操作，这应该可以帮助您继续前进。

答案2

转到获取数据 -> 从文件 -> 从文件夹并选择文件夹。单击打开，在弹出窗口中选择合并菜单按钮下的合并和转换数据。然后从左侧窗格中选择最后一个节点。

相关内容