Power Query Веб-запросы №13. Получить все URL с веб-страницы на лист Excel (Регулярные выражения)

Файлы к уроку:

Ссылки:

Описание

В этом уроке мы разбираем как получить все нужные URL с веб-страницы. Мы получим ссылки, которые ведут на страницы с таблицей, а потом все эти таблицы объединим по вертикали.

Решение

Здесь мы воспользуемся регулярными выражениями. О том как пользоваться регулярными выражениями в Power Query для Excel написано здесь и здесь.

Сначала нам нужно получить код веб-страницы с помощью Web.Contents. Далее из всего кода нужно вырезать только нужный кусок с помощью Text.BetweenDelimiters.

Потом этот код нужно передать функции Web.Page и с помощью регулярного выражения получить нужные URL.

Примененные функции

Text.Combine
Lines.FromBinary
Web.Contents
Text.BetweenDelimiters
Web.Page
Table.FromColumns
Text.Split
Table.TransformColumns
Table.AddColumn
Text.AfterDelimiter
RelativePosition.FromEnd
Table.ExpandTableColumn
Table.TransformColumnTypes
Table.Sort
Order.Ascending
Table.RenameColumns

Код

Регулярное выражение.

\/[0-9][0-9][0-9][0-9]-[0-9][0-9]

Получить HTML код.

let
   get_html = Text.Combine(
      Lines.FromBinary(
         Web.Contents("http://bigmacindex.ru/")
      )
   ), 
   section_tag = Text.BetweenDelimiters(
      get_html, 
      "<section class=""section", 
      "</section>"
   )
in
   section_tag

Код для получения ссылок, получения таблиц и объединения по вертикали.

let
   urls_str = Web.Page(
      "<script>
           var x='"
         & html_str
         & "';
           var y=new RegExp('"
         & regexp
         & "','g');
           var b=x.match(y);
           document.write(b);
         </script>"
   ){0}[Data]{0}[Children]{[Name = "BODY"]}[Children]{0}[
      Text
   ], 
   urls_col = Table.FromColumns(
      {Text.Split(urls_str, ",")}, 
      {"URL"}
   ), 
   get_urls = Table.TransformColumns(
      urls_col, 
      {{"URL", each "http://bigmacindex.ru" & _, type text}}
   ), 
   get_table = Table.AddColumn(
      get_urls, 
      "tab", 
      each Web.Page(Web.Contents([URL])){
         [ClassName = "table is-narrow"]
      }[Data]
   ), 
   col_transform = Table.TransformColumns(
      get_table, 
      {
         {
            "URL", 
            each Text.AfterDelimiter(
               _, 
               "/", 
               {0, RelativePosition.FromEnd}
            ), 
            type text
         }
      }
   ), 
   col_expand = Table.ExpandTableColumn(
      col_transform, 
      "tab", 
      {"Страна", "Местная валюта", "Рубли ↓↑"}, 
      {"Страна", "Местная валюта", "Рубли"}
   ), 
   cols_types = Table.TransformColumnTypes(
      col_expand, 
      {
         {"URL", type date}, 
         {"Местная валюта", type number}, 
         {"Рубли", type number}
      }, 
      "en_US"
   ), 
   tab_sort = Table.Sort(
      cols_types, 
      {
         {"URL", Order.Ascending}, 
         {"Страна", Order.Ascending}
      }
   ),
    col_rename = Table.RenameColumns(tab_sort,{{"URL", "Дата"}})
in
   col_rename

Этот урок входит в курс Веб-запросы в Power Query

Номер урока	Урок	Описание
1	Котировки Yahoo Finance	Вы хотите получить историю котировок акций MCD с 2000 года по текущий день с сайта Yahoo Finance. Вы копируете ссылку на раздел с историческими данными, вставляете ссылку в Power Query и получаете только 100 строк.
2	HTML, получение данных по облигациям	Мы хотим получить данные по всем облигациям со страницы bonds.finam.ru
3	Многостраничное извлечение	На разных страницах находятся сайта находятся котировки золота на каждый год. Нужно соединить все данные в одну таблицу.
4	JSON, Котировки Yahoo Finance 2	В этом уроке мы разберем еще один способ скачивания котировок с Yahoo Finance. Этот способ намного удобнее и быстрее. Здесь мы научимся обрабатывать формат JSON.
5	Котировки Московской биржи	В этом уроке мы научимся получать прямо в Excel котировки разных ценных бумаг с сайта Московской биржи. Московская биржа предоставляет возможность скачивать котировки и другую информацию по ценным бумагам при помощи специальных ссылок. Если мы введем такую ссылку в Power Query, создав запрос из интернета, то получим интересующие нас котировки прямо в Excel.
6	Неразмеченный текст	Вы делаете запрос к Web-странице и сталкиваетесь с неразмеченным текстом. Вы хотите при помощи Power Query этот неразмеченный текст преобразовать в нормальную красивую таблицу.
7	Текстовый документ, веб-страница, List.Zip	В этом запросе мы научимся получать нужную информацию из текста веб-страницы.
8	Youtube Data API	В этом уроке мы научимся получать данные из Youtube Data API. Узнаем как получить данные о Youtube канале: список видео и плейлистов, названия, описания, длительность, тэги, количество лайков, дизлайков, просмотров и комментариев.
9	Личный OneDrive — подключаемся напрямую к файлам и папкам	В этом уроке мы разберем как напрямую подключиться к файлам и папкам на личном OneDrive.
10	Подключение Google Spreadsheets, Google Drive (Excel.Workbook, Web.Contents)	В этом уроке мы научимся подключаться к xlsx файлам, которые находятся в вашем Google Drive.
11	Получить все станции метро	Получим таблицу с перечнем станций метрополитена даже с координатами широты и долготы.
12	Получить ссылки с веб-страницы (Html.Table)	Получить URL с веб-страницы. Сделать запрос к каждому URL, получить таблицы и объединить их всех по вертикали.
13	Получить все URL с веб-страницы	Получим нужные URL с веб-страницы в Excel. Сделать запрос к каждому URL, получить таблицы и объединить их по вертикали.
14	Ищем работу через API Head Hunter (hh.ru)	Получим таблицу с перечнем вакансий с сайте HH. Получим вакансии, в названии или описании которых встречаются слова SQL, Pandas, Power Query, Power Pivot, Power BI.
15	Запрос к XML Sitemap	Сделаем запрос к sitemap.xml, чтобы извлечь список всех веб-страниц сайта.

предыдущая запись

Power Query Веб-запросы №12. Получить ссылки с веб-страницы (Извлечь все URL, Html.Table)

следующая запись