Как подключить sql к excel
Мало пользователей, да и начинающих программистов, которые знают о возможности Excel подключаться к внешним источникам, и в частности к SQL серверу, для загрузки данных из этих источников. Эта возможность достаточно полезна, поэтому сегодня мы займемся ее рассмотрением.
Функционал Excel получения данных из внешних источников значительно упростит выгрузку данных с SQL сервера, так как Вам не придется просить об этом программиста, к тому же данные попадают сразу в Excel. Для этого достаточно один раз настроить подключение и в случае необходимости получать данные в Excel из любых таблиц и представлений Views, из базы настроенной в источнике, естественно таких источников может быть много, например, если у Вас несколько баз данных.
Задача для получения данных в Excel
И для того чтобы более понятно рассмотреть данную возможность, мы это будем делать как обычно на примере. Другими словами допустим, что нам надо выгрузить данные, одной таблицы, из базы SQL сервера, средствами Excel, т.е. без помощи вспомогательных инструментов, таких как Management Studio SQL сервера.
Примечание! Все действия мы будем делать, используя Excel 2010. SQL сервер у нас будет MS Sql 2008.
И для начала разберем исходные данные, допустим, есть база test, а в ней таблица test_table, данные которой нам нужно получить, для примера будут следующими:
Эти данные располагаются в таблице test_table базы test, их я получил с помощью простого SQL запроса select, который я выполнил в окне запросов Management Studio. И если Вы программист SQL сервера, то Вы можете выгрузить эти данные в Excel путем простого копирования (данные не большие), или используя средство импорта и экспорта MS Sql 2008. Но сейчас речь идет о том, чтобы простые пользователи могли выгружать эти данные.
Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.
Настройка Excel для получения данных с SQL сервера
Настройка, делается достаточно просто, но требует определенных навыков и консультации администратора SQL сервера. Вы, конечно, можете попросить программиста настроить Excel на работу или сделать это сами, просто спросив пару пунктов, а каких мы сейчас узнаем.
И первое что нам нужно сделать, это конечно открыть Excel 2010. Затем перейти на вкладку «Данные» и нажать на кнопку «Из других источников» и выбрать «С сервера SQL Server»
Затем у Вас откроется окно «Мастер подключения данных» в котором Вам необходимо, указать на каком сервере располагается база данных и вариант проверки подлинности. Вот именно это Вам придется узнать у администратора баз данных, а если Вы и есть администратор, то заполняйте поля и жмите «Далее».
- Имя сервера – это адрес Вашего сервера, здесь можно указывать как ip адрес так и DNS имя, в моем случае сервер расположен на этом же компьютере поэтому я и указал localhost;
- Учетные данные – т.е. это логин и пароль подключения к серверу, здесь возможно два варианта, первый это когда в сети Вашей организации развернута Active directory (Служба каталогов или домен), то в этом случае можно указать, что использовать те данные, под которыми Вы загрузили компьютер, т.е. доступы доменной учетки, и в этом случае никаких паролей здесь вводить не надо, единственное замечание что и на MSSql сервере должна стоять такая настройка по проверки подлинности. У меня именно так и настроено, поэтому я и выбрал этот пункт. А второй вариант, это когда администратор сам заводит учетные данные на SQL сервере и выдает их Вам, и в этом случае он должен их Вам предоставить.
Далее необходимо выбрать базу, к которой подключаться, в нашем примере это база test. Также это подключение можно настроить сразу на работу с определенной таблицей или представлением, список таблиц и представлений у Вас будет отображен, давайте мы сделаем именно так и настроем подключение сразу на нашу таблицу test_table. Если Вы не хотите этого, а хотите чтобы Вы подключались к базе и потом выбирали нужную таблицу, то не ставьте галочку напротив пункта «Подключаться к определенной таблице», а как я уже сказал, мы поставим эту галочку и жмем «Далее».
В следующем окне нам предложат задать имя файла подключения, название и описание, я например, написал вот так:
После того как Вы нажмете «Готово» у Вас откроется окно импорта этих данных, где можно указать в какие ячейки копировать данные, я например, по стандарту выгружу данные, начиная с первой ячейки, и жмем «ОК»:
В итоге у меня загрузятся из базы вот такие данные:
Т.е. в точности как в базе. Теперь когда, например, изменились данные в этой таблице, и Вы хотите выгрузить их повторно Вам не нужно повторять все заново, достаточно в excel перейти на вкладку «Данные» нажать кнопку «Существующие подключения» и выбрать соответствующее, т.е. то которое Вы только что создали.
Вот собственно и все, как мне кажется все достаточно просто.
Таким способом получать данные в Excel из базы SQL сервера очень удобно и главное быстро, надеюсь, Вам пригодятся эти знания полученные в сегодняшнем уроке. Удачи!
В этой статье показано, как подключаться к источникам данных 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-разрядную версию мастера.
Хотя действия 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 столбцом, содержащим значение, которое вы хотите найти. Столбцы в первой строке листа Excel идентифицируются как имена столбцов таблицы.
- VALUE значением, которое вы хотите найти
Удалить данные из строки Excel
Хотя Excel не поддерживает SQL-запрос DELETE, вы можете использовать запрос UPDATE, чтобы установить для всех ячеек определенной строки значение NULL.
Точнее, вы можете использовать следующий SQL-запрос:
При разработке потока вы должны заменить заполнитель SHEET именем электронной таблицы, к которой вы хотите получить доступ.
Заполнители COLUMN1 а также COLUMN2 представляют имена всех существующих столбцов. В этом примере столбцов два, но в реальном сценарии количество столбцов может быть другим. Столбцы в первой строке листа Excel идентифицируются как имена столбцов таблицы.
Часть запроса [COLUMN1]='VALUE' определяет строку, которую вы хотите обновить. В вашем потоке используйте имя столбца и значение в зависимости от того, какая комбинация однозначно описывает строки.
Получить данные Excel, кроме определенной строки
В некоторых сценариях может потребоваться получить все содержимое электронной таблицы Excel, кроме определенной строки.
Удобный способ добиться этого результата — установить для значений нежелательной строки значение NULL, а затем получить все значения, кроме нулевых.
Чтобы изменить значения определенной строки в электронной таблице, вы можете использовать SQL-запрос UPDATE, представленный в Удалить данные из строки Excel:
Затем выполните следующий SQL-запрос, чтобы получить все строки электронной таблицы, не содержащие значений NULL:
Заполнители COLUMN1 а также COLUMN2 представляют имена всех существующих столбцов. В этом примере столбцов два, но в реальной таблице количество столбцов может быть другим. Все столбцы в первой строке листа Excel идентифицируются как имена столбцов таблицы.
Вы можете подключить 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 поддерживает подключения к другим источникам данных 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:
Читайте также: