Парсинг нетабличных данных с сайтов
Проблема с нетабличными данными
С загрузкой в Excel табличных данных из интернета проблем нет. Надстройка Power Query в Excel легко позволяет реализовать эту задачу буквально за секунды. Достаточно выбрать на вкладке Данные команду Из интернета (Data - From internet), вставить адрес нужной веб-страницы (например, ключевых показателей ЦБ) и нажать ОК:
Power Query автоматически распознает все имеющиеся на веб-странице таблицы и выведет их список в окне Навигатора:
Дальше останется выбрать нужную таблицу методом тыка и загрузить её в Power Query для дальнейшей обработки (кнопка Преобразовать данные) или сразу на лист Excel (кнопка Загрузить).
Если с нужного вам сайта данные грузятся по вышеописанному сценарию - считайте, что вам повезло.
К сожалению, сплошь и рядом встречаются сайты, где при попытке такой загрузки Power Query "не видит" таблиц с нужными данными, т.е. в окне Навигатора попросту нет этих Table 0,1,2... или же среди них нет таблицы с нужной нам информацией. Причин для этого может быть несколько, но чаще всего это происходит потому, что веб-дизайнер при создании таблицы использовал в HTML-коде страницы не стандартную конструкцию с тегом <TABLE>, а её аналог - вложенные друг в друга теги-контейнеры <DIV>. Это весьма распространённая техника при вёрстке веб-сайтов, но, к сожалению, Power Query пока не умеет распознавать такую разметку и загружать такие данные в Excel.
Тем не менее, есть способ обойти это ограничение ;)
В качестве тренировки, давайте попробуем загрузить цены и описания товаров с маркетплейса 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-код как раз и увидим (он может быть весьма объемным - не пугайтесь):
Ищем за что зацепиться
Теперь нужно понять на какие теги, атрибуты или метки в коде мы можем ориентироваться, чтобы извлечь из этой кучи текста нужные нам данные о товарах. Само-собой, тут всё зависит от конкретного сайта и веб-программиста, который его писал и вам придётся уже импровизировать.
В случае с Wildberries, промотав этот код вниз до товаров, можно легко нащупать простую логику:
- Строчки с ценами всегда содержат метку lower-price
- Строчки с названием бренда - всегда с меткой brand-name c-text-sm
- Название товара можно найти по метке goods-name c-text-sm
Иногда процесс поиска можно существенно упростить, если воспользоваться инструментами отладки кода, которые сейчас есть в любом современном браузере. Щёлкнув правой кнопкой мыши по любому элементу веб-страницы (например, цене или описанию товара) можно выбрать из контекстного меню команду Инспектировать (Inspect) и затем просматривать код в удобном окошке непосредственно рядом с содержимым сайта:
Фильтруем нужные данные
Теперь совершенно стандартным образом давайте отфильтруем в коде страницы нужные нам строки по обнаруженным меткам. Для этого выбираем в окне Power Query в фильтре [1] опцию Текстовые фильтры - Содержит (Text filters - Contains), переключаемся в режим Подробнее (Advanced) [2] и вводим наши критерии:
Добавление условий выполняется кнопкой со смешным названием Добавить предложение [3]. И не забудьте для всех условий выставить логическую связку Или (OR) вместо И (And) в выпадающих списках слева [4] - иначе фильтрация просто не сработает.
После нажатия на ОК на экране останутся только строки с нужной нам информацией:
Чистим мусор
Останется почистить всё это от мусора любым подходящим и удобным лично вам способом (их много). Например, так:
- Удалить заменой на пустоту начальный тег: <span class="price"> через команду Главная - Замена значений (Home - Replace values).
- Разделить получившийся столбец по первому разделителю ">" слева командой Главная - Разделить столбец - По разделителю (Home - Split column - By delimiter) и затем ещё раз разделить получившийся столбец по первому вхождению разделителя "<" слева, чтобы отделить полезные данные от тегов:
- Удалить лишние столбцы, а в оставшемся заменить стандартную HTML-конструкцию " на нормальные кавычки.
Разбираем блоки по столбцам
Если присмотреться, то информация о каждом отдельном товаре в получившемся списке сгруппирована в блоки по три ячейки. Само-собой, нам было бы гораздо удобнее работать с этой таблицей, если бы эти блоки превратились в отдельные столбцы: цена, бренд (издательство) и наименование.
Выполнить такое преобразование можно очень легко - с помощью, буквально, одной строчки кода на встроенном в Power Query языке М. Для этого щёлкаем по кнопке fx в строке формул (если у вас её не видно, то включите её на вкладке Просмотр (View)) и вводим следующую конструкцию:
= Table.FromRows(List.Split(#"Замененное значение1"[Column1.2.1],3))
Здесь функция List.Split разбивает столбец с именем Column1.2.1 из нашей таблицы с предыдущего шага #"Замененное значение1" на кусочки по 3 ячейки, а потом функция Table.FromRows конвертирует получившиеся вложенные списки обратно в таблицу - уже из трёх столбцов:
Ну, а дальше уже дело техники - настроить числовые форматы столбцов, переименовать их и разместить в нужном порядке. И выгрузить получившуюся красоту обратно на лист Excel командой Главная - Закрыть и загрузить (Home - Close & Load...)
Вот и все хитрости :)
Ссылки по теме
- Импорт курса биткойна с сайта через Power Query
- Парсинг текста регулярными выражениями (RegExp) в Power Query
- Параметризация путей к данным в Power Query
ЭТО БОМБА!! )))
Возможно ли парсеринг информации с такого сайта:
Пожалуйста подскажите каким образом.
поясню, есть интернет-магазин инструмента, есть категория товара, например аккум шуруповерты, внутри категории там много видов, брендов и типов самих шуруповертов (короче товара), но мне надо каким-то образом с корневой категории (аккум шуруповерты) стянуть данные, находящиеся в каждой отдельной странице товара, т.е. данные, описание, характеристик и цены, что-то типа этого...
есть ли такая возможность сделать с этим функционалом или это жуе более сложный скрипт в VBA???
и, вообще, возможно ли такое?
За ранее премного благодарен, статья шикарна, уже попробовал ))))
Спасибо Николаю за прекрасные видео и книги, написанные доступным языком. В данный момент активно разбираюсь в PQ.
Вдохновленный этим видео решил сделать небольшую таблицу, которая забирала бы данные с нескольких сайтов по актуальным ценам планшетов и выводила для принятия решения о покупке и отслеживания наиболее выгодных предложений.
Спарсить данные с Вайлдбериз не составило большого труда, благодаря видео и их открытости, а вот получить данные от МВидео стало проблемой, PQ их просто не видит, хотя Power BI их отлично распознает и выводит сразу в удобном виде. Поскольку перекинуть запрос из BI в Excel невозможно в домашней лицензии я просто скопировал запрос из одного PQ в другой. Вот тут и вылезла ошибка:
Power BI умеет делать команду Web.BrowserContents, а вот Excel ее не знает:-(
Буду благодарен за совет о парсинге данных с М.Видео или о обходе данной ошибки в Excel.
Существуют разновидности сайтов, в которых наполнение меняется динамически. Например, стоимость товара в зависимости от региона.
Адрес страницы, при изменении региона, не меняется.
Есть ли какая-то хитрость, которая поможет перейти к данным для конкретного региона?
как и выше был вопрос, подскажите пожалуйста, когда на сайте сотня страниц, может ли PQ по ним пройтись или нужно под каждую страницу отдельный запрос?
фишка с power bi упростила жизнь, СПАСИБО ОГРОМНОЕ!!!