Запрос ole db в excel
Представьте себе ситуацию, Вы получили целевую выборку из одной базы данных, но для полноты картины, как всегда, нужны дополнительные данные. Проблема может быть в том, что нужная информация хранится в другой базе данных и возможности создать на ней свою таблицу нет, подключиться используя link тоже нельзя, да и количество элементов, по которым нужно получить данные, несколько больше, чем допустимое на данном источнике. Вот и получается, что возможность написать SQL запрос и получить нужные данные есть, но написать придется не один запрос, а потом потратить время на объединение полученных данных.
Выйти из подобной ситуации поможет Excel.
Уверен, что ни для кого не секрет, что MS Excel имеет встроенный модуль VBA и надстройки, позволяющие подключаться к внешним источникам данных, то есть по сути является мощным инструментом для аналитики, а значит идеально подходит для решения подобных задач.
Для того чтобы обойти проблему, нам потребуется таблица с целевой выборкой, в которой содержатся идентификаторы, по которым можно достаточно корректно получить недостающую информацию (это может быть уникальный идентификатор, назовем его ID, или набор из данных, находящихся в разных столбцах), ПК с установленным MS Excel, и доступом к БД с недостающей информацией и, конечно, желание получить ту самую информацию.
Создаем в MS Excel книгу, на листе которой размещаем таблицу с идентификаторами, по которым будем в дальнейшем формировать запрос (если у нас есть уникальный идентификатор, для обеспечения максимальной скорости обработки таблицу лучше представить в виде одного столбца), сохраняем книгу в формате *.xlsm, после чего приступаем к созданию макроса.
Через меню «Разработчик» открываем встроенный VBA редактор и начинаем творить.
Sub job_sql() — Пусть наш макрос называется job_sql.
Пропишем переменные для подключения к БД, записи данных и запроса:
Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim sql As StringОпишем параметры подключения:
Объявим процедуру свойства, для присвоения значения:
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) & "'"В целях самоконтроля я обычно записываю сформированный макросом запрос, чтобы иметь возможность проверить его корректность и работоспособность, для этого добавим вот такую строчку:
Microsoft SQL Server поддерживает подключения к другим источникам данных OLE DB (как постоянные, так и прямые). При наличии постоянного подключения сервер называется связанным. Прямое подключение устанавливается для отправки одного запроса (распределенного запроса).
Одним из типов источников данных OLE DB, которые можно запрашивать через SQL Server подобным образом, являются книги Microsoft Excel. В этой статье описан синтаксис, который необходимо использовать при настройке источника данных Excel в качестве связанного сервера, а также синтаксис распределенного запроса к источнику данных Excel.
Дополнительная информация
Запрос источника данных Excel на связанном сервере
Вы можете использовать SQL Server Management Studio или Enterprise Manager, хранимую в системе процедуру, SQL-DMO (Объекты распределенного управления) или SMO (Управляющие объекты SQL Server) для настройки источника данных Excel в качестве связанного сервера SQL Server. (Объекты SMO поддерживаются только в Microsoft SQL Server 2005.) В каждом случае необходимо задать следующие четыре свойства:
Имя, которое необходимо использовать для связанного сервера.
Поставщик OLE DB, который будет использоваться для подключения.
Источник данных или полное имя пути и файла для рабочей книги Excel.
Строка провайдера, которая идентифицирует цель как рабочую книгу Excel. По умолчанию поставщик Jet ожидает базу данных Access.
Хранимая в системе процедура sp_addlinkedserver также требует свойство @srvproduct, которое может быть любым строковым значением.
Заметка Если вы используете SQL Server 2005, то для свойства Имя продукта в SQL Server Management Studio или для свойства @srvproduct в хранимой процедуре для источника данных Excel необходимо указать значение, которое не должно быть пустым.
Использование SQL Server Management Studio или Enterprise Manager для настройки источника данных Excel в качестве связанного сервера
SQL Server Management Studio (SQL Server 2005)
В SQL Server Management Studio разверните Серверные объекты в Обозреватель объектов.
Щелкните правой кнопкой мыши Связанные серверы, а затем щелкните Новый связанный сервер.
В левой панели выберите страницу Общие, а затем выполните следующие шаги:
В первом текстовом поле введите любое имя для связанного сервера.
Выберите опцию Другой источник данных.
В списке Поставщик выберите Microsoft Jet 4.0 OLE DB Provider.
В поле Имя продукта введите Excel для имени источника данных OLE DB.
В поле Источник данных введите полный путь и имя файла Excel.
В поле Строка поставщика введите Excel 8.0 для рабочей книги Excel 2002, Excel 2000 или Excel 97.
Нажмите OK, чтобы создать новый связанный сервер.
Примечание В SQL Server Management Studio невозможно развернуть имя нового связанного сервера для просмотра списка объектов, содержащихся на сервере.
Enterprise Manager (SQL Server 2000)
В менеджере Enterprise Manager щелкните, чтобы развернуть папку Безопасность.
Щелкните правой кнопкой мыши Связанные серверы, а затем щелкните Новый связанный сервер.
На вкладке Общие выполните следующие действия:
В первом текстовом поле введите любое имя для связанного сервера.
В поле Тип сервера нажмите Другой источник данных.
В списке Имя поставщика нажмите кнопку Microsoft Jet 4.0 OLE DB Provider.
В поле Источник данных введите полный путь и имя файла Excel.
В поле Строка поставщика введите Excel 8.0 для рабочей книги Excel 2002, Excel 2000 или Excel 97.
Нажмите OK, чтобы создать новый связанный сервер.
Щелкните имя связанного сервера, чтобы развернуть список объектов, которые он содержит.
Под новым именем связанного сервера нажмите Таблицы. В правой области появятся книги и именованные диапазоны.
Использование хранимой процедуры для настройки источника данных Excel в качестве связанного сервера
Вы также можете использовать хранимую в системе процедуру sp_addlinkedserver для настройки источника данных Excel в качестве связанного сервера:
Как уже отмечалось выше, для данной хранимой процедуры требуется дополнительное произвольное значение строки для аргумента @srvproduct, которое отображается в виде "Имени продукта" в конфигурации Enterprise Manager и SQL Server Management Studio. Аргументы @location и @catalog не используются.
Использование SQL-DMO для настройки источника данных Excel в качестве связанного сервера
Объекты распределенного управления SQL можно использовать для настройки источника данных Excel в качестве связанного сервера программно с использованием Microsoft Visual Basic или другого языка программирования. Необходимо указать те же четыре аргумента, которые требуются при настройке через Enterprise Manager и SQL Server Management Studio.
Использование SMO для настройки источника данных Excel в качестве связанного сервера
Запрос источника данных Excel на связанном сервере
После настройки источника данных Excel в качестве связанного сервера, вы можете легко запросить его данные из Query Analyzer или другого клиентского приложения. Например, чтобы получить строки данных, которые хранятся на листе Sheet1 файла Excel, используйте через SQL-DMO следующий код для настроенного связанного сервера:
Кроме того, можно использовать OPENQUERY для "транзитного" запроса связанного сервера Excel:
Первый аргумент, который требуется OPENQUERY, — это имя связанного сервера. Чтобы указать имена листов, используйте разделители, как показано выше.
Кроме того, можно получить список всех таблиц, доступных на связанном сервере Excel, с помощью следующего запроса:
Запрос источника данных Excel с помощью распределенных запросов
Можно использовать распределенные запросы SQL Server и функцию OPENDATASOURCE или OPENROWSET для специальных запросов к редко обращающимся источникам данных Excel.
Заметка Если вы используете SQL Server 2005, убедитесь, что вы включили опцию Ad Hoc Distributed Queries, используя Настройка контактной зоны SQL Server, как в следующем примере:
Обратите внимание на необычный синтаксис второго аргумента OPENROWSET ("Строка поставщика"):
Синтаксис, привычный для разработчиков ADO, выглядит следующим образом:
Этот синтаксис вызывает следующую ошибку поставщика Jet:
Невозможно найти устанавливаемый ISAM.
Примечание Эта ошибка также возникает, если вместо ИсточникДанных ввести Источник данных. Например, следующий аргумент является неправильным:
Ссылки
Так как для связанных серверов SQL Server и распределенных запросов используется поставщик OLE DB, учитывайте общие рекомендации и предупреждения, которые относятся к применению ADO с Excel.
Дополнительные сведения см. в следующей статье базы знаний Майкрософт:
257819 Как использовать ADO с данными из Visual Basic или VBA в Excel.
Для получения дополнительной информации об управляющих объектах SQL Server (SMO) посетите следующий веб-сайт MSDN:
Для работы с Excel 2003 (.Xls) можно использовать провайдер Microsoft Jet OLE DB 4.0.
Для работы с Excel 2007 (.Xlsx) - Microsoft ACE OLEDB 12.0.
В Windows 10 открыть настройки источников данных ODBC можно написав "Источники данных ODBC" или через Панель управления \ Администрирование.
- HDR=YES|NO . HDR=YES означает, что первую строку листа, следует рассматривать как заголовки колонок. Т.о. значение из первой строки можно использовать как имена полей в sql запросах (любых: select, insert, update, delete).
- IMEX=1|3 . 1 - открыть соединение для чтения. 3 - для записи.
Создание Linked Server в Sql Server для доступа к Excel
После создания связанного сервера можно будет просмотреть имена доступных листов.
Затем, чтобы обратиться к сервису:
SELECT * FROM OPENQUERY (XLSX_2010, 'Select * from [Sheet1$]') или SELECT * FROM [XLSX_2010]. [Лист1$]
Обращение к лиcтам, диапазонам, полям
Для обращения к листу из SQL запроса нужно использовать имя листа, например: [Sheet1$] или [Лист1$] . Обращение к диапазону: [Sheet1$A16:F16] .
Вставка данных в произвольное место
Примеры указания диапазона при вставке строк ( insert )
- [table1$B4:E20]
- [table1$S4:U]
- [table1$F:G]
При вставке должны выполняться следующие условия:
- Первая строчка указанного диапазона дожна входить в диапазон ячеек с данными. Чтобы создать на листе диапазон с данными достаточно в углах некоторого прямоугольного диапазона (в левом верхнем и правом нижнем) вписать значение (C4:I7 на скриншоте). Т.е. сама первая строчка указанного в insert диапазона данные содержать не обязана, достаточно, чтобы она просто входила в такой диапазон. Иначе возникнет ошибка "This table contains cells that are outside the range of cells defined in this spreadsheet"
- Хвост диапазона должен содержать пустые строки (хотя бы одну).
Пример: Дан лист, где заполнены только 2 ячейки: C4, I7. После выполнения команды INSERT INTO [table1$E6:G] VALUES(2, 'FF','2014-01-03') результат будет как на скриншоте. Поясним: строка E6:G6 является первой строкой диапазона для вставки. Она входит в заполненный диапазон C4:I7. Поэтому данные были вставлены на следующей пустой строке - 8. Из этого примера становится ясно, что через OleDb нельзя работать с несколькими независимыми диапазонами на одном листе, используя вставку ( update будет работать).
- System.Data.OleDb.OleDbException (0x80004005): Operation must use an updateable query . Соединение открыто для чтение, при этом происходит попытка внести изменения (выполнить insert, update или delete). Решение: открыть соединение для записи, установив свойство провайдера в строке соединения IMEX=3 (см. выше).
- System.Data.OleDb.OleDbException (0x80004005): "This table contains cells that are outside the range of cells defined in this spreadsheet" . Такая ошибка возникает при подпытке обновить ( update ) или вставить ( insert ) значения в диапазоне, в котором отсутствуют данные на листе.
- Если нужно произвести запись в определенные ячейки инструкцией update, то
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters
This comment has been minimized.
Copy link Quote reply
Spirit412 commented Sep 9, 2019
Спасибо за код. Пригодился.
Не подскажите ли мне, как узнать номер строки у ячейки? Мне нужно циклами SQL обойти таблицу, которая начинается с именованной ячейки.
Значение самой ячейки я получаю так
Set rs = objConn.execute("SELECT * FROM CustomerName")
Т.е. в rs записываю значение из ячейки с именем CustomerName.
Но как мне в переменную записать номер строки этой ячейки?
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: 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 предложит сохранить изменения запроса – отвечаем положительно.
Читайте также: