Получить данные из файлов XML при помощи Power Query

Формат XML(eXtensible Markup Language) - специальный язык разметки, который приобрел в последние годы популярность за счет своей простоты и гибкости. Плюс он кроссплатформенный, что позволяет работать с такими файлами практически из любой программы. По сути это простой текстовый файл, но со своей разметкой, в которую можно заключить данные, структурировав их специальными тегами. Сам синтаксис XML очень похож на HTML, а это означает, что он очень прост в освоении. Но получать данные из таких файлов надо тоже чутка "пошаманив". В этой статье я покажу как получить данные из файлов XML при помощи надстройки Power Query. Если еще не работали с этой надстройкой и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе?

Что имеем: есть несколько файлов в формате XML с данными примерно следующего вида:

<?xml version="1.0" encoding="windows-1251" ?>
<Продажи>
	<Месяц номер="01">
		<Дата>01.01.2013</Дата>
		<Менеджер>Иванов В.В</Менеджер>
		<Сумма>1849941.38</Сумма>
	</Месяц>
	<Месяц номер="02">
		<Дата>01.02.2013</Дата>
		<Менеджер>Иванов В.В</Менеджер>
		<Сумма>1057377.21</Сумма>
	</Месяц>
	<Месяц номер="03">
		<Дата>01.03.2013</Дата>
		<Менеджер>Иванов В.В</Менеджер>
		<Сумма>2041079.96</Сумма>
	</Месяц>
	<Месяц номер="04">
		<Дата>01.04.2013</Дата>
		<Менеджер>Иванов В.В</Менеджер>
		<Сумма>1128102.22</Сумма>
	</Месяц>
	<Месяц номер="05">
		<Дата>01.05.2013</Дата>
		<Менеджер>Иванов В.В</Менеджер>
		<Сумма>991735.68</Сумма>
	</Месяц>
	<Месяц номер="06">
		<Дата>01.06.2013</Дата>
		<Менеджер>Иванов В.В</Менеджер>
		<Сумма>1502531.50</Сумма>
	</Месяц>
	<Месяц номер="07">
		<Дата>01.07.2013</Дата>
		<Менеджер>Иванов В.В</Менеджер>
		<Сумма>1904990.78</Сумма>
	</Месяц>
	<Месяц номер="08">
		<Дата>01.08.2013</Дата>
		<Менеджер>Иванов В.В</Менеджер>
		<Сумма>1370.86</Сумма>
	</Месяц>
	<Месяц номер="09">
		<Дата>01.09.2013</Дата>
		<Менеджер>Иванов В.В</Менеджер>
		<Сумма>1670190.78</Сумма>
	</Месяц>
	<Месяц номер="10">
		<Дата>01.10.2013</Дата>
		<Менеджер>Иванов В.В</Менеджер>
		<Сумма>1449435.21</Сумма>
	</Месяц>
	<Месяц номер="11">
		<Дата>01.11.2013</Дата>
		<Менеджер>Иванов В.В</Менеджер>
		<Сумма>1456267.16</Сумма>
	</Месяц>
	<Месяц номер="12">
		<Дата>01.12.2013</Дата>
		<Менеджер>Иванов В.В</Менеджер>
		<Сумма>1196971.27</Сумма>
	</Месяц>
</Продажи>

И необходимо записать данные из этих файлов в плоскую таблицу вида:

Для этого необходимо перейти на вкладку Данные(Data) или Power Query -группа Скачать и преобразовать(Get & Transform) -Получить данные(Get Data) -Из файла(From file) -Из XML(From XML)
В диалоговом окне выбираем нужный файл XML. Power Query постарается автоматически определить формат и кодировку и вывести окно выбора таблиц. Но получается это не всегда удачно и есть вероятность при работе с русскими кодировками получить сообщение:

в этом случае необходимо в окне с ошибкой нажать Изменить(Edit) и в поле Источник файла(File origin) выбрать нужную кодировку и нажать Ок:

Сразу возникает вопрос: а какая кодировка нужная?
Здесь два варианта:

1. можно открыть файл XML обычным блокнотом и обратить внимание на первую строку. Как правило она выглядит примерно так:
   <?xml version="1.0" encoding="windows-1251" ?>
   здесь нас интересует параметр encoding. То, что идет после него и есть кодировка файла. В данном случае это кодировка 1251 для Windows. Этого более чем достаточно для корректного получения содержимого.
2. методом подбора. Т.е. поочередно пробовать в окне с ошибкой в поле Источник файла(File origin) выбирать различные кодировки(начать советую с кириллических) до тех пор, пока не появится окно с разбором содержимого.

После того, как нужная кодировка подобрана появится окно содержимого файла:

В нем необходимо указать нужную таблицу(в примере на картинке выше это таблица Месяц). В окне правее будет отображено содержимое выбранной таблицы(если данных много - то несколько первых строк).
Если файл содержит более одной таблицы, то можно выбрать их все, предварительно установив флажок Несколько элементов и отметить нужные таблицы.
На этом основная работа по загрузке данных закончена. Если нажать Загрузить(Load), то выбранные таблицы будут выгружены на лист книги Excel, из которой был вызван импорт данных. Если нажать Правка(Edit), то будет открыт Редактор запросов, в котором можно будет сделать дополнительные преобразования данных: удалить лишние столбцы, заменить данные и т.п. Все зависит от ситуации.

Получение данных из всех файлов XML в папке
Однако часто необходимо получить данные сразу из нескольких файлов XML, а не только из одного. Power Query позволяет получить данные из всех файлов в указанной папке. Переходим на вкладку Данные(Data) или Power Query -группа Скачать и преобразовать(Get & Transform) -Получить данные(Get Data) -Из файла(From file) -Из папки(From folder)
В появившемся окне указываем папку, в которой находятся нужные файлы XML:

Нажимаем Ок - появится другое окно, в котором будут перечислены все файлы указанной папки. В этом окне надо нажать Изменить(Edit). Откроется окно редактора запросов. Здесь уже на свое усмотрение можно удалить лишние столбцы(как правило это столбцы с датой создания файлов, изменения и т.п.). Главное, надо оставить столбец Extension. В этом столбце необходимо установить текстовый фильтр -Равно,

и указать там ".xml".
Делается это для избежания ошибок, если вдруг в папку будут помещены файлы другого формата(отличного от XML)
Для начала советую попробовать нажать значок с двумя стрелочками на столбце Content:

в этом случае содержимое всех файлов либо будет получено и останется произвести необходимые действия в редакторе запросов(удалить лишние столбцы, заменить значения и т.п.). Либо получим ошибку - так же как в случае с одним файлом. Тогда надо будет так же в окне с ошибкой нажать Изменить(Edit) и указать нужную кодировку в поле Источник файла(File origin)(как определить кодировку написано выше).
Если же после раскрытия значка в столбце Content содержимое наполняется беспорядочным набором символов(вроде ZCHGASsadfajVHHghHHgjJhJJHgjTrTPukhUu), то необходимо удалить последний шаг(в поле примененные шаги) и проделать следующее:

1. вкладка Добавить столбец(Add Column) -Пользовательский столбец(Custom Column)
2. в появившемся окне указать имя столбца(я его назвал Данные XML), а в поле Пользовательская формула столбца(Custom column formula) записать следующую формулу:
   =Xml.Tables([Content], null, 1251)
   где 1251 - кодировка файла XML(как определить кодировку написано выше)
   Нажать Ок, в конец таблицы будет добавлен новый столбец.
   По сути необязательно запоминать эту формулу. Если возникли проблемы получения данных из папки, то можно один раз получить данные из одного файла(выше в статье описано как это сделать) и в самом первом шаге запроса(как правило он называется Источник) скопировать строку получения данных. Останется только вставить её в качестве формулы в пользовательский столбец и вместо File.Contents("путь к файлу") вставить [Content]
3. раскрываем данные созданного столбца при помощи значка с двумя развернутыми стрелками:
   
4. Будет создан еще один столбец(Table), который так же надо будет раскрыть при помощи значка с двумя развернутыми стрелками. Здесь лучше будет заранее снять галку с пункта Использовать исходное имя столбца как префикс(Use original column name as prefix)
   
   Это нужно для того, чтобы столбцы с данными таблиц XML в результате были названы как есть, без добавления имени столбца, из которого были развернуты.

По сути нужного результата добились. Остальные действия - это различного рода преобразования данных. Для загрузки данных на лист результата необходимо перейти на вкладку Главная(Home) -Закрыть и загрузить(Close & Load).
Если в папку будут добавлены/удалены файлы или информация в них будет изменена, то необходимо будет перейти на лист, на который выгружена результирующая таблица, выделить любую ячейку в ней, перейти на вкладку Запрос(Query) и нажать Обновить(Refresh). Так же это можно сделать с вкладки Данные(Data) -Обновить все(Refresh all). Но в этом случае будут обновлены все запросы и сводные таблицы, что не всегда нужно, особенно если запросов много.

К слову, подобный подход можно использовать и с файлами других типов(txt, htm и т.д.), для которых запрос получения данных из папки не работает корректно.