![使用 Excel Power Query 从 XML 文件返回多个值](https://linux22.com/image/1618655/%E4%BD%BF%E7%94%A8%20Excel%20Power%20Query%20%E4%BB%8E%20XML%20%E6%96%87%E4%BB%B6%E8%BF%94%E5%9B%9E%E5%A4%9A%E4%B8%AA%E5%80%BC.png)
我有大约 50 个结构相同的 XML 文件。我想从每个文件中提取多个值,这些值位于 XML 表的不同“行”中。
目前,我可以使用 Power Query 提取一个文件的 XML 表的一个“行”。但这对我来说不起作用,因为:
- 我需要对多个 XML 文件执行此查询(不是手动对每个文件执行)
- 我尝试提取的信息位于 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
转到获取数据 -> 从文件 -> 从文件夹并选择文件夹。单击打开,在弹出窗口中选择合并菜单按钮下的合并和转换数据。然后从左侧窗格中选择最后一个节点。