Отримати дані з файлів XML за допомогою Power Query

Наша взаимовыгодная связь https://banwar.org/

»18. Май 2017 Дмитро 6513 переглядів

Формат 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 </ Сума> </ місяць> </ Продажі>

<? 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. Те, що йде після нього і є кодування файлу. В даному випадку це кодування тисячі двісті п'ятьдесят-одна для 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, тисячу двісті п'ятьдесят одна)
   де тисячі двісті п'ятьдесят одна - кодування файлу XML (як визначити кодування написано вище)
   Натиснути Ок, в кінець таблиці буде додано новий стовпець.
   По суті необов'язково запам'ятовувати цю формулу. Якщо виникли проблеми отримання даних з папки, то можна один раз отримати дані з одного файлу (вище в статті описано як це зробити) і в самому першому кроці запиту (як правило він називається Джерело) скопіювати рядок отримання даних. Залишиться тільки вставити її в якості формули в призначений для користувача стовпець і замість File.Contents ( "шлях до файлу") вставити [Content]
3. розкриваємо дані створеного стовпця за допомогою значка з двома розгорнутими стрілками:
   
4. Буде створено ще один стовпець (Table), який так само треба буде розкрити за допомогою значка з двома розгорнутими стрілками. Тут краще буде заздалегідь зняти галку з пункту Використовувати вихідне ім'я стовпця як префікс (Use original column name as prefix)
   
   Це потрібно для того, щоб стовпці з даними таблиць XML в результаті були названі як є, без додавання імені стовпця, з якого були розгорнуті.

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

До слова, подібний підхід можна використовувати і з файлами інших типів (txt, htm і т.д.), для яких запит отримання даних з папки не працює коректно.

Відео уроки

{ "Bottom bar": { "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1" 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, " textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; position: relative; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Пошук по мітках