Использование Excel со связанными серверами SQL Server и распределенными запросами
Аннотация
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 в качестве связанного сервера
В SQL Server 2005 можно использовать управляющие объекты SQL Server (SMO) для программной настройки источника данных Excel в качестве связанного сервера. Для этого применяется Microsoft Visual Basic .NET или другой язык программирования. Необходимо указать те же аргументы, которые требуются при настройке через SQL Server Management Studio. Объектная модель SMO расширяет и заменяет объектную модель SQL-DMO. Так как модель SMO совместима с SQL Server 7.0, SQL Server 2000 и SQL Server 2005, ее также можно использовать для настройки SQL Server 2000.
Запрос источника данных 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:
Как запустить SQL-запрос в таблице Excel?
Я пытаюсь создать под-таблицу из другой таблицы всех полей фамилии, отсортированных A-Z, которые имеют поле номера телефона, которое не является нулевым. Я мог бы сделать это довольно легко с SQL, но я понятия не имею, как запустить SQL-запрос в Excel. У меня возникает соблазн импортировать данные в postgresql и просто запросить их там, но это кажется немного чрезмерным.
для того, что я пытаюсь сделать, SQL query SELECT lastname, firstname, phonenumber WHERE phonenumber IS NOT NULL ORDER BY lastname будет делать трюк. Это кажется слишком простым, чтобы быть чем-то. что Excel не может изначально. Как я могу запустить SQL-запрос из Excel?
11 ответов
есть много прекрасных способов сделать это, которые другие уже предложили. Следуя вдоль "получить данные Excel через SQL track", вот некоторые указатели.
Excel имеет "мастер подключения к данным", который позволяет импортировать или связать из другого источника данных или даже в том же файле Excel.
в составе Microsoft Office (и ОС) есть два интересующих поставщика: старый " Microsoft.Реактивный.OLEDB", и последний "Microsoft.ТУЗ.Для oledb". Ищите их при настройке соединения (например, с помощью мастера подключения к данным).
после подключения к книге Excel рабочий лист или диапазон эквивалентны таблице или представлению. Имя таблицы рабочего листа — это имя рабочего листа со знаком доллара ( " $ " ), добавленным к нему, и окруженным квадратными скобками ("[" и "]"); диапазона, это просто имя диапазона. Чтобы указать неназванный диапазон ячеек в качестве источника записей, добавьте стандартные обозначения строк/столбцов Excel в конец имени листа в квадратных скобках.
собственный SQL будет (более или менее) SQL Microsoft Access. (В прошлом это называлось JET SQL; однако Access SQL эволюционировал, и я считаю, что JET является устаревшим old tech.)
пример чтения рабочего листа: выберите * из [Sheet1$]
пример, чтение диапазона: выберите * из Миранж!—3—>
пример чтения неназванного диапазона ячеек: выберите * из [Sheet1$A1: B10]
есть много много много книг и веб-сайтов, доступных, чтобы помочь вам работать через детали.
по умолчанию предполагается, что первая строка источника данных Excel содержит заголовки столбцов, которые могут использоваться как имена полей. Если это не так, вы должны включить этот параметр выкл., или ваша первая строка данных "исчезает" для использования в качестве имен полей. Это делается путем добавления необязательного параметра HDR= в расширенные свойства строки подключения. Значение по умолчанию, которое не нужно указывать, равно HDR=Yes. Если у вас нет заголовков столбцов, вам нужно указать HDR=No; поставщик называет ваши поля F1, F2 и т. д.
предупреждение об указании листов: поставщик предполагает, что ваша таблица данных начинается с самой верхней, самой левой, непустой ячейки на указанном листе. Другими словами, ваша таблица данных может начинаться в строке 3, столбце C без проблем. Однако нельзя, например, ввести заголовок листа выше и слева от данных в ячейке A1.
предупреждение об указании диапазонов: когда вы указываете лист в качестве источника записей, поставщик добавляет новые записи ниже существующих записей на листе, насколько позволяет пространство. При указании диапазона (именованного или неназванного) Jet также добавляет новые записи ниже существующих записи в диапазоне, как позволяет пространство. Однако при запросе исходного диапазона результирующий набор записей не включает вновь добавленные записи вне диапазона.
типы данных (стоит попробовать) для CREATE TABLE: Short, Long, Single, Double, Currency, DateTime, Bit, Byte, GUID, BigBinary, LongBinary, VarBinary, LongText, VarChar, Decimal.