Импорт данных в Excel 2010 из веба

Желаете употреблять часто обновляющиеся данные из веба? Мы покажем Для вас, как просто и стремительно настроить импорт данных из веба в Excel 2010, чтоб Ваша таблица была повсевременно в животрепещущем состоянии.

Как сделать таблицу, связанную с вебом?

Чтоб импортировать данные в таблицу Excel, изберите команду From Web (Из веба) в разделе Get External Data (Получение наружных данных) на вкладке Data (Данные).

В открывшемся диалоговом окне введите адресок веб-сайта, из которого требуется импортировать данные и нажмите Go (Запуск). Страничка будет загружена в это окно для предпросмотра, её можно пролистать и отыскать подходящую информацию

Перед каждой из web-таблиц имеется малая стрелочка, которая показывает, что эта таблица быть может импортирована в Excel. Кликните по ней, чтоб избрать данные для загрузки, а потом нажмите Import (Импорт).

Покажется сообщение Downloading (Загрузка) – это значит, что Excel импортирует данные с обозначенной web-страницы.

Изберите ячейку, в которой будут расположены данные из веба, и нажмите ОК.

В избранной ячейке покажется системное сообщение о том, что Excel импортирует данные.

Через некое время информация с избранной web-страницы покажется в таблице Excel. Может быть, в таблицу попадут некие излишние данные – их можно расслабленно удалить.

Импортированные данные Вы сможете употреблять буквально так же, как и всякую другую информацию в Excel. Их можно употреблять для построения графиков, спарклайнов, формул. Спарклайны – это новейший инструмент для работы с данными, показавшийся в Excel 2010. Наиболее тщательно о спарклайнах Вы сможете выяснить из урока Как употреблять спарклайны в Excel 2010. Внедрение динамических данных в Excel даёт одно замечательное преимущество – они будут автоматом обновляться при изменении инфы на web-странице.

Обновление данных

Если Вы желаете быть уверенными, что информация в таблице обновлена и очень животрепещуща, нажмите команду Refresh All (Обновить все) на вкладке Data (Данные). Это действие вышлет запрос web-странице и, если есть наиболее свежайшая версия данных, запустит процесс обновления в таблице.

Если же необходимо, чтоб информация в таблице автоматом обновлялась с некий данной периодичностью, изберите ячейку таблицы, содержащую динамические данные, и нажмите команду Properties (Характеристики) в разделе Connections (Подключения) на вкладке Data (Данные).

В открывшемся диалоговом окне поставьте галочку Refresh every (Обновлять любые) и укажите частоту обновления в минутках. По дефлоту Excel автоматом обновляет данные любые 60 минут, но Вы сможете установить хоть какой нужный период. Либо, к примеру, указать Excel обновлять информацию всякий раз при открытии файла.

Если Вы используете статические данные из веба в Excel, к примеру, удельные веса минералов либо площади территорий стран, тогда обновление в фоновом режиме можно отключить, чтоб Excel не соединялся с вебом без необходимости.

Заключение

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

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

С загрузкой в 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) и вводим адресок подходящей нам странички:

Опосля нажатия на ОК покажется окно Навигатора, где мы уже не увидим никаких нужных таблиц, не считая непонятной 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] — по другому фильтрация просто не сработает.

Опосля нажатия на ОК на дисплее останутся лишь строчки с подходящей нам информацией:

Чистим мусор

Остается почистить всё это от мусора хоть каким пригодным и комфортным лично для вас методом (их много). К примеру, так:

1. Удалить подменой на пустоту исходный тег: <span > через команду Основная — Подмена значений (Home — Replace values) .
2. Поделить получившийся столбец по первому разделителю » > » слева командой Основная — Поделить столбец — По разделителю (Home — Split column — By delimiter) и потом ещё раз поделить получившийся столбец по первому вхождению разделителя » < » слева, чтоб отделить полезные данные от тегов:

Разбираем блоки по столбцам

Если приглядеться, то информация о любом отдельном товаре в получившемся перечне сгруппирована в блоки по три ячейки. Само-собой, нам было бы еще удобнее работать с данной для нас таблицей, если б эти блоки перевоплотился в отдельные столбцы: стоимость, бренд (издательство) и наименование.

Выполнить такое преобразование можно весьма просто — с помощью, практически, одной строки кода на интегрированном в 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. )

Вот и все хитрости 🙂

Ссылки по теме

Николай! с Выздоравлением!

Может быть ли парсеринг инфы с такового сайта:

Пожалуйста подскажите каким образом.

Хороший вечер, Николай. У меня наиболее непростой вопросец, мне необходимо парсинг нескольких страничек в одну таблицу.
объясню, есть интернет-магазин инструмента, есть категория продукта, к примеру аккумулятор шуруповерты, снутри группы там много видов, брендов и типов самих шуруповертов (короче продукта), но мне нужно каким-то образом с корневой группы (аккумулятор шуруповерты) стянуть данные, находящиеся в каждой отдельной страничке продукта, т.е. данные, описание, черт и цены, что-то типа этого.

есть ли таковая возможность создать с сиим функционалом либо это жуе наиболее непростой скрипт в VBA.

и, совершенно, может быть ли такое?

Предварительно премного признателен, статья роскошна, уже попробовал ))))

Здрасти!
Спасибо Николаю за красивые видео и книжки, написанные легкодоступным языком. На этот момент интенсивно разбираюсь в PQ.
Воодушевленный сиим видео собрался сделать маленькую таблицу, которая забирала бы данные с нескольких веб-сайтов по животрепещущим ценам планшетов и выводила для принятия решения о покупке и отслеживания более прибыльных предложений.
Спарсить данные с Вайлдбериз не составило огромного труда, благодаря видео и их открытости, а вот получить данные от МВидео сделалось неувязкой, PQ их просто не лицезреет, хотя Power BI их непревзойденно распознает и выводит сходу в комфортном виде. Так как перекинуть запрос из BI в Excel нереально в домашней лицензии я просто скопировал запрос из 1-го PQ в иной. Вот здесь и вылезла ошибка:

Power BI умеет созодать команду Web.BrowserContents, а вот Excel ее не понимает:-(

Буду признателен за совет о парсинге данных с М.Видео либо о обходе данной ошибки в Excel.

© Николай Павлов, Planetaexcel, 2006-2021
info@planetaexcel.ru

Внедрение всех материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием наименования сайта, имени создателя и неизменности начального текста и иллюстраций.

За изображения спасибо Depositphotos.com

ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРН 310633031600071

Загрузка и Выгрузка продуктов для 1С-Битрикс в формате XLSX, CSV, XML, JSON

В большинстве проектов разработанных на 1С-Битрикс требуется настроить выгрузку либо загрузку продуктов. Самый наилучший вариант — написать личный скрипт-загрузчик под определенные задачки. Из данной статьи вы поймете почему это так, и узнаете в чем заключаются главные отличия различных форматов.

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

Обычные методы выгружать и загружать данные в 1С-Битрикс

В системе битрикс предвидено всего некоторое количество видов обычной загрузки и выгрузки данных. 1-ый из их это ИмпортЭкспорт Информационных блоков:

Конкретно инфоблоки являются главным инвентарем, в которым мы храним данные. Такая «часть базы данных» в комфортном представлении.

При ИмпортЭкспорте доступны 2 формата:
1. CSV — это простой формат, который можно открыть в экселе и узреть наглядно структуру файла в виде таблицы (правда, время от времени в сломанной шифровке, т.к. эта программка почему-либо любит windows-1251). Если открыть в блокноте, этот формат имеет вид:

В качестве разделителя выступает точка с запятой. С CSV приятно работать, и файлы в таком формате имеют меньший вес — в нем просто нет ничего излишнего.

Правда есть один противный аспект — В Битриксе вы не можете правильно выгрузить и потом загрузить данные о товаре, если созодать это через Импорт Экспорт инфоблока. При всем этом варианте отсутствуют главные поля каталога, цены и количество, какой в этом смысл? Не считая того, ссылки для изображений указывают на папку /upload/, как вы поймете что для вас копировать а что нет? Это можно поправить написав собственный скрипт, но о этом позднее.

2. XML — здесь дела обстоят лучше, когда речь входит о наиболее полной структуре данных. Здесь для вас и дерево из разделов, и все характеристики из информационного блока, и даже цены. По сопоставлению с CSV форматом, все изображения собираются в отдельную папку, с таковым же самым заглавием, которое мы давали файлу, что непременно плюс.

Из нередких заморочек я бы выделил:
— сам формат труднее в плане чтения и работы с ним (в случае если возжелаете что-то добавить через редактор)
— при выгрузке продукты выгружаются в новейший инфоблок (а обязано загружаться в старенькый)

Видите ли, если вы автоматом собираете XML выгрузку, к примеру, из программки 1С, и опосля этого загружаете без заморочек на собственный веб-сайт, то XML весьма даже неплохой формат. Но как указывает практика, это весьма редчайшие случаи.

Какая выгрузка на проектах изготовленных на 1С-Битрикс самая наилучшая?

Выгрузка в формате CSV, XLSX либо XLS для CMS 1С-Битрикс написанная вручную

По моему опыту, самый наилучший вариант, это написать неповторимый загрузчик. Почему?

1. Будет применен формат данных, к которому привыкли вы и ваши коллеги / партнеры. К примеру, если у вас сохраняется определенная последовательность полей — «Заглавие, Артикул, Стоимость, Вес», то в самом скрипте будет прописано «находить продукты по Артикулу, если ничего не найдено находить по Наименованию, если продукт найден поменять его Стоимость и Вес«.
2. При желании файл может загружать рисунки из определенных папок и даже посторониих веб-сайтов. Задавать рисунки для продуктов можно будет не только лишь для полей «Картина для анонса» и «Детальная картина», да и перезаписать нередко применяемое свойство типа Файл, с отметкой Множественное.
3. Можно будет задать наиболее сложную логику загрузки. К примеру, у ваших продуктов есть свойство бренд, и вы желаете чтоб всем товарам также отмечался раздел, с подходящим брендом. Все ограничивается только поставленной задачей, в рамках наиболее сложной логики загрузки можно учитывать почти все аспекты, которые применимы лишь в вашем бизнесе либо проекте.

При разработке такового скрипта код пишется с нуля на языке PHP при использовании Bitrix API, и не содержит излишних строчек.

Форматы файлов могут быть хоть какими: CSV, XML, XLSX, XLS, YML, JSON и т.д.

Вас интересует данная услуга? Свяжитесь со мной, отвечу на все вопросцы и помогу с реализацией.

Для примера отчет 1-го из таковых загрузчиков, который видно сходу опосля выполнения скрипта:

Отчет помогает находить любые несоответствия в базе, исправлять ошибки.
Во время выполнения скрипта можно добавить прелоадер и разбить загрузку на шаги:

Также, есть вариант загрузки в битрикс с иной базы данных (при условии что к ней есть доступ). Плюс этого метода в том, что запросы к базе данных работают весьма стремительно.

создатель: Dmitriy

Занимаюсь веб-разработкой с 2011 года. Поглядеть некие из моих работ, также выяснить чуток больше обо мне, вы сможете на forwww.ru.

— Создание веб-сайтов на 1С-Битрикс хоть какой трудности
— Вёрстка макетов Figma, Photoshop, Zeplin
— Поддержка проектов на Битриксе
— Разработка новейшего функционала для сайта
— Парсинг данных
— Выгрузка из файлов в формате XML, YML, XLS, XLSX, CSV, JSON
— Интеграция по API со посторонними сервисами
и почти все другое

Как выгрузить продукты в Excel?

Опосля загрузки продуктов на парсер, находясь на страничке со перечнем продуктов, нажмите клавишу «Выгрузить».

2. Задайте опции файла

В показавшемся окне изберите формат «Excel (XLS/x)» и задайте опции формата: версия Excel (XLS либо XLSx), разделение черт и пр..

Направьте внимание, что формат Excel5 не поддерживает больше 65535 продуктов в одном файле.

Подробное описание каждой опции вы сможете отыскать ниже на данной для нас страничке.

3. Выгрузка запущена

Покажется индикатор выгрузки продуктов. Если не желаете ожидать, вы сможете выключить комп либо закрыть браузер — выгрузка продолжится независимо от вас.

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

Встраивание изображений в ячейки

Для формата Excel имеется возможность автоматического встраивания изображений в ячейки таблицы. Для этого нужно избрать подобающую опцию в разделе «Выгрузка изображений» окна выгрузки.

В ячейку постоянно выгружается 1-ое изображение уменьшенного размера.

Опции формата:

Разделитель изображений — знак для разделения нескольких ссылок изображений снутри одной колонки. Можно задать разбиение нескольких картинок по отдельным колонкам.

Разделитель черт — знак для разделения нескольких черта продукта снутри одной колонки. К примеру, цветов: красноватый/голубий/зеленоватый.

Формат файла — дозволяет указать формат файла версии Excel. Обратиет внимание, что формат Excel5(XLS) не поддерживает наиболее 65535 строк (продуктов).

Наибольшее число продуктов для выгрузки: 300000

За одну выгрузку можно выгрузить не больше обозначенного числа продуктов. Это соединено с ограничениями на размер файла при импорте на ваш веб-сайт.

Используйте частичную выгрузку для выгрузки большего числа продуктов.

Общие опции:

Разрешить HTML разметку в полях продукта — разрешает либо воспрещает HTML-разметку в полях продукта. Весьма изредка употребляется интернет-магазинами.

Выгрузка изображений — дозволяет поменять метод выгрузки изображений.

Имеется возможность скачать все изображения продуктов в виде архива. Для этого изберите опцию «Поместить в ZIP-архив» при выгрузке.

Выгрузка черт — дозволяет выгрузить характеристики продуктов (цвета, размеры и пр.) отдельными полями либо добавить их к описанию.

Выгрузка рядов — аналогично выгрузке черт, но в отношении рядов продукта. Дозволяет добавить информацию о рядах к описанию.

Делить группы — делить вложенные группы на различные поля (по цифрам либо по заглавиям).

Разбить на несколько файлов — дозволяет разбить выгрузку на несколько файлов: по категориям либо по брендам.

Отыскали ошибку в выгрузке в этот формат?

Если вы нашли ошибку в формате выгрузки Excel (XLS/x), пожалуйста, сообщите нам на почту либо в чат на веб-сайте. Мы попытаемся поправить выгрузку как можно быстрее.