Парсинг нетабличных данных с сайтов
Проблема с нетабличными данными
С загрузкой в 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 упростила жизнь, СПАСИБО ОГРОМНОЕ!!!
Такой вопрос
Как спарсить данные с сайта, в Google Excel без ед.значений (шт. рубли и тд.)
Как на данный момент происходит парсинг:
Но мне парсится строка в таком виде: 899,00 р.
Так же на странице сайта имеется:
Без использования power query.
Прошу помочь. Спасибо.
Как-то так.
спасибо!
Столкнулся с парсингом страниц, на которых есть закладки без очевидных URL-ссылок
Нужны табличные данные раздела "Translations".
Подключаться планирую функцией - страниц много. Меняться будет окончание ссылки "EC000001" - > EC000002 и т.д.
Но не могу вытащить данные из этого раздела.
PQ подключается к первому разделу Features.
Может кто подсказать путь решения?
Столкнулся с такой же неприятностью, PQ пропускает отдельные строки при парсинге страницы.
При просмотре HTML кода страницы они есть, а в импорте отсутствуют (фильтром не находятся).
Подскажите, пожалуйста, в чем может быть проблема?
У меня такая же проблема как у всех, Полностью повторил всю процедуру парсинга, но данных ссылок на эти метки нет. В коде страницы есть, но excell не вытаскивает их, такое ощущение что стоит блокировка какая то от парсинга, как обойти и как вытащить весь код, кто знает?
Т.е. не вводить ссылку в Визард, а сослаться на строку в таблице.
В выпадающием списке Открыть файл как (Open file as) вместо выбранной там по-умолчанию HTML-страницы выбираем Текстовый файл (Text file). Это заставит Power Query интерпретировать загружаемые данные не как веб-страницу, а как простой текст, т.е. Power Query не будет пытаться распознавать HTML-теги и их атрибуты, ссылки, картинки, таблицы, а просто обработает исходный код страницы как текст.
Попробуйте обновиться до последней, если это возможно.
= Table.FromColumns({Lines.FromBinary(Web.Contents("сайт"))})
Приходится, их искать и составлять таблицу вручную и подгружать актуальные данные в строки над расчётами
Проблема №2: Пропуск платежа и/или передача показаний счётчиков.
Формула для ввода показаний, например:
=ЕСЛИ(И(ЕТЕКСТ(C6);ЕЧИСЛО(C7));_Расх_ХВ;C7-ЕСЛИ(СМЕЩ(C7;-1;0)=0;СМЕЩ(C7;-2;0);СМЕЩ(C7;-1;0)))
где _Расх_ХВ -- средний показатель расхода холодной воды за прошлый год.
(c7) = Значение (ХВп, показание счётчика холодной воды) за декабрь прошлого года. (с18) = декабрь текущего года.
Наши УК-ашки норовят содрать с тебя по Нормативу потребления воды, если показания не передавались более трёх месяцев (квартала), по закону они обязаны пересчитать и вернуть деньги, если ты вернулся и передаёшь актуальные показания, но это они не делают, вернее не хотят делать.
Возможно у вас есть более лаконичная оптимальная формула.
Заранее благодарю Вас.
Ссылка на УК-ire --
... вот нашёл:
Тариф ещё надо выбирать
Вот уточнённая формула массива:
=ЕСЛИ(ЕЧИСЛО(F3);
F3 - ИНДЕКС(F:F; МАКС(ЕСЛИ((СТРОКА(F$1:F2)<СТРОКА(F3))*(ЕЧИСЛО(F$1:F2)); СТРОКА(F$1:F2))));
""
)
пока могу это сделать только вручную.
распознаю *.pdf и загружаю его как таблицу в Excel
Правительство Москвы, а изначально сайт mos.ru создавал я, даже не хочет озаботиться в прозрачности тарифов.