SQL запросы в Microsoft Excel

Содержание

Подключение Excel к отдельной базе данных в Базе данных SQL Azure

02/12/2019
Время чтения: 3 мин
Соавторы
- все

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

Connect Excel to a single database in Azure SQL Database and import data and create tables and charts values in the database.

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

In this tutorial you will set up the connection between Excel and a database table, save the file that stores data and the connection information for Excel, and then create a pivot chart from the database values.

Чтобы начать работу, вам понадобится отдельная база данных.You'll need a single database before you get started. Если база данных отсутствует, см. статью Краткое руководство. Создание отдельной базы данных в Базе данных SQL с помощью портала Azure, а также Краткое руководство.

Создание правила брандмауэра на уровне сервера для Базы данных SQL Azure с помощью портала Azure, чтобы за несколько минут получить единую базу данных с примерами данных.

If you don't have one, see Create a single database and Create server-level IP firewall to get a single database with sample data up and running in a few minutes.

Следуя инструкциям в этой статье, вы импортируете демонстрационные данные в Excel, но те же действия можно выполнять и с собственными данными.In this article, you'll import sample data into Excel from that article, but you can follow similar steps with your own data.

Вам также понадобится копия Excel.You'll also need a copy of Excel. В этой статье используется Microsoft Excel 2016.This article uses Microsoft Excel 2016.

Подключение Excel к базе данных SQL и загрузка данныхConnect Excel to a SQL database and load data

Чтобы подключить Excel к базе данных SQL, откройте программу Excel, а затем создайте новую книгу или откройте существующую.To connect Excel to SQL database, open Excel and then create a new workbook or open an existing Excel workbook.
В строке меню в верхней части страницы выберите вкладку Данные, нажмите кнопку Получить данные, выберите пункт “Из Azure”, а затем — пункт Из базы данных SQL Azure.In the menu bar at the top of the page, select the Data tab, select Get Data, select From Azure, and then select From Azure SQL Database.
Откроется мастер подключения данных.The Data Connection Wizard opens.
В диалоговом окне Подключение к серверу баз данных введите имя сервера базы данных SQL, к которому нужно подключиться, в формате .database.windows.net.In the Connect to Database Server dialog box, type the SQL Database Server name you want to connect to in the form .
database.windows.net. Пример: msftestserver.database.windows.net.For example, msftestserver.database.windows.net. При необходимости введите имя базы данных.Optionally, enter in the name of your database. Нажмите кнопку ОК, чтобы открыть окно учетных данных.
Select OK to open the credentials window.
В диалоговом окне База данных SQL Server выберите слева пункт База данных, а затем введите имя пользователя и пароль для сервера Базы данных SQL, к которому нужно подключиться.
In the SQL Server Database dialog box, select Database on the left side, and then enter in your User Name and Password for the SQL Database server you want to connect to.
Нажмите кнопку Подключиться, чтобы открыть навигатор.Select Connect to open the Navigator.
Совет
В зависимости от сетевой среды, возможно, вам не удастся подключиться или соединение будет разорвано, если сервер базы данных SQL не разрешает трафик с IP-адреса клиента.

Depending on your network environment, you may not be able to connect or you may lose the connection if the SQL Database server doesn't allow traffic from your client IP address.
Перейдите на портал Azure, щелкните “Серверы SQL Server”, выберите свой сервер, в разделе “Параметры” щелкните “Брандмауэр” и добавьте IP-адрес клиента.
Go to the Azure portal, click SQL servers, click your server, click firewall under settings and add your client IP address. Дополнительные сведения см. в статье Настройка правила брандмауэра уровня сервера базы данных SQL Azure с помощью портала Azure.See How to configure firewall settings for details.
В навигаторе выберите в списке нужную базу данных, выберите нужные таблицы или представления (мы выбрали vGetAllCategories), а затем нажмите кнопку Загрузить, чтобы перенести данные из базы данных в электронную таблицу Excel.
In the Navigator, select the database you want to work with from the list, select the tables or views you want to work with (we chose vGetAllCategories), and then select Load to move the data from your database to your Excel spreadsheet.

Импорт данных в Excel и создание сводной диаграммыImport the data into Excel and create a pivot chart

Теперь, когда вы установили подключение, вы можете загрузить данные несколькими способами.Now that you've established the connection, you have several different options with how to load the data. Например, ниже описывается создание сводной диаграммы на основе данных из базы данных SQL.For example, the following steps create a pivot chart the data found in your SQL Database.

Выполните действия из предыдущего раздела, но на этот раз не нажимайте кнопку Загрузить, а выберите пункт Загрузить в из раскрывающегося списка Загрузить.Follow the steps in the previous section, but this time, instead of selecting Load, select Load to from the Load drop-down.
Затем выберите способ представления данных в книге.Next, select how you want to view this data in your workbook. Мы выбрали режим Сводная диаграмма.We chose PivotChart.
Кроме того, можно создать новый лист или добавить эти сведения в модель данных.You can also choose to create a New worksheet or to Add this data to a Data Model. Дополнительные сведения о моделях данных см.
в статье Создание модели данных в Excel.For more information on Data Models, see Create a data model in Excel.
Лист теперь содержит пустую сводную таблицу и диаграмму.The worksheet now has an empty pivot table and chart.
В разделе Поля сводной таблицыустановите все флажки для полей, которые требуется просмотреть.Under PivotTable Fields, select all the check-boxes for the fields you want to view.

Совет

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

If you want to connect other Excel workbooks and worksheets to the database, select the Data tab, and select Recent Sources to launch the Recent Sources dialog box. Выберите в списке ранее созданное подключение и нажмите кнопку Открыть.

From there, choose the connection you created from the list, and then click Open.

Создание постоянного подключения с помощью файла ODCCreate a permanent connection using .odc file

Чтобы сохранить сведения о подключении, можно создать файл ODC. После этого подключение можно будет выбирать в диалоговом окне Существующие подключения.To save the connection details permanently, you can create an .odc file and make this connection a selectable option within the Existing Connections dialog box.

В строке меню в верхней части страницы выберите вкладку Данные и нажмите кнопку Существующие подключения, чтобы открыть диалоговое окно Существующие подключения.In the menu bar at the top of the page, select the Data tab, and then select Existing Connections to launch the Existing Connections dialog box.
1. Нажмите кнопку Найти другие, чтобы открыть диалоговое окно Выбор источника данных.Select Browse for more to open the Select Data Source dialog box.
2. Выберите файл +Новое подключение к SQL-серверу.odc и нажмите кнопку Открыть, чтобы запустить мастер подключения к данным.Select the +NewSqlServerConnection.odc file and then select Open to open the Data Connection Wizard.
В мастере подключения к данным введите имя сервера и учетные данные для базы данных SQL.In the Data Connection Wizard, type in your server name and your SQL Database credentials. Щелкните Далее.Select Next.
1. В раскрывающемся списке выберите нужную базу данных.Select the database that contains your data from the drop-down.
2. Выберите нужную таблицу или представление.Select the table or view you're interested in. Мы выбрали vGetAllCategories.We chose vGetAllCategories.
3. Щелкните Далее.Select Next.
На следующем экране мастера подключения к данным выберите расположение файла, имя файла и понятное имя.Select the location of your file, the File Name, and the Friendly Name in the next screen of the Data Connection Wizard.
Вы также можете сохранить пароль в файле, но это может сделать данные уязвимыми для несанкционированного доступа.You can also choose to save the password in the file, though this can potentially expose your data to unwanted access. По завершении нажмите кнопку Готово.
Select Finish when ready.
Выберите способ импорта данных.Select how you want to import your data. Мы выбрали создание сводной таблицы.We chose to do a PivotTable. Кроме того, можно изменить свойства подключения, нажав кнопку Свойства.
You can also modify the properties of the connection by select Properties. По окончании нажмите кнопку ОК.Select OK when ready. Если вы не сохранили пароль в файле, вам будет предложено ввести учетные данные.
If you did not choose to save the password with the file, then you will be prompted to enter your credentials.
Проверьте, сохранилось ли новое подключение. Для этого на вкладке Данные нажмите кнопку Существующие подключения.Verify that your new connection has been saved by expanding the Data tab, and selecting Existing Connections.

Дальнейшие действияNext steps

Отправьте отзыв о следующем:

Этот продукт

Источник: //docs.microsoft.com/ru-ru/azure/sql-database/sql-database-connect-excel

Как сделать запрос sql в excel?

SQL запросы в Microsoft Excel

SQL – популярный язык программирования, который применяется при работе с базами данных (БД). Хотя для операций с базами данных в пакете Microsoft Office имеется отдельное приложение — Access, но программа Excel тоже может работать с БД, делая SQL запросы. Давайте узнаем, как различными способами можно сформировать подобный запрос.

Как создать базу данных в Экселе

Создание SQL запроса в Excel

Язык запросов SQL отличается от аналогов тем, что с ним работают практически все современные системы управления БД.

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

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

Способ 1: использование надстройки

Но для начала давайте рассмотрим вариант, когда из Экселя можно создать SQL запрос не с помощью стандартного инструментария, а воспользовавшись сторонней надстройкой.

Одной из лучших надстроек, выполняющих эту задачу, является комплекс инструментов XLTools, который кроме указанной возможности, предоставляет массу других функций.

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

Скачать надстройку XLTools

После того, как вы скачали файл надстройки xltools.exe, следует приступить к его установке. Для запуска инсталлятора нужно произвести двойной щелчок левой кнопки мыши по установочному файлу. После этого запустится окно, в котором нужно будет подтвердить согласие с лицензионным соглашением на использование продукции компании Microsoft — NET Framework 4. Для этого всего лишь нужно кликнуть по кнопке «Принимаю» внизу окошка.
После этого установщик производит загрузку обязательных файлов и начинает процесс их установки.
Далее откроется окно, в котором вы должны подтвердить свое согласие на установку этой надстройки. Для этого нужно щелкнуть по кнопке «Установить».
Затем начинается процедура установки непосредственно самой надстройки.
После её завершения откроется окно, в котором будет сообщаться, что инсталляция успешно выполнена. В указанном окне достаточно нажать на кнопку «Закрыть».
Надстройка установлена и теперь можно запускать файл Excel, в котором нужно организовать SQL запрос. Вместе с листом Эксель открывается окно для ввода кода лицензии XLTools. Если у вас имеется код, то нужно ввести его в соответствующее поле и нажать на кнопку «OK». Если вы желаете использовать бесплатную версию на 14 дней, то следует просто нажать на кнопку «Пробная лицензия».
При выборе пробной лицензии открывается ещё одно небольшое окошко, где нужно указать своё имя и фамилию (можно псевдоним) и электронную почту. После этого жмите на кнопку «Начать пробный период».
Далее мы возвращаемся к окну лицензии. Как видим, введенные вами значения уже отображаются. Теперь нужно просто нажать на кнопку «OK».
После того, как вы проделаете вышеуказанные манипуляции, в вашем экземпляре Эксель появится новая вкладка – «XLTools». Но не спешим переходить в неё. Прежде, чем создавать запрос, нужно преобразовать табличный массив, с которым мы будем работать, в так называемую, «умную» таблицу и присвоить ей имя.
Для этого выделяем указанный массив или любой его элемент. Находясь во вкладке «» щелкаем по значку «Форматировать как таблицу». Он размещен на ленте в блоке инструментов «Стили». После этого открывается список выбора различных стилей. Выбираем тот стиль, который вы считаете нужным. На функциональность таблицы указанный выбор никак не повлияет, так что основывайте свой выбор исключительно на основе предпочтений визуального отображения.
Вслед за этим запускается небольшое окошко. В нем указываются координаты таблицы. Как правило, программа сама «подхватывает» полный адрес массива, даже если вы выделили только одну ячейку в нем. Но на всякий случай не мешает проверить ту информацию, которая находится в поле «Укажите расположение данных таблицы». Также нужно обратить внимание, чтобы около пункта «Таблица с заголовками», стояла галочка, если заголовки в вашем массиве действительно присутствуют. Затем жмите на кнопку «OK».
После этого весь указанный диапазон будет отформатирован, как таблица, что повлияет как на его свойства (например, растягивание), так и на визуальное отображение. Указанной таблице будет присвоено имя. Чтобы его узнать и по желанию изменить, клацаем по любому элементу массива. На ленте появляется дополнительная группа вкладок – «Работа с таблицами». Перемещаемся во вкладку «Конструктор», размещенную в ней. На ленте в блоке инструментов «Свойства» в поле «Имя таблицы» будет указано наименование массива, которое ему присвоила программа автоматически.
При желании это наименование пользователь может изменить на более информативное, просто вписав в поле с клавиатуры желаемый вариант и нажав на клавишу Enter.
После этого таблица готова и можно переходить непосредственно к организации запроса. Перемещаемся во вкладку «XLTools».
После перехода на ленте в блоке инструментов «SQL запросы» щелкаем по значку «Выполнить SQL».
Запускается окно выполнения SQL запроса. В левой его области следует указать лист документа и таблицу на древе данных, к которой будет формироваться запрос.
В правой области окна, которая занимает его большую часть, располагается сам редактор SQL запросов. В нем нужно писать программный код. Наименования столбцов выбранной таблицы там уже будут отображаться автоматически. Выбор столбцов для обработки производится с помощью команды SELECT. Нужно оставить в перечне только те колонки, которые вы желаете, чтобы указанная команда обрабатывала.
Далее пишется текст команды, которую вы хотите применить к выбранным объектам. Команды составляются при помощи специальных операторов. Вот основные операторы SQL:
- ORDER BY – сортировка значений;
- JOIN – объединение таблиц;
- GROUP BY – группировка значений;
- SUM – суммирование значений;
- DISTINCT – удаление дубликатов.
Кроме того, в построении запроса можно использовать операторы MAX, MIN, AVG, COUNT, LEFT и др.
В нижней части окна следует указать, куда именно будет выводиться результат обработки. Это может быть новый лист книги (по умолчанию) или определенный диапазон на текущем листе. В последнем случае нужно переставить переключатель в соответствующую позицию и указать координаты этого диапазона.
После того, как запрос составлен и соответствующие настройки произведены, жмем на кнопку «Выполнить» в нижней части окна. После этого введенная операция будет произведена.

Урок: «Умные» таблицы в Экселе

Способ 2: использование встроенных инструментов Excel

Существует также способ создать SQL запрос к выбранному источнику данных с помощью встроенных инструментов Эксель.

Запускаем программу Excel. После этого перемещаемся во вкладку «Данные».
В блоке инструментов «Получение внешних данных», который расположен на ленте, жмем на значок «Из других источников». Открывается список дальнейших вариантов действий. Выбираем в нем пункт «Из мастера подключения данных».
Запускается Мастер подключения данных. В перечне типов источников данных выбираем «ODBC DSN». После этого щелкаем по кнопке «Далее».
Открывается окно Мастера подключения данных, в котором нужно выбрать тип источника. Выбираем наименование «MS Access Database». Затем щелкаем по кнопке «Далее».
Открывается небольшое окошко навигации, в котором следует перейти в директорию расположения базы данных в формате mdb или accdb и выбрать нужный файл БД. Навигация между логическими дисками при этом производится в специальном поле «Диски». Между каталогами производится переход в центральной области окна под названием «Каталоги». В левой области окна отображаются файлы, расположенные в текущем каталоге, если они имеют расширение mdb или accdb. Именно в этой области нужно выбрать наименование файла, после чего кликнуть на кнопку «OK».
Вслед за этим запускается окно выбора таблицы в указанной базе данных. В центральной области следует выбрать наименование нужной таблицы (если их несколько), а потом нажать на кнопку «Далее».
После этого открывается окно сохранения файла подключения данных. Тут указаны основные сведения о подключении, которое мы настроили. В данном окне достаточно нажать на кнопку «Готово».
На листе Excel запускается окошко импорта данных. В нем можно указать, в каком именно виде вы хотите, чтобы данные были представлены:
- Таблица;
- Отчёт сводной таблицы;
- Сводная диаграмма.
Выбираем нужный вариант. Чуть ниже требуется указать, куда именно следует поместить данные: на новый лист или на текущем листе. В последнем случае предоставляется также возможность выбора координат размещения. По умолчанию данные размещаются на текущем листе. Левый верхний угол импортируемого объекта размещается в ячейке A1.
После того, как все настройки импорта указаны, жмем на кнопку «OK».
Как видим, таблица из базы данных перемещена на лист. Затем перемещаемся во вкладку «Данные» и щелкаем по кнопке «Подключения», которая размещена на ленте в блоке инструментов с одноименным названием.
После этого запускается окно подключения к книге. В нем мы видим наименование ранее подключенной нами базы данных. Если подключенных БД несколько, то выбираем нужную и выделяем её. После этого щелкаем по кнопке «Свойства…» в правой части окна.
Запускается окно свойств подключения. Перемещаемся в нем во вкладку «Определение». В поле «Текст команды», находящееся внизу текущего окна, записываем SQL команду в соответствии с синтаксисом данного языка, о котором мы вкратце говорили при рассмотрении Способа 1. Затем жмем на кнопку «OK».
После этого производится автоматический возврат к окну подключения к книге. Нам остается только кликнуть по кнопке «Обновить» в нем. Происходит обращение к базе данных с запросом, после чего БД возвращает результаты его обработки назад на лист Excel, в ранее перенесенную нами таблицу.

Способ 3: подключение к серверу SQL Server

Кроме того, посредством инструментов Excel существует возможность соединения с сервером SQL Server и посыла к нему запросов. Построение запроса не отличается от предыдущего варианта, но прежде всего, нужно установить само подключение. Посмотрим, как это сделать.

Запускаем программу Excel и переходим во вкладку «Данные». После этого щелкаем по кнопке «Из других источников», которая размещается на ленте в блоке инструментов «Получение внешних данных». На этот раз из раскрывшегося списка выбираем вариант «С сервера SQL Server».
Происходит открытие окна подключения к серверу баз данных. В поле «Имя сервера» указываем наименование того сервера, к которому выполняем подключение. В группе параметров «Учетные сведения» нужно определиться, как именно будет происходить подключение: с использованием проверки подлинности Windows или путем введения имени пользователя и пароля. Выставляем переключатель согласно принятому решению. Если вы выбрали второй вариант, то кроме того в соответствующие поля придется ввести имя пользователя и пароль. После того, как все настройки проведены, жмем на кнопку «Далее». После выполнения этого действия происходит подключение к указанному серверу. Дальнейшие действия по организации запроса к базе данных аналогичны тем, которые мы описывали в предыдущем способе.

Как видим, в Экселе SQL запрос можно организовать, как встроенными инструментами программы, так и при помощи сторонних надстроек.

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

Главный же недостаток XLTools заключается в том, что срок бесплатного пользования надстройкой ограничен всего двумя календарными неделями.

Мы рады, что смогли помочь Вам в решении проблемы.

Источник: //word-office.ru/kak-sdelat-zapros-sql-v-excel.html

Импорт данных из Excel в SQL Server

SQL запросы в Microsoft Excel

Microsoft Office Exel уже довольно давно стал ключевой программой при переносе разных баз, тк практически любая даже очень старая база работает с Exel и перевести в него данный не состовляет ни каких проблем. Это пошаговое руководство описывает различные способы импорта данных из листов Microsoft Excel в базы данных Microsoft SQL Server.

Требования перед началом

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

Экземпляр Microsoft SQL Server 7.0, Microsoft SQL Server 2000, Microsoft SQL Server 2005 или Microsoft SQL Server 2008
Microsoft Visual Basic 6.0 для примеров объектов ADO, использующих Visual Basic

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

Службы преобразования данных
Связанные серверы и распределенные запросы
Разработка объектов ADO на Visual Basic

Описание

В примерах, приведенных в данной статье, импорт данных Excel выполняется с помощью следующих функций:

Службы преобразования данных SQL Server (DTS)
Службы интеграции Microsoft SQL Server 2005 (SSIS)
Связанные серверы SQL Server
Распределенные запросы SQL Server
Поставщик объектов данных ActiveX(ADO) и Microsoft OLE DB для SQL Server
Поставщик ADO и Microsoft OLE DB для Jet 4.0

Import или Append

В примерах команд SQL, используемых в статье, показаны запросы Create Table для импорта данных Excel в новую таблицу SQL Server с использованием конструкций SELECT…INTO…FROM. При сохранении ссылок на объекты-источники и получатели выражения, приведенные в примерах, могут быть преобразованы в запросы Append с использованием конструкций INSERT INTO…SELECT…FROM.

Использование DTS или SSIS

Для импорта данных Excel в таблицы SQL Server могут быть использованы мастер импорта служб преобразования данных (DTS) SQL Server или мастер импорта и экспорта SQL Server.

При работе с мастером и выборе исходных таблиц Excel помните, что имена объектов Excel со знаком доллара ($) являются именами листов (например Лист1$), а имена объектов без знака доллара являются названиями именованных диапазонов Excel.

Использование связанного сервера

Для упрощения запросов книга Excel может быть настроена как связанный сервер в SQL Server. Для получения дополнительных сведений щелкните приведенный ниже номер статьи базы знаний Майкрософт: 306397 (//support.microsoft.com/kb/306397/RU/ )

Следующая программа импортирует данные из рабочего листа «Customers» связанного сервера Excel «EXCELLINK» в новую таблицу SQL Server с именем XLImport1:

←**

SELECT * INTO XLImport1 FROM EXCELLINK…[Customers$]

**→

При использовании OPENQUERY источнику может быть передан сквозной запрос:

←**

SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,

‘SELECT * FROM [Customers$]’)

**→

Использование распределенных запросов

Если устанавливать существующее подключение к книге Excel как связанный сервер нежелательно, данные могут быть импортированы с использованием функций OPENDATASOURCE или OPENROWSET. В следующих примерах кода также производится импорт данных из рабочего листа Excel «Customers» в новые таблицы SQL Server:

←**

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')…[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]'

**→

Использование ADO и SQLOLEDB

Синтаксис «распределенных запросов», приведенный в разделе Использование распределенных запросов, может быть использован также в приложении ADO для импорта данных Excel в SQL Server, если для подключения к SQL Server используется Microsoft OLE DB для SQL Server (SQLOLEDB).

Для работы следующего примера программы на Visual Basic 6.0 требуется добавление ссылки на проект в объекты данных ActiveX (ADO). В этом примере показано использование функций OPENDATASOURCE и OPENROWSET для подключения SQLOLEDB.

on Visual Basic

←**

Dim cn As ADODB.Connection

Dim strSQL As String

Dim lngRecsAff As Long

Set cn = New ADODB.Connection

cn.Open “Provider=SQLOLEDB;Data Source=;” & _

“Initial Catalog=;User ID=;Password=”

'Import by using OPENDATASOURCE.

strSQL = “SELECT * INTO XLImport6 FROM ” & _

“OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', ” & _

“'Data Source=C:\test\xltest.xls;” & _

“Extended Properties=Excel 8.0')…[Customers$]”

Debug.Print strSQL

cn.Execute strSQL, lngRecsAff, adExecuteNoRecords

Debug.Print “Records affected: ” & lngRecsAff

'Import by using OPENROWSET and object name.

strSQL = “SELECT * INTO XLImport7 FROM ” & _

“OPENROWSET('Microsoft.Jet.OLEDB.4.0', ” & _

“'Excel 8.0;Database=C:\test\xltest.xls', ” & _

“[Customers$])”

Debug.Print strSQL

cn.Execute strSQL, lngRecsAff, adExecuteNoRecords

Debug.Print “Records affected: ” & lngRecsAff

'Import by using OPENROWSET and SELECT query.

strSQL = “SELECT * INTO XLImport8 FROM ” & _

“OPENROWSET('Microsoft.Jet.OLEDB.4.0', ” & _

“'Excel 8.0;Database=C:\test\xltest.xls', ” & _

“'SELECT * FROM [Customers$]')”

Debug.Print strSQL

cn.Execute strSQL, lngRecsAff, adExecuteNoRecords

Debug.Print “Records affected: ” & lngRecsAff

cn.Close

Set cn = Nothing

**→

Использование ADO и Поставщика данных Jet

В примере из предыдущего раздела при импорте из Excel в SQL для связи с получателем использовались ADO и поставщик SQLOLEDB. Для подключения к источнику Excel можно воспользоваться поставщиком данных OLE DB для Jet 4.0.

Используя в выражениях SQL особые конструкции, имеющие три различных формата, база данных Jet может ссылаться на внешние базы данных:

[Полный путь к базе данных Microsoft Access].[Название таблицы]
[Название ISAM;Строка подключения ISAM].[Название таблицы]
[ODBC;Строка подключения ODBС].[Название таблицы]

В этом разделе для создания подключения ODBC к базе данных SQL Server используется третий формат. Может использоваться имя источника данных (DSN) ODBC или строка подключения без определения DSN:

←**

DSN:

[odbc;DSN=;UID=;PWD=]

Подключение без определения DSN:

[odbc;Driver={SQL Server};Server=;Database=;

UID=;PWD=]

**→

Для работы следующего примера программы на Visual Basic 6.0 требуется добавление ссылки на проект в ADO. В примере показан импорт данных Excel в SQL Server через подключение ADO с использованием поставщика данных Jet 4.0.

←**

Dim cn As ADODB.Connection

Dim strSQL As String

Dim lngRecsAff As Long

Set cn = New ADODB.Connection

cn.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _

“Data Source=C:\test\xltestt.xls;” & _

“Extended Properties=Excel 8.0”

'Import by using Jet Provider.

strSQL = “SELECT * INTO [odbc;Driver={SQL Server};” & _

“Server=;Database=;” & _

“UID=;PWD=].XLImport9 ” & _

“FROM [Customers$]”

Debug.Print strSQL

cn.Execute strSQL, lngRecsAff, adExecuteNoRecords

Debug.Print “Records affected: ” & lngRecsAff

cn.Close

Set cn = Nothing

**→

Для импорта данных Excel в другие базы данных Microsoft Access, базы данных индексно-последовательного метода доступа (ISAM) или базы ODBC также могут использоваться конструкции, поддерживаемые поставщиком данных Jet.

Устранение неполадок

Помните, что объекты Excel, имена которых содержат знак доллара ($), являются листами (например, Лист1$), другие объекты являются именованными диапазонами Excel.
В некоторых ситуациях, особенно если источник данных Excel представлен именем таблицы, а не запросом SELECT, столбцы таблицы-получателя SQL Server переупорядочиваются по алфавиту.Для получения дополнительных сведений о проблеме в работе поставщика данных Jet щелкните следующий номер статьи базы знаний Майкрософт: 299484 (//support.microsoft.com/kb/299484/RU/ )
Обнаружив, что в столбцах Excel содержатся числовые и текстовые данные, поставщик данных Jet выбирает «доминирующий» тип данных и возвращает NULL вместо значений другого типа.Для получения дополнительных сведений об обходе этой проблемы щелкните следующий номер статьи базы знаний Майкрософт: 194124 (//support.microsoft.com/kb/194124/RU/ )

—————————————————-

Полезные ссылки:

306397 (//support.microsoft.com/kb/306397/RU/ )

Использование Excel со связанными серверами SQL Server и распределенными запросами

299484 (//support.microsoft.com/kb/299484/RU/ )

При использовании ADOX для получения столбцов таблицы Access столбцы упорядочиваются по алфавиту

194124 (//support.microsoft.com/kb/194124/RU/ )

При использовании DAO OpenRecordset данным в Excel присваивается значение NULL

Источник: //webhamster.ru/mytetrashare/index/mtb172/1545887942g522kh16bj

Импорт данных SQL в Excel

SQL запросы в Microsoft Excel

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

Небольшое предисловие. Имеется база данных MS SQL, в которой содержится определённая таблица, её нужно загрузить в Excel.

Зачем использовать такую модель? Базы данных SQL могут быть объёмом гораздо больше чем файлы Excel и работают гораздо быстрее, а Excel используется как инструмент тонкой настройки загруженной таблицы.

Задача проста, но есть несколько моментов: необходимо знать, как называется инстанс (экземпляр) где находится база данных, естественно, нужно знать учётные данные, нужно знать название таблицы. Что ж, приступим.

//www.youtube.com/watch?v=Lo_-UXe5gq8

Я предпочитаю в таких случаях использовать Microsoft Management Studio, для поиска основных данных. Найти ярлык можно в меню Пуск.

В консоли MS SQL есть несколько полей:

Server Type — можно выбрать тип подключения (в этом случаем остаётся Database Engine);

Analysis Services — сервисы аналитики;
Reporting Services — сервисы отчётов;
Integration Services — сервисы интеграции (встраивания).

Далее следует имя сервера (его можно задать на этапе установки).

Тип аутентификации — WIndows Authentication или SQL Server Authentication, эти пункты позволяют выбрать тип проверки пользователя. Windows — можно войти под логином и паролем для операционной системы, SQL Server — под специальной учётной записью sa и заданным для неё паролем.

Оставим первый вариант — он проще (но запомните — только для тестов!). Хорошим тоном считается смешанная аутентификация и смена пароля для учётки sa на случай непредвиденных ситуаций.

Видно, что имя сервера — BLACKPRINCE, логин axiro. Пароль не скажу:) Осталось нажать кнопку «Connect» и зайти в базу данных.

Поди цифрой 1 — список баз данных, под 2 — тестовая база с именем Test, под 3 — запущенный SQL Server Agent — он должен быть запущен иначе база данных может быть не видна для других программ, если на нем стоит крест — щёлкнуть правой кнопкой мышки и выбрать «Start».

Исходные данные есть, теперь необходимо создать таблицу с данными. Это можно сделать через графический интерфейс, но лучше воспользоваться запросом — «New Query».

Откроется окно запроса.

Все запросы будут производиться на языке Transact SQL. У него очень много общего с синтаксисом SQL, но есть и много отличий.

Создаём базу данных

Если необходимо создать новую базу данных, например, магазины (SHOPS), синтаксис будет следующий:

CREATE DATABASE SHOPS

Обязательно после написания запроса нажать «Execute» или F5! В противном случае запрос не будет выполнен.

Под цифрой 1 — «Execute», под 2 — текст запроса, под 3 — результат выполнения. Если в процессе выполнения будут выявлены ошибки, они незамедлительно будут показаны. Пока всё идёт по плану.

Создаём таблицу

Если необходимо выполнить запрос к определённой базе данных — щёлкаем правой кнопкой на неё и выбираем «New query».

В окне запроса введём текст запроса. Цель — создать таблицу с четырьмя столбцами: номер магазина, название магазина, выручка магазина, менеджер. Лучше все столбцы называть по английски (хотя бы на первоначальном этапе).

CREATE TABLE dbo.Shops
(ShopID int PRIMARY KEY NOT NULL,
ShopName varchar(25) NOT NULL,
Revenue money NULL,
Manager text NULL)
GO

Нажать «Execute». Таблица создалась. Проверить можно раскрыв дерево базы данных и раскрыть группу «Tables».

Цель достигнута. В запросе также были упомянуты типы данных (varchar(25), money, text, PRIMARY KEY).

Соответственно — varchar это длина текста, в скобках указано, что длина названия магазина может быть до 25 символов включительно, money — тип данных, представляющий денежные (валютные) значения, text — обычный текст, PRIMARY KEY — ограничение, позволяющее однозначно идентифицировать каждую запись в таблице SQL. Также есть атрибут NULL и NOT NULL — позволяет значениям принимать нулевое значение или не принимать (может в поле стоять ноль или не может).

Добавим в таблицу одну строчку (пока). Открываем окно нового запроса базе данных и добавляем запрос.

INSERT INTO dbo.Shops VALUES ( ‘1’, ‘Ручеёк’, ‘120000’, ‘Петров В.И.’)

Добавилась одна строка.

Со стороны Excel действий будет гораздо меньше. Переходим на вкладку «Данные», нажимаем кнопку «Получение внешних данных», «Из других источников», «С сервера SQL Server».

Как было написано выше, нужно указать имя сервера SQL — BLACKPRINCE, и выбрать проверку подлинности — Windows. Нажать «Далее».

В следующем окне выберем базу данных SHOPS и отметим галкой таблицу Shops. Если нужно выбрать несколько таблиц — отметить галкой соответствующую настройку. Нажать «Далее».

В следующем окне Excel предложит сохранить файл для подключения к базе данных с настройками. Нажать «Готово».

Теперь можно выбрать расположение загруженной таблицы и указать необходимые настройки. Я пока указывать их не буду, просто нажму «ОК». Таблица будет размещена в ячейку A1.

Цель достигнута! Таблица из базы данных перекочевала в Excel и теперь над ней можно совершать привычные действия.

В следующих уроках мы поговорим о более сложных манипуляциях с запросами к базам данных.

Вас может заинтересовать — Анализ план факт в Excel.

Источник: //pcandlife.ru/import-dannih-sql-v-excel/

Как на T-SQL загрузить данные из Excel? Распределенные запросы

SQL запросы в Microsoft Excel

В 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

Все примеры будут выполнены в Microsoft SQL Server 2016 Express.

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

Включается возможность использования распределённых запросов с помощью системной хранимой процедуры sp_configure, которая отвечает за системные параметры сервера. Нам необходимо параметру Ad Hoc Distributed Queries присвоить значение 1, для этого выполните следующую SQL инструкцию.

sp_configure 'show advanced options', 1; RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; GO

Параметром show advanced options мы отображаем дополнительные параметры системной хранимой процедуры sp_configure, а командой RECONFIGURE мы применяем новые параметры.

Функция OPENDATASOURCE в T-SQL

OPENDATASOURCE – функция возвращает ссылку на источник данных, который может использоваться как часть четырехсоставного имени объекта.

Данная функция принимает два параметра, первый (provider_name) – это имя поставщика OLE DB, второй (init_string) – строка подключения.

Пример.

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:\TestExcel.xls; Extended Properties=Excel 8.0')…[Лист1$];

Как видите, в функцию мы передали имя поставщика и строку подключения, а потом обратились к источнику, используя четырехсоставное имя объекта, т.е. к таблице на листе 1.

Функция OPENROWSET в T-SQL

OPENROWSET – функция подключается к источнику данных и выполняет необходимый запрос.

У данной функции уже много параметров, с помощью которых мы указываем все необходимые сведения о подключении, которые требуются для доступа к источнику данных OLE DB.

Для того чтобы обратиться к Excel, можно написать следующий запрос.

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; Database=D:\TestExcel.xls', [Лист1$]);

Вместо [Лист1$] можно в апострофах указать необходимый SQL запрос, например:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; Database=D:\TestExcel.xls', 'SELECT ProductName, Price FROM [Лист1$]');

Функция OPENQUERY в T-SQL

OPENDATASOURCE и OPENROWSET удобно использовать, если Вам требуется разово получить данные из определённых внешних источников, в нашем случае из Excel, но если Вам требуется постоянно обращаться к таким источникам, то имеет смысл создать специальный объект в SQL Server – он называется «Связанный сервер» (Linked Server). Данный объект будет настроен на конкретный источник, и к этому объекту Вы сможете обращаться практически как к обычной базе данных, не указывая при этом строку подключения.

Связанный сервер настраивается один раз, иными словами, при его создании Вы указываете все необходимые настройки, а затем просто обращаетесь к нему по имени, которое Вы укажете.

Для обращения к связанным серверам в SQL сервере как раз и используется функция OPENQUERY.

OPENQUERY – функция обращается к связанному серверу и выполняет указанный запрос. На эту функцию можно даже ссылаться в инструкциях по модификации данных, т.е. мы можем изменять данные на связанном сервере.

Связанный сервер сначала нужно создать, это можно сделать с помощью системной процедуры sp_addlinkedserver, доступ к данному серверу настраивается процедурой sp_addlinkedsrvlogin. Связанные серверы также можно создавать и с помощью среды SQL Server Management Studio (Обозреватель объектов-> Объекты сервера-> Связанные серверы).

Пример.

–Создание связанного сервера EXEC dbo.sp_addlinkedserver @server = 'TEST_EXCEL', @srvproduct='OLE DB', @provider='Microsoft.Jet.OLEDB.4.0', @datasrc='D:\TestExcel.xls', @provstr='Excel 8.0' –Настройки безопасности (авторизации) EXEC dbo.sp_addlinkedsrvlogin @rmtsrvname= 'TEST_EXCEL', @useself= 'False', @locallogin=NULL, @rmtuser=NULL, @rmtpassword=NULL Более подробно про связанные серверы можете почитать в материале «Связанные серверы в MS SQL Server – создание и примеры использования».

После того как связанный сервер создан, в моем примере я его назвал TEST_EXCEL, мы можем к нему обратиться с помощью функции OPENQUERY.

SELECT * FROM OPENQUERY (TEST_EXCEL, 'SELECT * FROM [Лист1$]')

Также к связанному серверу можно обратиться и без использования функции OPENQUERY, например:

SELECT * FROM TEST_EXCEL…[Лист1$]

Но рекомендовано к связанным серверам обращаться через функцию OPENQUERY.

Как я уже отмечал, функцию OPENQUERY можно использовать и для модификации данных на связанном сервере в инструкциях INSERT, UPDATE или DELETE, например, для обновления данных, в нашем случае инструкция будет выглядеть следующем образом.

UPDATE OPENQUERY (TEST_EXCEL, 'SELECT * FROM [Лист1$]') SET Price = 700 WHERE ProductId = 3; SELECT * FROM OPENQUERY (TEST_EXCEL, 'SELECT * FROM [Лист1$]')

Заметка! Начинающим программистам рекомендую почитать мою книгу «Путь программиста T-SQL», в ней я подробно, с большим количеством примеров, рассказываю про многие другие полезные возможности языка Transact-SQL.

У меня на этом все, надеюсь, материал был Вам полезен, пока!

статьей с друзьями в социальных сетях:

Интересные книги:

Следите за выходом новых статей в социальных сетях:

Источник: //info-comp.ru/obucheniest/645-distributed-requests-in-t-sql.html