Bazaprogram.ru

Новости из мира ПК
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Импорт xml в excel vba

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

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

01.01.2013 Иванов В.В 1849941.38 01.02.2013 Иванов В.В 1057377.21 01.03.2013 Иванов В.В 2041079.96 01.04.2013 Иванов В.В 1128102.22 01.05.2013 Иванов В.В 991735.68 01.06.2013 Иванов В.В 1502531.50 01.07.2013 Иванов В.В 1904990.78 01.08.2013 Иванов В.В 1370.86 01.09.2013 Иванов В.В 1670190.78 01.10.2013 Иванов В.В 1449435.21 01.11.2013 Иванов В.В 1456267.16 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 обычным блокнотом и обратить внимание на первую строку. Как правило она выглядит примерно так:

    здесь нас интересует параметр 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 и т.д.), для которых запрос получения данных из папки не работает корректно.

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

Analyst Cave

Working with XML files in VBA (VBA XML)

XML files are one of the most common type of data files apart from text and CSV (comma-separated values) files. Reading data files which are not hierarchical (as XML files or JSON) is relatively easy. You can read in the data row by row and process columns separately. With XML (and JSON) the task is not as easy as the data is hierarchical (parent-child relationships exist between records in the schema) and the number of underlying nodes may vary as opposed to tabular data which usually has a constant number of columns separated with a specific delimiter.

Fortunately, we can use the MSXML2.DOMDocument object in VBA. Let’s however, as always, start with a short introduction as to how XML files a structure before we dive into the examples.

Loading XML document in VBA

The MSXML2.DOMDocument object allows you to easily traverse through an XML structure an extract any XML node and/or attribute needed. Let’s look at the example below.

Below we start by loading the XML document. Notice that I am selecting the load to be performed synchronously and not validation be carried out on parsing the document. Feel free to change these options if needed.

Alternatively load an XML from a string:

That’s it. You have loaded the XML document into memory into the DOMDocument object. The document has been parsed and you can easily traverse the enclosed elements. See next section.

XML DOM nodes in VBA

For the below I will use the following examples XML:

The XML document will provide you with the root of the entire DOM (of type XDoc.DocumentElement). Each DocumentElement (XML DOM node) facilitates the following node references:

Node ReferenceTypeDescription
parentNode[XDoc.DocumentElement]The parent node, one node higher in the DOM hierarchy
firstChild[XDoc.DocumentElement]The first child node, first node lower in the DOM hierarchy
lastChild[XDoc.DocumentElement]The last child node, last node lower in the DOM hierarchy
childNodes[Array of type XDoc.DocumentElement]All child nodes of the current node, all nodes lower in the DOM hierarchy
nextSibling[XDoc.DocumentElement]Next sibling node i.e. node on the same level in the DOM hierarchy, having the same parent node
previousSibling[XDoc.DocumentElement]Previous sibling node i.e. node on the same level in the DOM hierarchy, having the same parent node

All the above references allow you to free move within the XML DOM.

ChildNodes

Let’s start by extracting the first list and printing it’s XML and text contents. The basics to moving around the XML DOM is using ChildNodes.

This is the result

Traversing through the whole XML in VBA

Now that we got the basics let’s print out the whole contents of the XML DOM including the basenames (node names).

This is the result:

Easy right? Using the basics above we can easily move around the document. But this still seems like a lot of coding right? Well there is an easier way of moving / extracting items using the DOMDocument object – called XPath.

XML Document example node references

Now that we have a hang of our XML document, based on the example XML I provided above I mapped a reference to how to obtain various elements of our XML file by using node references:

  • DistributionLists[FirstChild]
    • List[ChildNodes(0)]
      • Name: Recon[ChildNodes(0).ChildNodes(0).innerText]
      • TO: John;Bob;Rob;Chris[ChildNodes(0).ChildNodes(1).innerText]
      • CC: Jane;Ashley
      • BCC: Brent
    • List[ChildNodes(1)]
      • Name: Performance Report[ChildNodes(1).ChildNodes(0).innerText]
      • TO: Huck;Ashley
      • CC: Tom;Andrew
      • BCC: John;Seema

XPath in VBA

Instead of traversing the elements/nodes in your XML using the .ChildNodes/.FirstChild/NextChild properties we can also use XPath. XPath is a query language used for selecting XML nodes in an XML document. It is represented by a single string. It allows you to extract any number of nodes (0 or more) which match the specified XPath query.

Читать еще:  Как разбить видео на 2 части
Ссылка на основную публикацию
Adblock
detector