☰

Используйте 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-файле, с которым я играл, я увидел таблицу со столбцами "Name" и "Table". Мне нужна была только одна строка, где значение столбца "Name" было "Body", поэтому я изменил свой запрос на

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

Перейдите в Get Data -> From File -> From Folder и выберите папку. Нажмите Open и во всплывающем окне выберите Combine & Transform Data под кнопкой меню Combine. После этого выберите последний узел на левой панели.

Связанный контент