Как открыть sql в excel
Импорт данных SQL в Excel
Импорт данных SQL в Excel
Небольшой экскурс в MS SQL
Добрый день, уважаемые читатели и подписчики блога. Как вы уже догадались из названия статьи, речь сегодня пойдёт об импорте данных SQL в таблицу Excel.
Небольшое предисловие. Имеется база данных MS SQL, в которой содержится определённая таблица, её нужно загрузить в Excel.
Зачем использовать такую модель? Базы данных SQL могут быть объёмом гораздо больше чем файлы Excel и работают гораздо быстрее, а Excel используется как инструмент тонкой настройки загруженной таблицы.
Задача проста, но есть несколько моментов: необходимо знать, как называется инстанс (экземпляр) где находится база данных, естественно, нужно знать учётные данные, нужно знать название таблицы. Что ж, приступим.
Я предпочитаю в таких случаях использовать Microsoft Management Studio, для поиска основных данных. Найти ярлык можно в меню Пуск.
В консоли MS SQL есть несколько полей:
Далее следует имя сервера (его можно задать на этапе установки).
Откроется окно запроса.
Все запросы будут производиться на языке Transact SQL. У него очень много общего с синтаксисом SQL, но есть и много отличий.
Создаём базу данных
Если необходимо создать новую базу данных, например, магазины (SHOPS), синтаксис будет следующий:
CREATE DATABASE SHOPS
Обязательно после написания запроса нажать «Execute» или F5! В противном случае запрос не будет выполнен.
Создаём таблицу
CREATE TABLE dbo.Shops
(ShopID int PRIMARY KEY NOT NULL,
ShopName varchar(25) NOT NULL,
Revenue money NULL,
Manager text NULL)
GO
Нажать «Execute». Таблица создалась. Проверить можно раскрыв дерево базы данных и раскрыть группу «Tables».
Добавим в таблицу одну строчку (пока). Открываем окно нового запроса базе данных и добавляем запрос.
INSERT INTO dbo.Shops VALUES ( ‘1’, ‘Ручеёк’, ‘120000’, ‘Петров В.И.’)
Добавилась одна строка.
Импорт данных SQL в Excel
Со стороны Excel действий будет гораздо меньше. Переходим на вкладку «Данные», нажимаем кнопку «Получение внешних данных», «Из других источников», «С сервера SQL Server».
В следующем окне Excel предложит сохранить файл для подключения к базе данных с настройками. Нажать «Готово».
Теперь можно выбрать расположение загруженной таблицы и указать необходимые настройки. Я пока указывать их не буду, просто нажму «ОК». Таблица будет размещена в ячейку A1.
Цель достигнута! Таблица из базы данных перекочевала в Excel и теперь над ней можно совершать привычные действия.
Excel — Подключение и получение данных с SQL сервера
Мало пользователей, да и начинающих программистов, которые знают о возможности 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»
Затем у Вас откроется окно «Мастер подключения данных» в котором Вам необходимо, указать на каком сервере располагается база данных и вариант проверки подлинности. Вот именно это Вам придется узнать у администратора баз данных, а если Вы и есть администратор, то заполняйте поля и жмите «Далее».
Далее необходимо выбрать базу, к которой подключаться, в нашем примере это база test. Также это подключение можно настроить сразу на работу с определенной таблицей или представлением, список таблиц и представлений у Вас будет отображен, давайте мы сделаем именно так и настроем подключение сразу на нашу таблицу test_table. Если Вы не хотите этого, а хотите чтобы Вы подключались к базе и потом выбирали нужную таблицу, то не ставьте галочку напротив пункта «Подключаться к определенной таблице», а как я уже сказал, мы поставим эту галочку и жмем «Далее».
В следующем окне нам предложат задать имя файла подключения, название и описание, я например, написал вот так:
После того как Вы нажмете «Готово» у Вас откроется окно импорта этих данных, где можно указать в какие ячейки копировать данные, я например, по стандарту выгружу данные, начиная с первой ячейки, и жмем «ОК»:
В итоге у меня загрузятся из базы вот такие данные:
Т.е. в точности как в базе. Теперь когда, например, изменились данные в этой таблице, и Вы хотите выгрузить их повторно Вам не нужно повторять все заново, достаточно в excel перейти на вкладку «Данные» нажать кнопку «Существующие подключения» и выбрать соответствующее, т.е. то которое Вы только что создали.
Вот собственно и все, как мне кажется все достаточно просто.
Таким способом получать данные в Excel из базы SQL сервера очень удобно и главное быстро, надеюсь, Вам пригодятся эти знания полученные в сегодняшнем уроке. Удачи!
Выполнять SQL-запросы к файлам Excel
Хотя действия 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-запрос в ваших потоках, замените:
Удалить данные из строки 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 со связанными серверами 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 в качестве связанного сервера
Запрос источника данных 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?
Представьте себе ситуацию, Вы получили целевую выборку из одной базы данных, но для полноты картины, как всегда, нужны дополнительные данные. Проблема может быть в том, что нужная информация хранится в другой базе данных и возможности создать на ней свою таблицу нет, подключиться используя link тоже нельзя, да и количество элементов, по которым нужно получить данные, несколько больше, чем допустимое на данном источнике. Вот и получается, что возможность написать SQL запрос и получить нужные данные есть, но написать придется не один запрос, а потом потратить время на объединение полученных данных.
Выйти из подобной ситуации поможет Excel.
Уверен, что ни для кого не секрет, что MS Excel имеет встроенный модуль VBA и надстройки, позволяющие подключаться к внешним источникам данных, то есть по сути является мощным инструментом для аналитики, а значит идеально подходит для решения подобных задач.
Для того чтобы обойти проблему, нам потребуется таблица с целевой выборкой, в которой содержатся идентификаторы, по которым можно достаточно корректно получить недостающую информацию (это может быть уникальный идентификатор, назовем его ID, или набор из данных, находящихся в разных столбцах), ПК с установленным MS Excel, и доступом к БД с недостающей информацией и, конечно, желание получить ту самую информацию.
Создаем в MS Excel книгу, на листе которой размещаем таблицу с идентификаторами, по которым будем в дальнейшем формировать запрос (если у нас есть уникальный идентификатор, для обеспечения максимальной скорости обработки таблицу лучше представить в виде одного столбца), сохраняем книгу в формате *.xlsm, после чего приступаем к созданию макроса.
Через меню «Разработчик» открываем встроенный VBA редактор и начинаем творить.
Sub job_sql() — Пусть наш макрос называется job_sql.
Пропишем переменные для подключения к БД, записи данных и запроса:
Опишем параметры подключения:
Объявим процедуру свойства, для присвоения значения:
Вот теперь можно приступать непосредственно к делу.
Как вы уже поняли конечное значение i=1000 здесь только для примера, а в реальности конечное значение соответствует количеству строк в Вашей таблице. В целях унификации можно использовать автоматический способ подсчета количества строк, например, вот такую конструкцию:
Тогда открытие цикла будет выглядеть так:
Как я уже говорил выше MS Excel является мощным инструментом для аналитики, и возможности Excel VBA не заканчиваются на простом переборе значений или комбинаций значений. При наличии известных Вам закономерностей можно ограничить объем выгружаемой из БД информации путем добавления в макрос простых условий, например:
Итак, мы определились с объемом и условиями выборки, организовали подключение к БД и готовы формировать запрос. Предположим, что нам нужно получить информацию о размере ежемесячного платежа [Ежемесячный платеж] из таблицы [payments].[refinans_credit], но только по тем случаям, когда размер ежемесячного платежа больше 0
Если значений для формирования запроса несколько, соответственно прописываем их в запросе:
В целях самоконтроля я обычно записываю сформированный макросом запрос, чтобы иметь возможность проверить его корректность и работоспособность, для этого добавим вот такую строчку: