Запрос sql в excel из access

Обновлено: 06.07.2024

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 могут обрабатывать большинство сценариев автоматизации Excel, запросы SQL могут более эффективно извлекать значительные объемы данных Excel и работать с ними.

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

Напротив, вы можете реализовать эту функциональность с помощью SQL-запросов, используя только два действия: действие Открыть SQL-подключение и действие Выполнять инструкции SQL.

Откройте SQL-подключение к файлу Excel

Перед запуском SQL-запроса вы должны открыть подключение с файлом Excel, к которому вы хотите получить доступ.

Чтобы установить подключение, создайте новую переменную с именем %Excel_File_Path% и инициализируйте его, указав путь к файлу Excel. При желании вы можете пропустить этот шаг и использовать жестко заданный путь к файлу позже в потоке.

Теперь разверните действие Открыть SQL-подключение и заполните следующую строку подключения в его свойствах.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%Excel_File_Path%;Extended Properties="Excel 12.0 Xml;HDR=YES";

Для успешного использования представленной строки подключения вам необходимо скачать и установить Распространяемый пакет ядра СУБД Microsoft Access 2010.

Откройте SQL-подключение к файлу Excel, защищенному паролем

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

Для этого запустите файл Excel с помощью действие Запустить Excel. Файл защищен паролем, поэтому введите соответствующий пароль в поле Пароль.

Затем разверните соответствующие действия автоматизации пользовательского интерфейса и перейдите к Файл > Информация > Защита книги > Зашифровать паролем. Дополнительные сведения об автоматизации пользовательского интерфейса и о том, как использовать соответствующие действия можно найти в Автоматизировать классические потоки.

После выбора Зашифровать паролем заполните пустую строку во всплывающем диалоговом окне, используя действие Заполнить текстовое поле в окнах. Чтобы заполнить пустую строку, используйте следующее выражение: %""%.

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

Наконец, разверните действие Закрыть Excel, чтобы сохранить незащищенную книгу как новый файл Excel.

После сохранения файла следуйте инструкциям в Открытие SQL-подключения к файлам Excel, чтобы открыть к нему подключение.

Когда работа с файлом Excel будут завершена, используйте действие Удалить файлы для удалению незащищенной копии файла Excel.

Чтение содержимого электронной таблицы Excel

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

Более эффективный способ получения определенных значений из электронных таблиц — это рассматривать файлы Excel как базы данных и выполнять на них SQL-запросы. Этот подход быстрее и увеличивает производительность потока.

Чтобы получить все содержимое электронной таблицы, вы можете использовать следующий SQL-запрос в действие Выполнять инструкции SQL.

Чтобы применить этот SQL-запрос в ваших потоках, замените заполнитель SHEET именем электронной таблицы, к которой вы хотите получить доступ.

Чтобы получить строки, содержащие определенное значение в определенном столбце, используйте следующий запрос SQL:

Чтобы применить этот SQL-запрос в ваших потоках, замените:

SHEET именем электронной таблицы, к которой вы хотите получить доступ
COLUMN NAME столбцом, содержащим значение, которое вы хотите найти
VALUE значением, которое вы хотите найти

Удалить данные из строки Excel

Хотя Excel не поддерживает SQL-запрос DELETE, вы можете использовать запрос UPDATE, чтобы установить для всех ячеек определенной строки значение NULL.

Точнее, вы можете использовать следующий SQL-запрос:

При разработке потока вы должны заменить заполнитель SHEET именем электронной таблицы, к которой вы хотите получить доступ.

Заполнители COLUMN1 а также COLUMN2 представляют имена всех существующих столбцов. В этом примере столбцов два, но в реальном сценарии количество столбцов может быть другим.

Часть запроса [COLUMN1]='VALUE' определяет строку, которую вы хотите обновить. В вашем потоке используйте имя столбца и значение в зависимости от того, какая комбинация однозначно описывает строки.

Получить данные Excel, кроме определенной строки

В некоторых сценариях может потребоваться получить все содержимое электронной таблицы Excel, кроме определенной строки.

Удобный способ добиться этого результата — установить для значений нежелательной строки значение NULL, а затем получить все значения, кроме нулевых.

Чтобы изменить значения определенной строки в электронной таблице, вы можете использовать SQL-запрос UPDATE, представленный в Удалить данные из строки Excel:

Затем выполните следующий SQL-запрос, чтобы получить все строки электронной таблицы, не содержащие значений NULL:

Заполнители COLUMN1 а также COLUMN2 представляют имена всех существующих столбцов. В этом примере столбцов два, но в реальной таблице количество столбцов может быть другим.

Представьте себе ситуацию, Вы получили целевую выборку из одной базы данных, но для полноты картины, как всегда, нужны дополнительные данные. Проблема может быть в том, что нужная информация хранится в другой базе данных и возможности создать на ней свою таблицу нет, подключиться используя 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) & "'"

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

Вы можете импортировать данные из различных поддерживаемых баз данных. Вы также можете выполнить запрос на языке базы данных, чтобы сэкономить время, необходимое для создания одинаковых результатов в Power Query.

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

При подключении к базе данных можно указать SQL в поле Заявление о SQL базы данных. В примере ниже мы импортируем данные из SQL Server базы данных с помощью запроса на ее языке. Эта процедура аналогична процедуре для всех остальных поддерживаемых баз данных.

Выберите Данные> Получить данные > из баз данных > из SQL Server базы данных.Появится База данных SQL диалоговое окно.

Укажите сервер и базу данных, из которых вы хотите импортировать данные с помощью запроса на языке базы данных.

Выберите элемент Дополнительные параметры .

Введите запрос базы данных в поле SQL базы данных.

В зависимости от базы данных могут быть доступны дополнительные параметры.

Нажмите ОК.

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

Данные возвращаются в редактор Power Query.

Вы можете сформировать данные, а затем нажать кнопку Применить & Закрыть, чтобы сохранить изменения и импортировать данные на этот таблицу.

Примечание При импорте базы данных Access она не отображает SQL, но в окне Навигатор отображаются запросы. Запрос Access основан на SQL и вы можете выбрать его, чтобы указать SQL запроса.

Чтобы изменить содержимое SQL заявления:

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

Выберите запрос> изменить. Появится редактор Power Query.

В области Параметры запроса слева в области Примененные шаги щелкните правой кнопкой мыши шаг Источник и выберите изменить Параметры. Появится диалоговое окно Навигатор.

Внести изменения в поле выписки SQL, а затем выберите ОК.

Новые результаты появятся в области Предварительный просмотр данных.

Завершив редактирование запроса, выберите главная > Закрыть и загрузить.

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