Глава 12. Импорт веб-данных в Power Query

Рубрика: 7. Полезняшки Excel

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

Power Query работает очень хорошо, когда данные веб-сайта хранятся в таблицах. Иногда вы сможете импортировать данные из текстового файла. Однако, если данные не отформатированы с помощью тегов таблиц в HTML-коде, все становится довольно сложным, даже если у вас есть некоторые знания HTML.

В качестве примера попытается выяснить, в каких странах лучше всего продавать ваш товар. Вы предполагаете, что это ТОП-10 стран с самым большим населением. Вы нашли нужные данные на веб-сайте ЦРУ. Создайте новую книгу Excel, пройдите по меню Данные –> Из интернета. При запросе URL-адреса введите: https://www.cia.gov/library/publications/the-world-factbook/rankorder/2119rank.html. Нажмите Ok. Вам будет предложено ввести параметры проверки подлинности:

Ris. 12.1. Dialogovoe okno veb autentifikatsii

Рис. 12.1. Диалоговое окно веб-аутентификации

Скачать заметку в формате Word или pdf

В левой части диалогового окна есть несколько параметров, позволяющих выбрать несколько вариантов проверки подлинности. В данный момент остановитесь на варианте Анонимно. Вопрос, который вам задают здесь, заключается в том, хотите ли вы использовать эти учетные данные для конкретной страницы или корневого домена. Если у вас нет особых причин, сохраните установку по умолчанию – корневой домен. Нажмите Подключение.

Power Query проверяет страницу, ища таблицы. Откроется окно Навигатор. В нашем примере есть два варианта Document и Table 0. Кликните вторую опцию, и в окне появится предварительный просмотр таблицы:

Ris. 12.2. Predvaritelnyj prosmotr Table 0 v Navigatore

Рис. 12.2. Предварительный просмотр Table 0 в Навигаторе; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Таблица выглядит довольно хорошо. Ваша цель – отфильтровать первые десять стран. Кликните Преобразовать данные. Переименуйте запрос Top 10 Population. Главная –> Сохранить строки –> Сохранить верхние строки –> 10 (рис. 12.3). Завершите импорт: Главная –> Закрыть и загрузить.

Ris. 12.3. TOP 10 stran po naseleniyu

Рис. 12.3. ТОП-10 стран по населению

Подключение к размещенным в Интернете файлам

Если вы знаете URL-адрес текстового файла, вы можете подключиться непосредственно к нему. Создайте новую книгу Excel, пройдите по меню Данные –> Из интернета. Введите URL: https://www.cia.gov/library/publications/the-world-factbook/rankorder/rawdata_2119.txt. Нажмите Ok. Вы попадаете в окно предварительного просмотра Power Query, минуя шаг проверки подлинности веб-сайта. Причина в том, что во время выполнения предыдущего запроса вы указали, что хотите, чтобы параметры проверки подлинности применялись к корню веб-сайта. Если бы это было первое обращение к сайту, у вас запросили бы параметры проверки подлинности.

Ris. 12.4. Podklyuchenie k tekstovomu fajlu v Internete

Рис. 12.4. Подключение к текстовому файлу в Интернете

Глядя на рис. 12.4, вы можете предположить, что заголовки столбцов не хранятся в текстовом файле (так же, как и даты оценки населения), а были предоставлены HTML веб-страницы. Это не страшно. Нажмите Преобразовать данные. Оставьте первые 10 строк, и переименуйте столбцы.

Подключение к страницам без таблиц

Если веб-страница, к которой вы подключаетесь, не содержит тэгов таблиц в исходном коде HTML, задача усложняется. Откройте в веб-браузере страницу https://www.cia.gov/library/publications/the-world-factbook/rankorder/2119rank.html, откройте окно Инструменты разработчика (в Chrome можно нажать клавишу F12).

Ris. 12.5. Okno Instrumenty razrabotchika

Рис. 12.5. Окно Инструменты разработчика

Хитрость заключается в том, чтобы последовательно разворачивая тэги, добраться до таблицы. В правом окне наводя курсор мыши на ту или иную строку кода html, в левом окне будут подсвечиваться различные элементы страницы. Когда подсветка покрывает таблицу, вы разворачиваете элемент, нажав на стрелку, которая указывает вправо. При этом стрелка поворачивается вниз, открывая дополнительные элементы, и вы продолжаете процесс, пока не найдете нужную вам таблицу.

Теперь можно попробовать воспроизвести в запросе Power Query процесс поиска таблицы среди тэгов html. Создайте новый файл Excel. Пройдите по меню Данные –> Из интернета. Введите URL: https://www.cia.gov/library/publications/the-world-factbook/rankorder/2119rank.html. В окне Навигатор выберите Document:

Ris. 12.7. Okno Navigator dlya vybora importa iz html

Рис. 12.6. Окно Навигатор для выбора импорта из html

Нажмите Преобразовать данные. Откроется редактор Power Query (рис. 12.7). Если кликнуть в пустое пространство справа от слова Table (1), в нижней части экрана появится поле с перечислением детских элементов, относящихся к Table (2).

Ris. 12.8. Ishodnaya tablitsa

Рис. 12.7. Исходная таблица

Раскройте Table, кликнув на нее (3 на рис. 12.7). Одна таблица HTML теперь представлена двумя таблицами: HEAD и BODY

Ris. 12.9. Tablitsa HTML raskrylas v dve HEAD i BODY

Рис. 12.8. Таблица HTML раскрылась в две: HEAD и BODY

Обратите внимание: имена таблиц в столбце Name соответствуют тэгам в браузере.

Ris. 12.9. Tegi v brauzere

Рис. 12.9. Тэги в браузере

Теперь можно идти параллельно. Сначала смотреть в браузере, какой тэг подсвечивает таблицу, и раскрывать его, а затем переходить в Power Query и раскрывать соответствующую таблицу.

Ris. 12.10. Raskryt teg body v brauzere i Power Query

Рис. 12.10. Раскрыт тэг body в браузере и таблица BODY Power Query

Проблема в том, что в браузере все теги имеют имена, но в Power Query они не отражаются, поэтому очень легко запутаться. Кроме того, в области Примененные шаги маршрут не отслеживается, а с каждым новым раскрытием таблицы формула шага видоизменяется, не давая возможности создать резервную копию или вернуться на шаг назад. Так что, если вы пошли не по той дорожке, единственный выход – начать все сначала.

Если вы всё сделали верно, то доберетесь до нужной таблицы:

Ris. 12.11. Tablitsy s chislennostyu naseleniya po stranam v kodah html i Power Query

Рис. 12.11. Таблицы с численностью населения по странам в кодах html и в Power Query

Предостережения и разочарования, связанные с веб-опытом

Power Query отлично справляется с файлами и структурированными таблицами, размещенными в Интернете. А вот обработка тэгов, пожалуй, не является сильным местом Power Query (см. Извлечение данных с web-страниц с помощью кода на языке Python). Помните также, что дизайн страниц может меняться, и сложный хорошо разработанный запрос Power Query может из-за этого сломаться.

Комментарии: 4 комментария

Когда будет продолжение по импорту web без таблиц?

Артем, в книге я не нашел продолжения рассмотрения импорта web-страниц без таблиц. Возможно, авторы имели ввиду, что далее будут представлены методы, позволяющие вести такую обработку.

Спасибо! Очень кстати. Только на сайты, требующие идентификации, ни на один не получилось залогиниться.

Сергей, только что проверил еще раз. По первой ссылке получил доступ без проблем…


Прокомментировать