Парсинг нетабличных данных с сайтов

Проблема с нетабличными данными

С загрузкой в Excel табличных данных из интернета проблем нет. Надстройка Power Query в Excel легко позволяет реализовать эту задачу буквально за секунды. Достаточно выбрать на вкладке Данные команду Из интернета (Data - From internet), вставить адрес нужной веб-страницы (например, ключевых показателей ЦБ) и нажать ОК:

Импорт данных с веб-страницы через Power Query

Power Query автоматически распознает все имеющиеся на веб-странице таблицы и выведет их список в окне Навигатора:

Выбираем таблицу на сайте для импорта

Дальше останется выбрать нужную таблицу методом тыка и загрузить её в Power Query для дальнейшей обработки (кнопка Преобразовать данные) или сразу на лист Excel (кнопка Загрузить).

Если с нужного вам сайта данные грузятся по вышеописанному сценарию - считайте, что вам повезло.

К сожалению, сплошь и рядом встречаются сайты, где при попытке такой загрузки Power Query "не видит" таблиц с нужными данными, т.е. в окне Навигатора попросту нет этих Table 0,1,2... или же среди них нет таблицы с нужной нам информацией. Причин для этого может быть несколько, но чаще всего это происходит потому, что веб-дизайнер при создании таблицы использовал в HTML-коде страницы не стандартную конструкцию с тегом <TABLE>, а её аналог - вложенные друг в друга теги-контейнеры <DIV>. Это весьма распространённая техника при вёрстке веб-сайтов, но, к сожалению, Power Query пока не умеет распознавать такую разметку и загружать такие данные в Excel.

Тем не менее, есть способ обойти это ограничение ;)

В качестве тренировки, давайте попробуем загрузить цены и описания товаров с маркетплейса Wildberries - например, книг из раздела Детективы:

Детективы на Wildberries

Загружаем HTML-код вместо веб-страницы

Сначала используем всё тот же подход - выбираем команду Из интернета на вкладке Данные (Data - From internet) и вводим адрес нужной нам страницы:

https://www.wildberries.ru/catalog/knigi/hudozhestvennaya-literatura/detektivy

После нажатия на ОК появится окно Навигатора, где мы уже не увидим никаких полезных таблиц, кроме непонятной Document:

Навигатор без таблиц

Дальше начинается самое интересное. Жмём на кнопку Преобразовать данные (Transform Data), чтобы всё-таки загрузить содержимое таблицы Document в редактор запросов Power Query. В открывшемся окне удаляем шаг Навигация (Navigation) красным крестом:

Удаляем ненужный шаг Навигация

... и затем щёлкаем по значку шестерёнки справа от шага Источник (Source), чтобы открыть его параметры:

Меняем тип файла

В выпадающием списке Открыть файл как (Open file as) вместо выбранной там по-умолчанию HTML-страницы выбираем Текстовый файл (Text file). Это заставит Power Query интерпретировать загружаемые данные не как веб-страницу, а как простой текст, т.е. Power Query не будет пытаться распознавать HTML-теги и их атрибуты, ссылки, картинки, таблицы, а просто обработает исходный код страницы как текст.

После нажатия на ОК мы этот HTML-код как раз и увидим (он может быть весьма объемным - не пугайтесь):

Исходный код страницы в Power Query

Ищем за что зацепиться

Теперь нужно понять на какие теги, атрибуты или метки в коде мы можем ориентироваться, чтобы извлечь из этой кучи текста нужные нам данные о товарах. Само-собой, тут всё зависит от конкретного сайта и веб-программиста, который его писал и вам придётся уже импровизировать.

В случае с Wildberries, промотав этот код вниз до товаров, можно легко нащупать простую логику:

Изучаем исходный код

  • Строчки с ценами всегда содержат метку lower-price
  • Строчки с названием бренда - всегда с меткой brand-name c-text-sm
  • Название товара можно найти по метке goods-name c-text-sm

Иногда процесс поиска можно существенно упростить, если воспользоваться инструментами отладки кода, которые сейчас есть в любом современном браузере. Щёлкнув правой кнопкой мыши по любому элементу веб-страницы (например, цене или описанию товара) можно выбрать из контекстного меню команду Инспектировать (Inspect) и затем просматривать код в удобном окошке непосредственно рядом с содержимым сайта:

Инспектирование кода HTML на веб-странице

Фильтруем нужные данные

Теперь совершенно стандартным образом давайте отфильтруем в коде страницы нужные нам строки по обнаруженным меткам. Для этого выбираем в окне Power Query в фильтре [1] опцию Текстовые фильтры - Содержит (Text filters - Contains), переключаемся в режим Подробнее (Advanced) [2] и вводим наши критерии:

Фильтруем нужные строки

Добавление условий выполняется кнопкой со смешным названием Добавить предложение [3]. И не забудьте для всех условий выставить логическую связку Или (OR) вместо И (And) в выпадающих списках слева [4] - иначе фильтрация просто не сработает.

После нажатия на ОК на экране останутся только строки с нужной нам информацией:

Отобранные строки

Чистим мусор

Останется почистить всё это от мусора любым подходящим и удобным лично вам способом (их много). Например, так:

  1. Удалить заменой на пустоту начальный тег: <span class="price"> через команду Главная - Замена значений (Home - Replace values).
  2. Разделить получившийся столбец по первому разделителю ">" слева командой Главная - Разделить столбец - По разделителю (Home - Split column - By delimiter) и затем ещё раз разделить получившийся столбец по первому вхождению разделителя "<" слева, чтобы отделить полезные данные от тегов:

    Отделяем данные от HTML-тегов

  3. Удалить лишние столбцы, а в оставшемся заменить стандартную HTML-конструкцию &quot; на нормальные кавычки.
В итоге получим наши данные в уже гораздо более презентабельном виде:

Зачищенные данные

Разбираем блоки по столбцам

Если присмотреться, то информация о каждом отдельном товаре в получившемся списке сгруппирована в блоки по три ячейки. Само-собой, нам было бы гораздо удобнее работать с этой таблицей, если бы эти блоки превратились в отдельные столбцы: цена, бренд (издательство) и наименование.

Выполнить такое преобразование можно очень легко - с помощью, буквально, одной строчки кода на встроенном в Power Query языке М. Для этого щёлкаем по кнопке fx в строке формул (если у вас её не видно, то включите её на вкладке Просмотр (View)) и вводим следующую конструкцию:

= Table.FromRows(List.Split(#"Замененное значение1"[Column1.2.1],3))

Здесь функция List.Split разбивает столбец с именем Column1.2.1 из нашей таблицы с предыдущего шага #"Замененное значение1" на кусочки по 3 ячейки, а потом функция Table.FromRows конвертирует получившиеся вложенные списки обратно в таблицу - уже из трёх столбцов:

Разобранная на 3 столбца таблица

Ну, а дальше уже дело техники - настроить числовые форматы столбцов, переименовать их и разместить в нужном порядке. И выгрузить получившуюся красоту обратно на лист Excel командой Главная - Закрыть и загрузить (Home - Close & Load...)

Загруженные в Excel данные с сайта

Вот и все хитрости :)

Ссылки по теме




19.01.2021 11:45:25
Здравствуйте Николай. урок полезный, только не отрабатывает функция List.Split  на Excel2016 (Ошибка выражения: Имя "List.Split" не распознано. Убедитесь в том, что оно написано верно.), как решить проблему?
21.01.2021 14:16:59
Отлично работает именно в 2016. Установите последние обновления.
15.06.2021 11:02:05
Обновил excel и все Ок
20.01.2021 21:19:22
Шикарно! Невероятная вещь эта Power Query
21.01.2021 04:32:20
спасибо! отличное обучающее видео ролик!
В конце видео вы рассказываете, что легче всего это сделать через Power BI, этот способ работает все хорошо. А как потом с Power BI выгрузить эту таблицу в ексель так, что бы таблица обновлялась?
27.01.2021 11:09:11
Если при парсинге в PBI вы использовали функции совместимые с функциями PQ Экселя, то просто копируете код из PBI и вставляете в пустой запрос в Экселе. Если использовался функционал недоступный в Экселе, то можно приобрести PRO лицензию PBI, опубликовать полученный после парсинга отчет и далее использовать функционал под названием analyze in excel
10.02.2021 13:20:37
Николай! с Выздоравлением!

ЭТО БОМБА!! )))
11.02.2021 16:38:50
Добрый день!

Возможно ли парсеринг информации с такого сайта:

https://agregatoreat.ru/organizations/card/e62b986c-c91c-4794-863b-ff7e05970e0d/customer

Пожалуйста подскажите каким образом.
24.02.2021 18:23:07
Добрый вечер, Николай. У меня более сложный вопрос, мне нужно парсинг нескольких страниц в одну таблицу...
поясню, есть интернет-магазин инструмента, есть категория товара, например аккум шуруповерты, внутри категории там много видов, брендов и типов самих шуруповертов (короче товара), но мне надо каким-то образом с корневой категории (аккум шуруповерты) стянуть данные, находящиеся в каждой отдельной странице товара, т.е. данные, описание, характеристик и цены, что-то типа этого...

есть ли такая возможность сделать с этим функционалом или это жуе более сложный скрипт в VBA???

и, вообще, возможно ли такое?

За ранее премного благодарен, статья шикарна, уже попробовал ))))
11.03.2021 15:17:50
Здравствуйте!
Спасибо Николаю за прекрасные видео и книги, написанные доступным языком. В данный момент активно разбираюсь в PQ.
Вдохновленный этим видео решил сделать небольшую таблицу, которая забирала бы данные с нескольких сайтов по актуальным ценам планшетов и выводила для принятия решения о покупке и отслеживания наиболее выгодных предложений.
Спарсить данные с Вайлдбериз не составило большого труда, благодаря видео и их открытости, а вот получить данные от МВидео стало проблемой, PQ их просто не видит, хотя Power BI их отлично распознает и выводит сразу в удобном виде. Поскольку перекинуть запрос из BI в Excel невозможно в домашней лицензии я просто скопировал запрос из одного PQ в другой. Вот тут и вылезла ошибка:

Power BI умеет делать команду Web.BrowserContents, а вот Excel ее не знает:-(

Буду благодарен за совет о парсинге данных с М.Видео или о обходе данной ошибки в Excel.
15.06.2021 11:56:26
А как оптимально действовать в случае если большое кол-во страниц?  
Наверх