Как подключить sql к excel
Обновлено: 04.07.2024
Вы можете подключить Excel к базе данных, а затем импортировать данные и создать таблицы и диаграммы на основе значений в базе данных. Работая с этим руководством, вы установите подключение между Excel и таблицей базы данных, сохраните файл, в котором хранятся данные и сведения о соединении для Excel, а затем создадите сводную диаграмму на основе значений базы данных.
Перед началом работы вам необходимо создать базу данных. Если у вас его нет, см. разделы Создание базы данных в базе данных SQL Azure и Создание брандмауэра IP на уровне сервера, чтобы получить базу данных с образцами данных и запустить ее за несколько минут.
Следуя инструкциям в этой статье, вы импортируете демонстрационные данные в Excel, но те же действия можно выполнять и с собственными данными.
Вам также понадобится копия Excel. В этой статье используется Microsoft Excel 2016.
Подключите Excel и загрузите данные
Чтобы подключить Excel к базе данных в базе данных SQL, откройте Excel, а затем создайте новую книгу или откройте существующую книгу Excel.
В строке меню в верхней части страницы выберите вкладку Данные, нажмите кнопку Получить данные, выберите пункт "Из Azure", а затем — пункт Из базы данных SQL Azure.
В зависимости от вашей сетевой среды вы не сможете подключиться или потеряете подключение, если сервер не разрешает трафик с IP-адреса вашего клиента. Перейдите на портал Azure, щелкните "Серверы SQL Server", выберите свой сервер, в разделе "Параметры" щелкните "Брандмауэр" и добавьте IP-адрес клиента. Дополнительные сведения см. в статье Настройка правила брандмауэра уровня сервера базы данных SQL Azure с помощью портала Azure.
В навигаторе выберите в списке нужную базу данных, выберите нужные таблицы или представления (мы выбрали vGetAllCategories), а затем нажмите кнопку Загрузить, чтобы перенести данные из базы данных в электронную таблицу Excel.
Импорт данных в Excel и создание сводной диаграммы
Теперь, когда вы установили подключение, вы можете загрузить данные несколькими способами. Например, следующие шаги создают сводную диаграмму на основе данных, найденных в вашей базе данных в базе данных SQL.
Выполните действия из предыдущего раздела, но на этот раз не нажимайте кнопку Загрузить, а выберите пункт Загрузить в из раскрывающегося списка Загрузить.
Затем выберите способ представления данных в книге. Мы выбрали режим Сводная диаграмма. Кроме того, можно создать новый лист или добавить эти сведения в модель данных. Дополнительные сведения о моделях данных см. в статье Создание модели данных в Excel.
Лист теперь содержит пустую сводную таблицу и диаграмму.
В разделе Поля сводной таблицы установите все флажки для полей, которые требуется просмотреть.
Если нужно подключить другие книги и листы Excel к базе данных, на вкладке Данные нажмите кнопку Последние источники, чтобы открыть диалоговое окно Последние источники. Выберите в списке ранее созданное подключение и нажмите кнопку Открыть.
Создание постоянного подключения с помощью файла ODC
Чтобы сохранить сведения о подключении, можно создать файл ODC. После этого подключение можно будет выбирать в диалоговом окне Существующие подключения.
В строке меню в верхней части страницы выберите вкладку Данные и нажмите кнопку Существующие подключения, чтобы открыть диалоговое окно Существующие подключения.
Выберите файл +Новое подключение к SQL-серверу.odc и нажмите кнопку Открыть, чтобы запустить мастер подключения к данным.
В мастере подключения к данным введите имя сервера и учетные данные для базы данных SQL. Выберите Далее.
В раскрывающемся списке выберите нужную базу данных.
Выберите нужную таблицу или представление. Мы выбрали vGetAllCategories.
Выберите Далее.
На следующем экране мастера подключения к данным выберите расположение файла, имя файла и понятное имя. Вы также можете сохранить пароль в файле, но это может сделать данные уязвимыми для несанкционированного доступа. По завершении нажмите кнопку Готово.
Выберите способ импорта данных. Мы выбрали создание сводной таблицы. Кроме того, можно изменить свойства подключения, нажав кнопку Свойства. По окончании нажмите кнопку ОК. Если вы не сохранили пароль в файле, вам будет предложено ввести учетные данные.
Проверьте, сохранилось ли новое подключение. Для этого на вкладке Данные нажмите кнопку Существующие подключения.
Microsoft SQL Server позволяет встроенными средствами языка T-SQL в SQL запросе импортировать данные из файла Excel в базу данных. Сегодня я подробно расскажу, как это делается, какие условия необходимо выполнить, чтобы эта операция проходила успешно, расскажу про особенности импорта для самых распространённых случаев конфигураций SQL сервера и приведу конкретный порядок действий и практические примеры.
Начну я с того, что импортировать данные из Excel в Microsoft SQL Server можно с помощью «Распределенных запросов» и с помощью «Связанных серверов». Это, скорей всего, Вы уже знаете, так как я уже не раз писал об этом (ссылки на соответствующие материалы указаны чуть выше).
Обратиться к файлу Excel и импортировать данные в Microsoft SQL Server можно с помощью T-SQL инструкций OPENDATASOURCE, OPENROWSET или OPENQUERY.
Однако в вышеупомянутых статьях я упустил несколько важных моментов, одним из которых является то, что у всех конфигурация SQL сервера разная, за счет чего у многих возникают различные проблемы и появляются ошибки во время выполнения распределенных запросов и обращений к связанным серверам. Также я описывал способ загрузки данных из Excel, который на сегодняшний день уже устарел, поэтому сегодня я постараюсь дать Вам немного больше информации о том, как импортировать данные из файла Excel в Microsoft SQL Server на языке T-SQL.
Введение
Итак, как я уже сказал, очень важную роль здесь играет конфигурация SQL сервера, в частности, какая версия сервера установлена, x86 или x64.
Если говорить о последних версиях Microsoft SQL Server 2016-2019, то они только x64 и устанавливаются на 64-разрядные версии Windows.
Исходя из этого я разделю статью на несколько частей, в каждой из которых я расскажу про особенности импорта данных из Excel для самых распространённых случаев конфигураций и приведу конкретный порядок действий.
Для того чтобы быстро узнать, какая версия SQL Server установлена у Вас на компьютере, можете выполнить простой SQL запрос
Обращение к файлу Excel и, соответственно, импорт данных в Microsoft SQL Server происходит с помощью специальных провайдеров (поставщиков). Для работы с Excel в Microsoft SQL Server обычно используются:
Во всех примерах ниже я буду посылать простой запрос SELECT на выборку данных из Excel файла, для того чтобы проверить доступ к данным в файле Excel. Чтобы осуществить импорт данных (загрузить данные в БД), Вы можете использовать любой удобный для Вас способ, например, конструкцию SELECT INTO или INSERT INTO.
Дополнительно рекомендовано закрывать файл Excel во время обращения к нему в распределенных запросах, а также указывать путь к файлу без пробелов (хотя современный SQL сервер умеет работать с пробелами).
Импорт данных из Excel 2003 (файл xls) в Microsoft SQL Server x86
Шаг 1 – Проверяем наличие провайдера Microsoft.Jet.OLEDB.4.0 на SQL Server
В результирующем наборе данных должна присутствовать строка с Microsoft.Jet.OLEDB.4.0. Если такого провайдера нет, то скорей всего в системе нет установленного Excel 2003 и, соответственно, его нужно установить.
Шаг 2 – Предоставление прав пользователю на временный каталог
Особенностью распределённых запросов и работы со связанным серверами Excel в x86 версиях SQL Server является то, что независимо от имени какой учетной записи посылается SQL запрос к Excel, эта учетная запись должна иметь права на запись во временный каталог той учетной записи, под которой работает сама служба SQL Server.Так как поставщик OLE DB создает временный файл во время запроса во временном каталоге SQL Server, используя учетные данные пользователя, выполняющего запрос.
Таким образом, если служба SQL Server работает от имени или локальной, или сетевой службы, необходимо дать соответствующие права на временный каталог этих служб всем пользователям, которые будут посылать распределенные запросы и обращаться к связанному серверу Excel (если сервер работает от имени пользователя, который посылает SQL запросы, то такие права давать не требуется, они у него уже есть).
Это можно сделать с помощью встроенной утилиты командной строки icacls.
Например, для локальной службы команда будет выглядеть следующим образом.
Для сетевой службы
Вместо UserName укажите имя пользователя, который посылает запрос.
Шаг 3 – Включаем распределенные запросы на SQL Server
По умолчанию возможность использования распределённых запросов, в частности функций OPENDATASOURCE и OPENROWSET, в Microsoft SQL Server запрещена, поэтому данную возможность нужно сначала включить.
Она включается с помощью системной хранимой процедуры sp_configure, которая отвечает за системные параметры сервера. Нам необходимо параметру Ad Hoc Distributed Queries присвоить значение 1, для этого выполняем следующую SQL инструкцию.
Шаг 4 – Выполняем SQL запрос, обращение к файлу Excel
Ниже я приведу несколько вариантов обращения к файлу Excel (TestExcel.xls).
OPENROWSET
OPENDATASOURCE
Linked Server
Импорт данных из Excel 2007 и выше (файл xlsx) в Microsoft SQL Server x86
Шаг 1 – Проверяем наличие провайдера Microsoft.ACE.OLEDB.12.0 на SQL Server
Точно так же, как и в предыдущем примере, сначала проверяем, установлен ли у нас необходимый нам провайдер, в данном случае нам нужен Microsoft.ACE.OLEDB.12.0.
Шаг 2 – Установка провайдера Microsoft.ACE.OLEDB.12.0 (32-bit)
Если провайдера нет, то его необходимо установить.
Вот ссылка на скачивание провайдера
Выберите и скачайте файл, соответствующий архитектуре x86 (т.е. в названии без x64).
Шаг 3 – Предоставление прав пользователю на временный каталог
В данном случае также даем права на временный каталог локальной или сетевой службы всем пользователям, которые будут посылать SQL запросы к файлу Excel.
Используем все ту же утилиту командной строки icacls.
Для локальной службы
Вместо UserName укажите имя пользователя, который посылает запрос.
Шаг 4 – Включаем распределенные запросы на SQL Server
Включаем возможность использования OPENDATASOURCE и OPENROWSET на Microsoft SQL Server, повторюсь, что по умолчанию данная возможность отключена.
Шаг 5 – Настройка провайдера Microsoft.ACE.OLEDB.12.0
В данном случае дополнительно потребуется настроить провайдер Microsoft.ACE.OLEDB.12.0. Для этого включим следующие параметры провайдера (для отключения укажите 0 вместо 1).
Если не включать данные параметры, то, скорей всего, появится ошибка примерно следующего содержания
Шаг 6 – Выполняем SQL запрос, обращение к файлу Excel
Примеры обращения к файлу Excel (TestExcel.xlsx).
OPENROWSET
OPENDATASOURCE
Linked Server
Импорт данных из Excel (любые файлы) в Microsoft SQL Server x64
Шаг 1 – Проверяем наличие провайдера Microsoft.ACE.OLEDB.12.0 на SQL Server
Шаг 2 – Установка провайдера Microsoft.ACE.OLEDB.12.0 (64-bit)
В случае, если провайдер не установлен, его необходимо скачать и установить.
Скачиваем файл x64.
Шаг 3 – Включаем распределенные запросы на SQL Server
Необходимость включения возможности использования распределенных запросов (OPENDATASOURCE и OPENROWSET) на Microsoft SQL Server x64 также есть, поэтому сначала включаем ее, выполнив точно такую же инструкцию.
Шаг 4 – Настройка провайдера Microsoft.ACE.OLEDB.12.0
Шаг 5 – Выполняем SQL запрос, обращение к файлу Excel
Здесь используются точно такие же параметры в SQL запросах, что и в предыдущем примере. Для удобства продублирую их еще раз.
Примеры обращения к файлу Excel (TestExcel.xlsx).
OPENROWSET
OPENDATASOURCE
Linked Server
Подведение итогов
Ну и в заключение я сгруппирую действия, которые необходимо выполнять в зависимости от выпуска SQL Server (x68 или x64) и версии файла Excel (xls или xlsx), в одну таблицу, для Вашего удобства.
| Действие / Настройка | Импорт Excel 2003 (файл xls) в SQL Server x86 | Импорт Excel 2007 (файл xlsx) в SQL Server x86 | Импорт Excel (любые файлы) в SQL Server x64 |
| Установка Excel 2003 | Да | Нет | Нет |
| Установка провайдера Microsoft.ACE.OLEDB.12.0 | Нет | Да (x86) | Да (x64) |
| Предоставление прав на временный каталог служб (если SQL сервер работает от имени служб) | Да | Да | Нет |
| Настройка провайдера Microsoft.ACE.OLEDB.12.0 | Нет | Да | Нет (по необходимости) |
| Параметры подключения в SQL запросах | Microsoft.Jet. OLEDB.4.0 и Excel 8.0 | Microsoft.ACE. OLEDB.12.0 и Excel 12.0 | Microsoft.ACE. OLEDB.12.0 и Excel 12.0 |
| Включение распределённых запросов на SQL Server | Да | Да | Да |
Заметка! Для комплексного изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL.
В Microsoft SQL Server существует возможность в SQL запросах, на языке T-SQL, запрашивать данные из внешних источников, сегодня мы рассмотрим несколько способов обращения к файлу Excel из запроса с целью получения данных.
В языке T-SQL это можно реализовать с помощью так называемых «Распределенных запросов», которые предполагают использование следующих конструкций:
- OPENDATASOURCE
- OPENROWSET
- OPENQUERY
Сейчас мы рассмотрим все эти конструкции, и напишем несколько примеров.
Примечание! Загрузить данные из Excel можно и без использования языка T-SQL, в Microsoft SQL Server разработаны специальные службы SSIS (SQL Server Integration Services), в частности реализован инструмент по загрузке данных из разных источников «Импорт и экспорт данных», который позволяет загружать данные, уже не прибегая к использованию языка T-SQL.
Исходные данные и требования
В качестве примера давайте представим, что нам нужно обратиться к файлу «TestExcel.xls», который расположен на диске D. Сами данные расположены на листе со стандартным названием «Лист1», они имеют следующую структуру.
| ProductId | CategoryId | ProductName | Price |
| 1 | 1 | Клавиатура | 100 |
| 2 | 1 | Мышь | 50 |
| 3 | 2 | Телефон | 300 |
Для того чтобы начать пользоваться распределенными запросами, нам сначала нужно включить данную возможность на SQL сервере, так как по умолчанию она выключена.
Включается возможность использования распределённых запросов с помощью системной хранимой процедуры sp_configure, которая отвечает за системные параметры сервера. Нам необходимо параметру Ad Hoc Distributed Queries присвоить значение 1, для этого выполните следующую SQL инструкцию.
Параметром show advanced options мы отображаем дополнительные параметры системной хранимой процедуры sp_configure, а командой RECONFIGURE мы применяем новые параметры.
Функция OPENDATASOURCE в T-SQL
OPENDATASOURCE – функция возвращает ссылку на источник данных, который может использоваться как часть четырехсоставного имени объекта.
Как видите, в функцию мы передали имя поставщика и строку подключения, а потом обратились к источнику, используя четырехсоставное имя объекта, т.е. к таблице на листе 1.
Функция OPENROWSET в T-SQL
OPENROWSET – функция подключается к источнику данных и выполняет необходимый запрос.
У данной функции уже много параметров, с помощью которых мы указываем все необходимые сведения о подключении, которые требуются для доступа к источнику данных OLE DB.
Для того чтобы обратиться к Excel, можно написать следующий запрос.
Вместо [Лист1$] можно в апострофах указать необходимый SQL запрос, например:
Функция OPENQUERY в T-SQL
OPENDATASOURCE и OPENROWSET удобно использовать, если Вам требуется разово получить данные из определённых внешних источников, в нашем случае из Excel, но если Вам требуется постоянно обращаться к таким источникам, то имеет смысл создать специальный объект в SQL Server – он называется «Связанный сервер» (Linked Server). Данный объект будет настроен на конкретный источник, и к этому объекту Вы сможете обращаться практически как к обычной базе данных, не указывая при этом строку подключения.
Связанный сервер настраивается один раз, иными словами, при его создании Вы указываете все необходимые настройки, а затем просто обращаетесь к нему по имени, которое Вы укажете.
Для обращения к связанным серверам в SQL сервере как раз и используется функция OPENQUERY.
OPENQUERY – функция обращается к связанному серверу и выполняет указанный запрос. На эту функцию можно даже ссылаться в инструкциях по модификации данных, т.е. мы можем изменять данные на связанном сервере.
Связанный сервер сначала нужно создать, это можно сделать с помощью системной процедуры sp_addlinkedserver, доступ к данному серверу настраивается процедурой sp_addlinkedsrvlogin. Связанные серверы также можно создавать и с помощью среды SQL Server Management Studio (Обозреватель объектов-> Объекты сервера-> Связанные серверы).
Более подробно про связанные серверы можете почитать в материале «Связанные серверы в MS SQL Server – создание и примеры использования».
После того как связанный сервер создан, в моем примере я его назвал TEST_EXCEL, мы можем к нему обратиться с помощью функции OPENQUERY.
Также к связанному серверу можно обратиться и без использования функции OPENQUERY, например:
Но рекомендовано к связанным серверам обращаться через функцию OPENQUERY.
Как я уже отмечал, функцию OPENQUERY можно использовать и для модификации данных на связанном сервере в инструкциях INSERT, UPDATE или DELETE, например, для обновления данных, в нашем случае инструкция будет выглядеть следующем образом.
Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.
В этой статье показано, как подключаться к источникам данных Microsoft Excel со страницы Выбор источника данных или Выбор назначения в мастере импорта и экспорта SQL Server.
На следующем снимке экрана показан пример подключения к книге Microsoft Excel.
Для подключения к файлам Excel может потребоваться скачать и установить дополнительные файлы. Дополнительные сведения см. в разделе Получение файлов, необходимых для подключения к Excel.
Дополнительные сведения о подключении к файлам Excel, а также об ограничениях и известных проблемах, связанных с загрузкой данных в файлы этого приложения и из них, см. в разделе Загрузка данных в приложение Excel или из него с помощью служб SQL Server Integration Services (SSIS).
Указываемые параметры
Параметры подключения для этого поставщика данных одинаковы независимо от того, является ли Excel источником или назначением. Таким образом, на страницах Выбор источника данных и Выбор назначения мастера отображаются одинаковые параметры.
Путь к файлу Excel
Укажите полный путь и имя для файла Excel. Пример:
- Для файла на локальном компьютере C:\MyData.xlsx.
- Для файла в общей сетевой папке \\Sales\Database\Northwind.xlsx.
или нажмите Обзор.
Обзор
Выберите электронную таблицу с помощью диалогового окна Открыть.
Мастер не может открыть защищенный паролем файл Excel.
Версия Excel
Выберите версию Excel для исходной или целевой рабочей книги.
Первая строка содержит имена столбцов
Укажите, содержит ли первая строка данных имена столбцов.
- Если данные содержат имена столбцов, но этот параметр включен, мастер рассматривает первую строку исходных данных как имена столбцов.
- Если данные содержат имена столбцов, но этот параметр отключен, мастер рассматривает строку имен столбцов как первую строку данных.
Если указать, что в данных отсутствуют имена столбцов, мастер внутренним образом использует F1, F2 и т. д. в качестве таких заголовков.
Excel не отображается в списке источников данных
Если вы не видите Excel в списке источников данных, определите, не используете ли вы 64-разрядный мастер? Поставщики для Excel и Access обычно 32-разрядные и поэтому не отображаются в 64-разрядном мастере. Запустите 32-разрядный мастер.
Чтобы использовать 64-разрядную версию мастера экспорта и импорта SQL Server, нужно установить SQL Server. SQL Server Data Tools (SSDT) и SQL Server Management Studio (SSMS) являются 32-разрядными приложениями и устанавливают только 32-разрядные файлы, включая 32-разрядную версию мастера.
Читайте также: