Файл веб запроса для microsoft excel iqy

Обновлено: 04.07.2024

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Power Query работает очень хорошо, когда данные веб-сайта хранятся в таблицах. Иногда вы сможете импортировать данные из текстового файла. Однако, если данные не отформатированы с помощью тегов таблиц в HTML-коде, все становится довольно сложным, даже если у вас есть некоторые знания HTML.

Рис. 12.1. Диалоговое окно веб-аутентификации

В левой части диалогового окна есть несколько параметров, позволяющих выбрать несколько вариантов проверки подлинности. В данный момент остановитесь на варианте Анонимно. Вопрос, который вам задают здесь, заключается в том, хотите ли вы использовать эти учетные данные для конкретной страницы или корневого домена. Если у вас нет особых причин, сохраните установку по умолчанию – корневой домен. Нажмите Подключение.

Power Query проверяет страницу, ища таблицы. Откроется окно Навигатор. В нашем примере есть два варианта Document и Table 0. Кликните вторую опцию, и в окне появится предварительный просмотр таблицы:

Рис. 12.2. Предварительный просмотр Table 0 в Навигаторе; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Таблица выглядит довольно хорошо. Ваша цель – отфильтровать первые десять стран. Кликните Преобразовать данные. Переименуйте запрос Top 10 Population. Главная –> Сохранить строки –> Сохранить верхние строки –> 10 (рис. 12.3). Завершите импорт: Главная –> Закрыть и загрузить.

Рис. 12.3. ТОП-10 стран по населению

Подключение к размещенным в Интернете файлам

Рис. 12.4. Подключение к текстовому файлу в Интернете

Глядя на рис. 12.4, вы можете предположить, что заголовки столбцов не хранятся в текстовом файле (так же, как и даты оценки населения), а были предоставлены HTML веб-страницы. Это не страшно. Нажмите Преобразовать данные. Оставьте первые 10 строк, и переименуйте столбцы.

Подключение к страницам без таблиц

Рис. 12.5. Окно Инструменты разработчика

Хитрость заключается в том, чтобы последовательно разворачивая тэги, добраться до таблицы. В правом окне наводя курсор мыши на ту или иную строку кода html, в левом окне будут подсвечиваться различные элементы страницы. Когда подсветка покрывает таблицу, вы разворачиваете элемент, нажав на стрелку, которая указывает вправо. При этом стрелка поворачивается вниз, открывая дополнительные элементы, и вы продолжаете процесс, пока не найдете нужную вам таблицу.

Рис. 12.6. Окно Навигатор для выбора импорта из html

Нажмите Преобразовать данные. Откроется редактор Power Query (рис. 12.7). Если кликнуть в пустое пространство справа от слова Table (1), в нижней части экрана появится поле с перечислением детских элементов, относящихся к Table (2).

Рис. 12.7. Исходная таблица

Раскройте Table, кликнув на нее (3 на рис. 12.7). Одна таблица HTML теперь представлена двумя таблицами: HEAD и BODY

Рис. 12.8. Таблица HTML раскрылась в две: HEAD и BODY

Обратите внимание: имена таблиц в столбце Name соответствуют тэгам в браузере.

Рис. 12.9. Тэги в браузере

Теперь можно идти параллельно. Сначала смотреть в браузере, какой тэг подсвечивает таблицу, и раскрывать его, а затем переходить в Power Query и раскрывать соответствующую таблицу.

Рис. 12.10. Раскрыт тэг body в браузере и таблица BODY Power Query

Проблема в том, что в браузере все теги имеют имена, но в Power Query они не отражаются, поэтому очень легко запутаться. Кроме того, в области Примененные шаги маршрут не отслеживается, а с каждым новым раскрытием таблицы формула шага видоизменяется, не давая возможности создать резервную копию или вернуться на шаг назад. Так что, если вы пошли не по той дорожке, единственный выход – начать все сначала.

Если вы всё сделали верно, то доберетесь до нужной таблицы:

Рис. 12.11. Таблицы с численностью населения по странам в кодах html и в Power Query

Предостережения и разочарования, связанные с веб-опытом

Power Query отлично справляется с файлами и структурированными таблицами, размещенными в Интернете. А вот обработка тэгов, пожалуй, не является сильным местом Power Query (см. Извлечение данных с web-страниц с помощью кода на языке Python). Помните также, что дизайн страниц может меняться, и сложный хорошо разработанный запрос Power Query может из-за этого сломаться.

Сложность заключается в том, что эти данные раньше Минфином выкладывались в экселевском файле, теперь вот в таком постраничном режиме с пересщелкиванием. Возможно ли осуществить экспорт отображаемых (те данные, что расскрываются при нажатии на каждое поле НЕ НУЖНЫ, по большому счету) на странице данных в упорядоченную таблицу в excel (пускай даже и с повторам шапки, её можно будет потом вручную удалить).

При создании веб-запроса в файл с расширением ".iqy" просписываются следующие параметры запроса:

Selection=10
Formatting=All
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=True
DisableDateRecognition=False
DisableRedirections=False

Может быть здесь сделать значение "1" переменным или как-то задать диапозон просматриваемых страниц, чтобы запрос выполнялся со всех таблиц однотипных таблиц. Если создать много запросов, просто их получится слишком много. Помогите пожалуйста реализовать эту задачу или хотя бы скажите на Вашем сайте могу ли я найти ответ, т.к. я не смог найти подходящей конструкции для решения моего вопроса.

При создании веб-запроса в файл с расширением ".iqy" просписываются следующие параметры запроса:

Selection=10
Formatting=All
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=True
DisableDateRecognition=False
DisableRedirections=False

При создании веб-запроса в файл с расширением ".iqy" просписываются следующие параметры запроса:

Selection=10
Formatting=All
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=True
DisableDateRecognition=False
DisableRedirections=False

Рис. 1. Использование диалогового окна Создание веб-запроса для указания данных, которые следует импортировать; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Термин «веб-запрос» немного неточен, так как с его помощью можно получать данные не только из Сети. Можно также выполнять веб-запрос к HTML-файлу, расположенному на локальном компьютере, или к файлу, хранящемуся на сетевом сервере либо на веб-сервере в Интернете. Чтобы получить информацию с веб-сервера, необходимо подключиться к Интернету. Когда данные получены, соединение с Интернетом для работы с ними больше не нужно (конечно, если вы не собираетесь обновлять эти данные).

Создать веб-запрос, обеспечивающий получение информации, которую позже можно будет обновить одним щелчком мыши, можно следующим образом.

Результат импорта (рис. 2) не совсем то, чего мы хотели. Нас интересовала только таблица валют, а веб-запрос вернул страницу целиком. По умолчанию импортированные данные являются веб-запросом. Чтобы обновить информацию, щелкните правой кнопкой мыши на любой ячейке в импортированном диапазоне и в контекстном меню выполните команду Обновить.

Рис. 2. Информация, полученная с помощью веб-запроса

Если вы не собираетесь создавать обновляемый запрос, укажите это в шаге 5 приведенного выше порядка действий. В диалоговом окне Импорт данных нажмите кнопку Свойства и снимите флажок Сохранить определение запроса.

Встроенная в Excel возможность работы с веб-запросами позволяет находить в HTML-документе таблицы (обозначаемые HTML-тегом <TABLE>). Однако веб-дизайнеры для отображения табличной информации все чаще пользуются каскадными таблицами стилей (CSS). Как понятно из данного примера, Excel не распознает такие таблицы и не показывает желтого квадрата со стрелкой, поэтому получить таблицу вы не сможете — вам может потребоваться получить целый документ, а потом удалить (или скрыть) все данные, кроме интересующей вас таблицы.

Еще один способ отобразить данные с веб-страницы на рабочем листе напрямую открыть URL в Excel, воспользовавшись командой Файл –> Открыть. Просто введите URL полностью в поле Имя файла и нажмите Открыть (рис. 2). Результаты могут различаться в зависимости от макета веб-страницы. Как правило, итог удовлетворительный. Иногда вы получаете немного лишней информации. Обратите внимание: такие данные не обновляются. Если информация на веб-странице изменится, нужно будет закрыть книгу и снова воспользоваться командой Файл –> Открыть. Мне не удалось этого сделать. Excel сначала писал, что открывает страницу в режиме защищенного просмотра, долго работал, а потом выдал, что книга повреждена, и открыть ее нельзя((

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 194–196.

Цель книги – предоставить инструменты Excel для автоматизации повторяющихся задач извлечения данных из Интернета. Автор предлагает несколько десятков готового кода VBA и описывает приемы работы в Power Query.

Eduardo Sanchez. Excel and The World Wide Web. Straight to the Point. – Holy Macro! Books, 2021. – 58 p.

Глава 1. Приступая к работе

Что такое HTML?

HTML (Hyper Text Markup Language) – язык гипертекстовой разметки. Он используется для создания веб-сайтов. Гипертекст – это контент, который ведет себя нелинейным образом. Представьте себе веб-сайт, на каждой странице которого есть несколько ссылок на другие страницы, как того же самого сайта, так и других сайтов. Пользователь перемещается, переходя с одной страницы на другую; это гипертекстовое поведение. Обычная печатная книга – это контрпример, ее предполагается читать последовательно.

Информация в HTML помечена тегами; ниже мы поговорим об этом подробнее. Существуют и другие языки, используемые для создания веб-сайтов, такие как CSS и JavaScript, но мы не будем подробно рассматривать их здесь.

CSS (Cascading Style Sheet) – каскадная таблица стилей, язык описания внешнего вида документа. Он работает вместе с HTML, который отвечать за содержимое страницы. Каскадирование означает, что можно использовать несколько CSS-файлов для создания окончательного визуального стиля. Этот язык управляет такими элементами, как размер шрифта, фоновые изображения и цветовая палитра.

JavaScript – язык программирования для реализации динамического поведения на веб-сайтах. С его помощью разработчики могут манипулировать содержимым страницы, создавать диаграммы и взаимодействовать с API (Application Programming Interface, интерфейс прикладного программирования). Обратите внимание, что JavaScript и Java – это два разных языка. Говорят, что в будущем JavaScript может заменить VBA в качестве языка программирования Office.

Одна из замечательных особенностей современных браузеров заключается в том, что они предоставляют исходный код страниц. Если вы используете Google Chrome, просто щелкните правой кнопкой мыши любой элемент страницы и выберите пункт Просмотреть код; в правой части окна появится панель, аналогичная показанной ниже:

Рис. 1. Фрагмент кода HTML веб-страницы; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Ключевые слова header, div, aside, nav, h4, li и другие являются тегами.
Когда маленькие черные треугольники указывают вправо, это означает, что их можно щелкнуть, чтобы развернуть и отобразить дополнительную информацию.
Ключевые слова id, class и href являются атрибутами.
<li – это открывающий тег, а </li> – закрывающий.
Элемент может принадлежать к нескольким различным классам.

Если вы чувствуете себя подавленным всем этим, не паникуйте; не обязательно быть программистом HTML, чтобы работать с такого рода автоматизацией. Позже мы увидим, как читать код HTML и извлекать информацию с помощью VBA.

Хотя веб-дизайнеры используют профессиональные редакторы, можно создавать HTML-файлы в стандартных приложениях Windows, например, в Блокноте. Это особенно быть полезно, когда вы хотите протестировать код VBA, который будет взаимодействовать с веб-страницей, но по какой-то причине реальный сайт недоступен.

Читайте также: