Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (14)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (38)
Баги и глюки Excel (2)

Получить данные из файлов 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 постарается автоматически определить формат и кодировку и вывести окно выбора таблиц. Но получается это не всегда удачно и есть вероятность при работе с русскими кодировками получить сообщение:
Ошибка подключения к XML

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

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

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


Получение данных из всех файлов XML в папке
Однако часто необходимо получить данные сразу из нескольких файлов XML, а не только из одного. Power Query позволяет получить данные из всех файлов в указанной папке. Переходим на вкладку Данные(Data) или Power Query -группа Скачать и преобразовать(Get & Transform) -Получить данные(Get Data) -Из файла(From file) -Из папки(From folder)
В появившемся окне указываем папку, в которой находятся нужные файлы XML:
Подключение к папке Power Query
Нажимаем Ок - появится другое окно, в котором будут перечислены все файлы указанной папки. В этом окне надо нажать Изменить(Edit). Откроется окно редактора запросов. Здесь уже на свое усмотрение можно удалить лишние столбцы(как правило это столбцы с датой создания файлов, изменения и т.п.). Главное, надо оставить столбец Extension. В этом столбце необходимо установить текстовый фильтр -Равно,
Фильтр на тип файла Power Query
и указать там ".xml".
Делается это для избежания ошибок, если вдруг в папку будут помещены файлы другого формата(отличного от XML)
Для начала советую попробовать нажать значок с двумя стрелочками на столбце Content:
Развернуть Content Power Query
в этом случае содержимое всех файлов либо будет получено и останется произвести необходимые действия в редакторе запросов(удалить лишние столбцы, заменить значения и т.п.). Либо получим ошибку - так же как в случае с одним файлом. Тогда надо будет так же в окне с ошибкой нажать Изменить(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 и т.д.), для которых запрос получения данных из папки не работает корректно.


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Печать Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки
Обсуждение: есть 1 комментарий
  1. Андрей VG:


    Вот с html не всё так просто. XML стандарт требует обязательного закрытия тэгов, что соблюдается только для xhtml, а просто html допускает не соблюдение таких требований (например, тэги ).
    Так что проще использовать
    source = Web.Page(Web.Contents("d:\path\filename.htm"))

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2018 Excel для всех   Войти