Как создать источник данных в excel
Представьте себе ситуацию, Вы получили целевую выборку из одной базы данных, но для полноты картины, как всегда, нужны дополнительные данные. Проблема может быть в том, что нужная информация хранится в другой базе данных и возможности создать на ней свою таблицу нет, подключиться используя link тоже нельзя, да и количество элементов, по которым нужно получить данные, несколько больше, чем допустимое на данном источнике. Вот и получается, что возможность написать SQL запрос и получить нужные данные есть, но написать придется не один запрос, а потом потратить время на объединение полученных данных.
Выйти из подобной ситуации поможет Excel.
Уверен, что ни для кого не секрет, что MS Excel имеет встроенный модуль VBA и надстройки, позволяющие подключаться к внешним источникам данных, то есть по сути является мощным инструментом для аналитики, а значит идеально подходит для решения подобных задач.
Для того чтобы обойти проблему, нам потребуется таблица с целевой выборкой, в которой содержатся идентификаторы, по которым можно достаточно корректно получить недостающую информацию (это может быть уникальный идентификатор, назовем его ID, или набор из данных, находящихся в разных столбцах), ПК с установленным MS Excel, и доступом к БД с недостающей информацией и, конечно, желание получить ту самую информацию.
Создаем в MS Excel книгу, на листе которой размещаем таблицу с идентификаторами, по которым будем в дальнейшем формировать запрос (если у нас есть уникальный идентификатор, для обеспечения максимальной скорости обработки таблицу лучше представить в виде одного столбца), сохраняем книгу в формате *.xlsm, после чего приступаем к созданию макроса.
Через меню «Разработчик» открываем встроенный VBA редактор и начинаем творить.
Sub job_sql() — Пусть наш макрос называется job_sql.
Пропишем переменные для подключения к БД, записи данных и запроса:
Опишем параметры подключения:
sql = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=Storoge.company.ru\ Storoge."
Объявим процедуру свойства, для присвоения значения:
Set cn = New ADODB.Connection cn.Provider = " SQLOLEDB.1" cn.ConnectionString = sql cn.ConnectionTimeout = 0 cn.Open
Вот теперь можно приступать непосредственно к делу.
Как вы уже поняли конечное значение i=1000 здесь только для примера, а в реальности конечное значение соответствует количеству строк в Вашей таблице. В целях унификации можно использовать автоматический способ подсчета количества строк, например, вот такую конструкцию:
Тогда открытие цикла будет выглядеть так:
Как я уже говорил выше MS Excel является мощным инструментом для аналитики, и возможности Excel VBA не заканчиваются на простом переборе значений или комбинаций значений. При наличии известных Вам закономерностей можно ограничить объем выгружаемой из БД информации путем добавления в макрос простых условий, например:
Итак, мы определились с объемом и условиями выборки, организовали подключение к БД и готовы формировать запрос. Предположим, что нам нужно получить информацию о размере ежемесячного платежа [Ежемесячный платеж] из таблицы [payments].[refinans_credit], но только по тем случаям, когда размер ежемесячного платежа больше 0
sql = "select [Ежемесячный платеж] from [PAYMENTS].[refinans_credit] " & _ "where [Ежемесячный платеж]>0 and [Номер заявки] ='" & Cells(i, 1) & "' "
Если значений для формирования запроса несколько, соответственно прописываем их в запросе:
"where [Ежемесячный платеж]>0 and [Номер заявки] = '" & Cells(i, 1) & "' " & _ " and [Дата платежа]='" & Cells(i, 2) & "'"
В целях самоконтроля я обычно записываю сформированный макросом запрос, чтобы иметь возможность проверить его корректность и работоспособность, для этого добавим вот такую строчку:
Возможность анализа всех данных помогает принимать более важные деловые решения. Но иногда бывает сложно понять, с чего начать, особенно если у вас много данных, которые хранятся за пределами Excel, например в базе данных Microsoft Access или Microsoft SQL Server либо в файле куба OLAP. В этом случае нужно подключиться к внешнему источнику данных, а затем создать сводимую, анализировать, изучать и представлять эти данные.
Вот как можно создать с помощью существующего подключения к внешним данным с помощью этой системы.
Щелкните любую ячейку на листе.
Выберите Вставка > Сводная таблица.
В диалоговом окне Создание сводной таблицы щелкните Из внешнего источника данных.
Выберите вариант Выбрать подключение.
На вкладке Подключения в поле Показать выберите все подключения или выберите категорию подключения с источником данных, к который вы хотите подключиться.
Чтобы повторно использовать существующее подключение или поделиться им, используйте подключение из Connections в этой книге.
В списке подключений выберите нужное подключение и нажмите кнопку Открыть.
В области Выберите, куда следует поместить отчет, выберите расположение.
Чтобы разместить таблицу на новом месте, начиная с ячейки A1, выберите элемент На новый.
Чтобы разместить таблицу на активном месте, выберите На существующий таблицу, а затем в поле Расположение введите ячейку, с которой нужно начать работу.
Excel добавляет пустую pivottable и отображает список полей, чтобы вы могли отдемонстрировать нужные поля и переустановить их для создания собственного макета.
В разделе список полей, чтобы разместить поле в области по умолчанию в разделе областей списка полей, поместите его рядом с именем поля.
Как правило, не числовые поля добавляются в область строк, числовые — в область значения, а поля даты и времени — в область Столбцы. При необходимости вы можете переместить поля в другую область.
Совет: Вы также можете щелкнуть имя поля правой кнопкой мыши и выбрать добавить в фильтр отчета, Добавить в названия столбцов, Добавить в названия строк или Добавить в значения, чтобы разместить поле в этой области раздела областей, или перетащить поле из раздела полей в область в разделе областей.
С помощью списка полей можно более точно спроектировать макет и формат с помощью правой кнопки мыши полей в разделе областей и выбора нужной области или перетаскиванием полей между областями раздела областей.
Подключение к новому внешнему источнику данных
Чтобы создать новое подключение к внешним данным SQL Server и импортировать данные в Excel как таблицу или таблицу, сделайте следующее:
Щелкните Данные > из других источников.
Выберите нужное подключение.
Щелкните Из SQL Server, чтобы создать подключение к SQL Server таблице.
Щелкните Из служб Analysis Services, чтобы создать подключение к кубу SQL Server Analysis.
В мастере подключения к данным выполните действия, чтобы установить подключение.
На странице 1 введите сервер базы данных и укажите, как вы хотите войти на сервер.
На странице 2 введите базу данных, таблицу или запрос, которые содержат нужные данные.
На странице 3 введите файл подключения, который вы хотите создать.
Чтобы создать новое подключение к базе данных Access и импортировать данные в Excel как таблицу или таблицу, сделайте следующее:
Щелкните Данные > из Access.
В диалоговом окне Выбор источника данных найдите базу данных, к которой нужно подключиться, и нажмите кнопку Открыть.
В диалоговом окне Выбор таблицы выберите таблицу и нажмите кнопку ОК.
Если таблиц несколько, в поле Включить выбор нескольких таблиц можно включить флажки нужных таблиц, а затем нажмите кнопку ОК.
В диалоговом окне Импорт данных выберите, как вы хотите просмотреть данные в книге и куда их поместить, а затем нажмите кнопку ОК.
Таблицы автоматически добавляются в модель данных, а база данных Access — к подключениям к книге.
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .
В этой статье
Обзор источников данных
Все данные, которая появляется в форме, является ли это данные, которые может выбрать пользователь в поле со списком, раскрывающегося списка или поля со списком или данные, которые пользователь добавляет в форму хранятся в форме источников данных. Источник данных состоит из полей и групп. Таким же образом, что содержат папки на жестком диске и упорядочения файлов, полей с данными, пользователи ввели в формах, основанных на шаблоне формы и группы содержат и объединяют эти поля. Пользователь добавит данных к элементам управления в форме и связанные элементы управления для этих полей. Например предположим, что вы разрабатываете шаблона формы для отчета о расходах. При открытии формы на основе шаблона формы, их можно ввести их имена, отчества и фамилии в трех текстовых полях — поле имени в поле для среднего имени и поле для фамилии привязан каждой надписи. Эти поля имя, содержащихся в группу с именем «имя».
Можно просматривать и работа с полями и группами в источнике данных шаблона формы в области задач Источник данных.
Несмотря на то, что структура источника данных не всегда совпадает с макета шаблона формы, существуют общие сходства, особенно для групп и поля, которые связываются с повторяющимися таблиц, разделы, повторяющиеся разделы и дополнительные разделы. Например таблица или раздел связана с группой в источнике данных, и все элементы управления таблицы или раздела связаны с полями, которые являются частью этой группы. На приведенном ниже рисунке расходы по категориям повторяющуюся таблицу в форме отчета о расходах состоит из трех столбцов, содержащих управляющего элемента выбора даты, текстовое поле Описание и затраты текстового поля. Эти элементы управления связаны для поля даты, описание и поле Сумма соответственно. Сама таблица связана с группой элемента.
Шаблон формы может иметь двух типов источников данных — один, основной источнику данных и при необходимости, один или несколько дополнительных источников данных.
Основной источник данных
Основной источник данных содержит следующие элементы:
Данные, добавить пользователей при заполнении формы, основанного на шаблоне формы, например данные, они вводятся в текстовое поле. Например пользователь вводит их имена, отчества и фамилии в текстовые поля, которые связаны с полями в основной источник данных.
Данные из подключение к данным в базу данных или веб-службы, который используется для определения источника данных шаблона формы.
Создание основного источника данных
При создании шаблона формы Microsoft Office InfoPath автоматически создает основного источника данных. Все шаблоны форм InfoPath содержать один, основной источнику данных. Поля и группы, можно добавить в основной источник данных, в зависимости от типа шаблона формы, которые можно создать:
Шаблон пустая форма Будет добавлен полей и групп основной источник данных с помощью области задач Источник данных или путем перетаскивания элемента управления из области задач элементы управления в представление в шаблоне формы. При перетаскивании элемента управления в представлении InfoPath добавляет основной источник данных в зависимости от типа элемента управления, добавляемого поля и группы. Например при перетаскивании элемента управления текстового поля в шаблон формы InfoPath добавляет поле основного источника данных. При перетаскивании элемента управления повторяющегося раздела в шаблон формы InfoPath добавляет группу основного источника данных. При перетаскивании элемента управления повторяющейся таблицы в шаблон формы InfoPath добавит двумя группами основного источника данных для повторяющейся таблицы и добавляет поля в основной источник данных для каждого столбца в таблице.
Шаблон формы, основанный на XML-документа Приложение InfoPath добавляет полей и групп, сопоставить элементы, определенные в схеме или элементы в документе.
Шаблон формы на основе базы данных Microsoft Office Access или базу данных Microsoft SQL Server InfoPath добавляет основного источника данных, исходя из способа базе данных хранятся полей и групп.
Шаблон формы, основанный на веб-службы Приложение InfoPath добавляет полей и групп, которые сопоставляют схема, предоставленному веб-службы.
Если источник данных, например базы данных или веб-службы — не входит в шаблоне формы, он считается внешним источником данных. Формы InfoPath подключиться к эти внешним источникам данных через подключение к данным.
Изменение основного источника данных
Для основного источника данных, в зависимости от существующего поля или группы в основной источник данных можно добавить дополнительные поля и группы. В следующей таблице представлены добавить существующие поля или группы, на основании значки, которые отображаются в области задач Источник данных.
Можно добавить
Вид значка В эту группу можно добавить поля или группы. Этот значок, представляющий любой из группы, который был добавлен основной источник данных с помощью области задач Источник данных или элемент управления, связанных с этой группы, который был перетащить из области задач элементы управления в представление в шаблоне пустая форма.
Значок поля В этом поле можно добавить только поля. Этот значок, представляющий какой-либо из поля, который был добавлен основной источник данных с помощью области задач Источник данных или элемент управления, связанные с этим полем, которая была перетащить из области задач элементы управления в представление в шаблоне пустая форма.
Значок группы Защищаемая ячейка Нельзя добавлять поля или группы в эту группу. Эта группа будет зависеть от подключение данных к внешнему источнику данных.
Значок Блокировка поля Невозможно добавить поля к этому полю. В этом поле будет зависеть от подключение данных к внешнему источнику данных.
При перетаскивании элемента управления в представление в шаблоне формы и этот шаблон формы содержит полей и групп, основанных на подключение к данным, вы должны привязан существующих полей и групп в основной источник данных. InfoPath не добавлять автоматически полей и групп в шаблон формы, содержащей полей и групп, основанные на внешних источников данных. Вы найдете ссылки на дополнительные сведения о добавлении полей и групп в разделе См.
Примечание: Если ваши пользователи уже заполнили формы, основанных на шаблоне формы, перечисленные ниже изменения шаблона формы основной источник данных может привести к потере данных в этих формах:
Перемещение, удаление и переименование поля или группы
Изменение повторяющегося поля или группы на неповторяющееся поле или группу
Изменение типа данных форматированного текста на другой тип данных
Для полей и групп, которые можно изменять вы можете изменить следующие свойства:
Имя Имя поля или группы.
Требования к именам
Каждое имя в основной источник данных должно быть уникальным. Если необходимо использовать то же имя для более одного поля или группы, создать ссылку для этого поля или группы вместо него. При создании ссылки на поля или группы InfoPath создает новую, одинаковые поля или группы, свойства которого связаны со свойствами первого поля или группы. Изменения для одного поля или группы автоматического обновления других, на который указывает ссылка поля или группы. Справочные сведения о группах, как ссылки на поля, содержащие же полей и групп и совместное использование те же свойства.
Имена не могут содержать пробелы.
Имена должны начинаться с буквы или подчеркивания (_), и они могут содержать только буквенно-цифровые символы подчеркивания, дефисов (-) и точки (.).
Дополнение к этим требованиям рекомендуется использовать имя, описывающее содержимое поля или группы. Например если у вас есть группы, содержащий детализированные сведений о расходах, имя группы ItemizedExpense. В поле в группу, которая содержит сумму расходов имя поля расходов.
Тип Определяет, является ли элемент в основной источник данных поля или группы. Поле содержит уникальное значение, а группа содержит другие поля.
Типы полей и групп
Когда использовать
Поле (атрибут)
Используйте этот тип для поля, если одно или несколько из следующих условий:
Поле не может содержать другие поля или группы.
Поле должно быть в поле элемента.
Поле не приводится.
Поле всегда должно содержать значение.
Это поле остается любого типа данных, кроме форматированный текст (XHTML).
База данных или веб-службы требует поле атрибута.
Поле (элемент)
Используйте этот тип для поля, если одно или несколько из следующих условий:
Поле может содержать другие поля атрибутов.
Поле является частью группы.
Поле повторяется в группе.
База данных или веб-службы требует поле элемента.
Поля элемент может быть любой данных введите и может или не может иметь значение по умолчанию.
Используйте этот тип группы, который может содержать другие поля или группы.
Группа (Выбор)
Используйте этот тип, если группа может содержать только один из нескольких различных типов поля или группы полей.
Например предположим, вы разрабатываете шаблона формы, поставщиков в Соединенных Штатах и в странах и регионах в Европейском союзе будет использовать и содержит адрес группа выбора шаблона формы. Группа выбора адрес содержит с полями для адресов в Соединенных Штатах или группу с полями для адресов в стране или регионе на территории Европейского союза. Когда пользователь открывает форму, основанную на этом шаблоне формы, можно выбрать пользователя для ввода адреса в Соединенных Штатах или адреса в другой стране или регионе. В форме отображаются элементы управления, которые связаны с полями в нужную группу.
Полная схема XML или XML-документ
Используйте этот тип элемента, если вы хотите добавить дополнительные схемы XML или XML-документ в указанном расположении в источнике данных.
Тип данных Определяет, какие данные, которые можно хранить в поле.
Список доступных типов данных
Когда использовать
Используйте этот тип данных для элемента или атрибута поле, содержащее неформатированные текста.
Используйте этот тип данных для элемента поле, содержащее форматированный текст. Нельзя использовать этот тип данных для полей атрибутов.
Используйте этот тип данных для элемента или атрибута поля, содержащий числа без десятичных знаков.
Используйте этот тип данных для элемента или атрибута поле, содержащее значения валюты или числа, десятичные значения.
Используйте этот тип данных для элемента или атрибута поля, которое содержит данные, которые могут быть только одно из двух значений.
Используйте этот тип данных для элемента или атрибута поле, содержащее универсальный код ресурса (URI), например гиперссылки или пространство имен.
Используйте этот тип данных для элемента или атрибута поле, содержащее дату календаря.
Используйте этот тип данных для элемента или атрибута поле, содержащее 24-часовом формате.
Используйте этот тип данных для элемента или атрибута поле, содержащее дату календаря и 24-часовом формате.
Изображение или вложение файла
Используйте этот тип данных для элемента или атрибута поле, которое содержит двоичные данные, например изображения или рисунка.
Используйте этот тип данных для элемента или атрибута поля с пользовательские типы данных, которые определены с помощью пространства имен. Этот тип данных обычно используется для поля или группы, к которому привязан пользовательский элемент управления. Поле имен данных становится доступен при выборе этого типа данных.
Примечание: В этой таблице показаны только наиболее распространенные XML типы данных используются в шаблоне формы. В приложении InfoPath можно использовать любой тип данных XML 1.0 рекомендаций World Wide Web Consortium (W3C). Чтобы использовать тип данных, который отсутствует в таблице, необходимо извлечь файлы формы для шаблона формы и редактирование файла схемы (XSD). Вы найдете ссылки на дополнительные сведения об извлечении файлов формы для шаблона формы в разделе См.
Пространство имен данных Определяет универсальный идентификатор ресурса (URI) для добавления имен для пользовательского типа данных. Это свойство доступно для элемента или атрибута полей со свойством пользовательский тип данных. При добавлении настраиваемого элемента управления в шаблон формы, это свойство обычно содержит значение, определенное автором настраиваемого элемента управления, привязанный к полю.
Значение по умолчанию Определяет начальное значение поля элемента или атрибута при первом открытии формы пользователем. Можно также добавить формулы, значения по умолчанию, например текущую дату, математическое выражение или ссылку на другое поле.
Повторяющийся Определяет, является ли поле элемента, атрибута поля или группы может возникнуть несколько раз в форме. Список элементов управления, повторяющиеся разделы, повторяющиеся таблицы и элементы управления, которые являются частью повторяющегося раздела или повторяющейся таблицы связаны с повторяющимися полями и повторяющихся групп.
Не могут быть пустыми Определяет, будет ли поле элемента или атрибута должно содержать значение. Если поле не имеет значения, элемент управления, привязанный к этому полю помечается красная звездочка или элементов управления, красным пунктиром. Это свойство доступно только для полей. Не поддерживается для групп.
Дополнительные источники данных
Дополнительные источники данных, источников данных в форму, содержащих данные, предоставленный через другие данные подключения к внешним источникам данных. Например предположим, что создание шаблона формы для отчета о расходах должны форм, основанных на шаблоне формы для принятия расходы в других валют. Тем не менее валюты данные хранятся в базе данных. Для отображения данных денежных единиц, Добавление подключения данных к базе данных. Данные валют сохраняется в дополнительный источник данных и отображаются в элемент управления списком шаблона формы.
Данные из дополнительные подключения к данным хранятся в дополнительных источников данных и затем отображаются как варианты в элементах управления, например список, поле со списком или раскрывающегося списка, пользователь может выбрать при заполнении формы. При выборе элемента в дополнительный источник данных к полю в основной источник данных копируется выделенного фрагмента.
Дополнительные источники данных отличаются от основного источника данных из следующих способов:
Дополнительные источники данных не являются обязательными в шаблоне формы. Все шаблоны форм необходимо иметь основной источник данных.
Шаблон формы можно имеют один или несколько дополнительных источников данных, но только один, основной источнику данных.
Полей и групп в дополнительный источник данных основаны на способ хранения данных в внешнего источника данных, который подключен к форме с помощью подключения к данным. Невозможно изменить этих полей и групп с помощью InfoPath.
В отличие от основного источника данных дополнительным источникам данных, не сохраняются вместе с формой.
Когда пользователь отправляет заполненной формы данных в дополнительных источников данных обычно не отправляется. Однако можно настроить шаблона формы для отправки данных в дополнительный источник данных для веб-службы с помощью правил или по электронной почте с использованием пользовательского кода.
Создание дополнительных источников данных
При добавлении подключения к данным к внешнему источнику данных, которая возвращает данные в форму InfoPath автоматически создает дополнительный источник данных. Каждого подключения к данным, которые будут добавлены в шаблон формы создается соответствующий дополнительный источник данных. Таким же образом, полей и групп, добавляются в основной источник данных InfoPath добавляет полей и групп дополнительный источник данных. Дополнительный добавляются полей и групп, чтобы они согласуются с принципами, что к внешнему источнику данных хранятся данные источника данных. Можно создать дополнительного подключения данных для указанных ниже действий:
База данных Microsoft Office Access или базу данных Microsoft SQL Server
Список на сайте под управлением Microsoft Windows SharePoint Services
После добавления подключения к данным в шаблоне формы и создать дополнительный источник данных, вы увидите полей и групп в дополнительный источник данных в области задач Источник данных. В этой области задач с именем подключения к данным, которые следуют вспомогательной в круглых скобках отображается каждый дополнительный источник данных.
Изменение дополнительных источников данных
Так как полей и групп в дополнительных источников данных основаны на данные подключения к внешним источникам данных, нельзя изменить поля и группы в дополнительный источник данных. При просмотре дополнительный источник данных в области задач Источник данных, поля имеют значок заблокированные поле и значок заблокированные группы указывает, что у группы не могут быть изменены.
Общие сведения о связи между источником данных и XML-схемы
При заполнении формы, основанного на шаблоне формы формы основной и дополнительный источников данных хранятся в виде XML-документов на компьютере пользователя. Структура XML-документа определяется XML-схемы. Схема XML является XML-файл, который определяет структуру и элементы XML-документ, в данном случае формы и данных, которые могут содержать элементы.
При создании шаблонов форм InfoPath автоматически создает схемы XML, которая используется для определения структуры форм, заполняемых пользователей. Элемент XML-схемы соответствует каждого поля или группой в источнике данных. Свойства для каждого поля и группой в источнике данных определяют структуры соответствующие элементы и каждый элемент может содержать данные в результирующем XML-документе. Вы можете просмотреть дополнительные схемы сведения на вкладке сведения в поле Свойства поля или группы диалоговое окно, которое можно открыть, дважды щелкнув поля или группы в области задач Источник данных.
С помощью Excel можно создавать и изменять подключения к внешним источникам данных, которые хранятся в книге или файле подключения. Вы можете легко управлять этими подключениями, в том числе создавать, изменять и удалять их, с помощью текущей области Запросы и подключения & или диалоговое окно Подключения к книге (доступно в предыдущих версиях).
Примечание: Подключение к внешним данным в настоящее время может быть отключено на компьютере. Чтобы подключаться к данным при открытии книги, включите функцию подключения к данным на панели центра управления безопасностью или сохраните книгу в надежном расположении. Дополнительные сведения см. в настройках Добавление,удаление и изменение надежного расположения для файлов, Добавление,удаление и просмотр надежного издателя и Просмотр параметров в центре управления доверием.
Данные в книге Excel могут приходить из двух разных мест. Данные могут храниться непосредственно в книге или во внешнем источнике данных, например в текстовом файле, базе данных или кубе OLAP. Внешний источник данных подключается к книге с помощью подключения к данным, которое является набором сведений, описывающий, как найти внешний источник данных, войти в систему, создать запрос и получить к нему доступ.
Если вы подключены к внешнему источнику данных, вы также можете выполнить операцию обновления для получения обновленных данных. При каждом обновлении данных вы увидите их последнюю версию, включая изменения, внесенные в данные с момента последнего обновления.
Сведения о под соединении могут храниться в книге или в файле подключения, например в Office-файле подключения к данным (ODC) или в файле UDC (UDCX). Файлы подключений особенно полезны для совместного использования подключений на постоянной основе и для облегчения администрирования источников данных.
Если для подключения к источнику данных используется файл подключения, Excel копирует сведения о подключении из файла подключения в Excel книгу. При внесении изменений в диалоговом окне Свойства подключения вы редактируете данные подключения к данным, хранимые в текущей книге Excel, а не исходный файл подключения, который мог использоваться для создания подключения, который указан именем файла, которое отображается в свойстве Файл подключения. После изменения сведений о под соединении (за исключением свойств Имя подключения и Описание подключения) ссылка на файл подключения удаляется, а свойство Файл подключения удаляется.
В диалоговом окне Свойства подключения или мастере подключения к данным можно Excel для создания ODC-файла Office подключения к данным (ODC). Дополнительные сведения см. в свойствах подключения и Совместное передачу данных с помощью ODC.
Выполните одно из указанных ниже действий.
Создайте новое подключение к источнику данных. Дополнительные сведения см. в Excel в Access, Импорт и экспорт текстовых файлов или Подключение в SQL Server служб Analysis Services (Импорт).
Используйте существующее подключение. Дополнительные сведения см. в Подключение внешних данных.
Сохраните данные подключения в файл подключения, нажав кнопку Экспорт файла подключения на вкладке Определение в диалоговом окне Свойства подключения, чтобы отобразить диалоговое окно Сохранение файла, а затем сохраните текущие данные подключения в ODC-файл.
Примечание В Microsoft Office 365 для Excel и Excel версии 2019 и более поздних версий доступна & запросы и подключения. Она заменила диалоговое окно Подключения к книге, которое доступно Excel автономных версиях 2010, 2013 и 2016.
Области Запросы и подключения & (Выбор данных> Запросы & Подключения). В одном расположении вы можете получить все сведения и команды, необходимые для работы с внешними данными. В этой области есть две вкладки:
ЗапросовОтображение всех запросов в книге. Щелкните запрос правой кнопкой мыши, чтобы увидеть доступные команды. Дополнительные сведения см. в теме Управление запросами.
Соединения Отображение всех подключений в книге. Щелкните правой кнопкой мыши подключение, чтобы увидеть доступные команды. Дополнительные сведения см. в свойстве подключения.
Примечание Диалоговое окно Подключения к книге доступно в Excel автономных версиях 2010, 2013 и 2016, но было заменено в Microsoft Office 365 для Excel и Excel версии 2019 на области "Запросы & Подключения".
Диалоговое окно Подключения к книге (Выбор подключения > данных)позволяет управлять подключениями к внешним источникам данных в книге.
Создавать, изменять, обновлять и удалять подключения, используемые в книге.
Узнавать, где находится источник внешних данных (если, например, подключение было определено другим пользователем).
Просматривать подключения в текущей книге.
Перенаправлять подключение на другой сервер или источник данных и заменять файлы подключения для существующих подключений.
Открывать диалоговое окно Существующие подключения для создания новых подключений. Дополнительные сведения см. в Подключение внешних данных.
Отображать диалоговое окно Свойства подключения для изменения свойств подключения к данным, редактирования запросов и настройки параметров. Дополнительные сведения см. в статье Свойства подключения.
Создавать файлы подключения и делиться ими с другими пользователями.
Для управления подключениями в книге выполните одно или несколько из следующих действий:
Просмотр подключения
В верхней части диалогового окна автоматически отображаются все подключения, а также следующие сведения:
Имя подключения, заданное в диалоговом окне Свойства подключения.
Необязательное описание подключения, заданное в диалоговом окне Свойства подключения.
Дата и время последнего успешного обновления подключения. Если эта информация отсутствует, то подключение никогда не обновлялось.
Добавление подключения
Отображение сведений о подключении
Выберите подключение, а затем щелкните Свойства, чтобы открыть диалоговое окно Свойства подключения. Дополнительные сведения см. в статье Свойства подключения.
Обновление внешних данных
Щелкните стрелку рядом с кнопкой Обновить, а затем выполните следующие действия:
Для обновления определенных подключений выберите одно или несколько подключений, а затем щелкните Обновить.
Для обновления всех подключений в книге снимите все флажки, а затем щелкните Обновить все.
Для получения информации о состоянии обновления выберите одно или несколько подключений, а затем щелкните Обновить состояние.
Для остановки текущего обновления щелкните Отменить обновление.
Дополнительные сведения см. в этойExcel.
Удаление одного или нескольких подключений
Выберите одно или несколько подключений, а затем нажмите кнопку Удалить.
Эта кнопка недоступна, если защищены книга или объект, использующий подключение (например, отчет сводной таблицы).
При удалении подключения удаляется только подключение. Объекты или данные из книги не удаляются.
Важно: Удаление подключения разрывает подключение к источнику данных и может вызвать непредвиденные последствия, например получение других результатов вычисления формул и возможные проблемы с другими функциями Excel.
Отображение мест использования подключений в книге
Выберите одно или несколько подключений, а затем в разделе Подключения, используемые в этой книге щелкните ссылку Щелкните здесь, чтобы увидеть, где используются выбранные подключения.
Будет выведена следующая информация:
Лист, на котором используется подключение.
Имя запроса Excel.
Ссылка на ячейку, диапазон или объект.
Значение ячейки. Для диапазона ячеек значение не указывается.
Формула ячейки или диапазона ячеек.
При выборе другого соединения в верхней части диалогового окна эта информация изменяется.
Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: MS SQL Server Management Studio, Aquafold Aqua Data Studio, DBeaver и т.п., а вероятность их установки в краткосрочной перспективе близка к нолю. В то же время, присутствует острая необходимость подключения к этой самой БД и работы с данными. Как оказалось, на помощь может прийти старый добрый MS Excel.
В моем случае требовалось подключиться к MS SQL Server, однако, MS Excel умеет устанавливать соединение не только с ним, но и с большинством современных БД: MySQL, PostgreeSQL, IBM DB2 и даже Oracle и Teradata, а также с файлами данных CSV, XML, JSON, XLS(X), MDB и другими.
Теперь немного о действиях, совершенных мной с целью подключения к базе:
В новой книге на ленте выбираем «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Из других источников» -> «(4) С сервера SQL Server».
Далее, в окне Мастера подключения к данным, заполняем «(1) Имя сервера» -> «(2) Учетные сведения»[ -> «(3) Имя пользователя» и «Пароль»]. Таким образом, мы сообщаем MS Excel, с каким сервером мы хотим установить соединение и какой метод аутентификации хотим использовать. Я использовал «проверку подлинности Windows», но возможно также указать учетные данные отличные от установленных в Windows.
Выбираем целевую «(1) Базу данных» -> «(2)(3) Определенную таблицу» или «Несколько таблиц» или же базу в целом (тогда оба «чекбокса» оставляем пустыми).
После всех проделанных манипуляций, Мастер подключения предложит сохранить файл подключения. Потребуется задать «(1) Имя файла». Желательно также указать «(2) Описание» и «(3) Понятное имя файла», чтобы спустя время было понятно какой файл подключения к какой базе или таблице обращается.
Теперь выбрать созданное подключение можно будет следующим образом: «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Существующие подключения».
Открыв только что созданное подключение, в случае если вы соединялись с базой в целом, MS Excel опять предложит выбрать одну или несколько конкретных таблиц:
Определив таблицы, MS Excel предложит выбрать «(1) Способ представления данных» и «(2) Куда следует поместить данные». Для простоты я выбрал табличное представление и размещение на уже имеющемся листе, чтобы не плодить новые. Далее следует нажать на «(3) Свойства».
В свойствах подключения, нужно перейти на вкладку «(1) Определение». Здесь можно выбрать «(2) Тип команды». Даже если требуется выгружать лишь одну таблицу без каких-либо связей, настоятельно рекомендую выбрать SQL команду, чтобы иметь возможность ограничить размер выгружаемой таблицы (например, с помощью TOP(n)). Так, если вы попытаетесь выгрузить целиком таблицу базы, это может привести в лучшем случае к замедлению работы MS Excel, а в худшем к падению программы, к тому же – это необоснованная нагрузка на сам сервер базы данных и на сеть. После того как «(3) Текст команды» будет введен и нажата кнопка «ОК», MS Excel предложит сохранить изменения запроса – отвечаем положительно.
Читайте также: