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

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

С загрузкой в 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
А как оптимально действовать в случае если большое кол-во страниц?  
19.07.2021 12:29:52
Здравствуйте!
Существуют разновидности сайтов, в которых наполнение меняется динамически. Например, стоимость товара в зависимости от региона.
Адрес страницы, при изменении региона, не меняется.
Есть ли какая-то хитрость, которая поможет перейти к данным для конкретного региона?
Пример сайта
20.08.2021 10:12:57
НИКОЛАЙ, МЕГА БЛАГОДАРЕН!!!
как и выше был вопрос, подскажите пожалуйста, когда на сайте сотня страниц, может ли PQ по ним пройтись или нужно под каждую страницу отдельный запрос?
фишка с power bi  упростила жизнь, СПАСИБО ОГРОМНОЕ!!!  
05.10.2021 14:06:04
Добрый день. Подскажите, пожалуйста, как сделать, чтобы не из каталога загружать, а из результата поискового запроса? По такой схеме у меня не получилось. На том-же ВБ.
14.10.2021 10:51:09
Всё бы ниче, но у меня Power Query выводит «крякозябры» где кириллицы. Было круто, если бы и этот момент разбирали;)
20.10.2021 15:09:58
Всем доброго,
Такой вопрос

Как спарсить данные с сайта, в Google Excel без ед.значений (шт. рубли и тд.)

Как на данный момент происходит парсинг:
=importxml(O21;"//div[@id='price']") 
где О21 - ссылка на конкретную страницу
Но мне парсится строка в таком виде: 899,00 р.

Так же на странице сайта имеется:
<meta itemprop="price" content="899"> 
менял на meta и тд, не парсит такое вообще.

Без использования power query.

Прошу помочь. Спасибо.
01.11.2021 10:44:35
Иван, такие вопросы лучше на форум.
23.10.2021 09:53:57
Актуальный вопрос как быть с многостраничным доком, page1, page2 .... page134
01.11.2021 10:43:50
Создать запрос для загрузки одной страницы, затем преобразовать запрос в функцию и применить её ко всем остальным страницам.
Как-то так.
28.11.2021 11:42:13
Добрый день! Можно, пожалуйста, для "чайника" рассказать подробнее как преобразовать функцию в запрос, чтобы парсить большое количество страниц?
Yfa
26.01.2022 17:31:47
я бы тоже хотел знать!
спасибо!
22.03.2022 04:40:49
В общем случае для парсинга нужен полноценный браузер интегрированный в Excel. Например, как здесь. Парсить средствами Power Query это скорее профессиональный трюк, чем рабочий инструмент.
12.06.2022 15:21:03
Николай, добрый день! Пытаюсь повторить парсинг WB - не получается. Код страницы, загружаемой в PQ по ссылке отличается от кода страницы в браузере. Он короче и не содержит информацию о товарах вообще. В Power BI такая же история. Настроить с примерами тоже не получается. Что-то изменилось в коде на WB? Или я допускаю какие-то ошибки?
Наверх