Использование sql в excel

Содержание
  1. Импорт данных SQL в Excel
  2. Импорт данных SQL в Excel
  3. Небольшой экскурс в MS SQL
  4. Создаём базу данных
  5. Создаём таблицу
  6. Импорт данных SQL в Excel
  7. Используйте возможности SQL для создания запросов в Excel и напрямую к таблицам Excel
  8. Перед началом работы добавьте «Всплывающие часы» в Excel
  9. Как превратить данные Excel в реляционную базу данных и подготовить их к работе с SQL запросами
  10. Как создать и выполнить запрос SQL SELECT к таблицам Excel
  11. Операторы Left Join, Order By, Group By, Distinct и другие SQLite команды в Excel
  12. Как объединить две и более Excel таблиц с помощью надстройки «SQL запросы»
  13. Эффективная работа в MS Office
  14. Экономия 5 минут в час за счет более продуктивной работы дает за год экономию в 4 рабочие недели
  15. Excel VBA. SQL-запросы в подключениях
  16. Выполнять SQL-запросы к файлам Excel
  17. Откройте SQL-подключение к файлу Excel
  18. Откройте SQL-подключение к файлу Excel, защищенному паролем
  19. Чтение содержимого электронной таблицы Excel
  20. Удалить данные из строки Excel
  21. Получить данные Excel, кроме определенной строки
  22. Использование Excel со связанными серверами SQL Server и распределенными запросами
  23. Аннотация
  24. Дополнительная информация
  25. Запрос источника данных Excel на связанном сервере
  26. Использование SQL Server Management Studio или Enterprise Manager для настройки источника данных Excel в качестве связанного сервера
  27. Использование хранимой процедуры для настройки источника данных Excel в качестве связанного сервера
  28. Использование SQL-DMO для настройки источника данных Excel в качестве связанного сервера
  29. Использование SMO для настройки источника данных Excel в качестве связанного сервера
  30. Запрос источника данных Excel на связанном сервере
  31. Запрос источника данных Excel с помощью распределенных запросов
  32. Ссылки

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

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

Небольшой экскурс в MS SQL

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

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

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

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

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

1

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

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

2

3

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

4

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

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

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

CREATE DATABASE SHOPS

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

5

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

6

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

7

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

9

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

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

10

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

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

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

11

12

13

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

14

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

15

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

Источник

Используйте возможности SQL для создания запросов в Excel и напрямую к таблицам Excel

sql select statement ru e1433761437783

Порой таблицы Excel постепенно разрастаются настолько, что с ними становится неудобно работать. Поиск дубликатов, группировка, сложная сортировка, объединение нескольких таблиц в одну, т.д. — превращаются в действительно трудоёмкие задачи. Теоретически эти задачи можно легко решить с помощью языка запросов SQL… если бы только можно было составлять запросы напрямую к данным Excel.

Инструмент XLTools «SQL запросы» расширяет Excel возможностями языка структурированных запросов:

Перед началом работы добавьте «Всплывающие часы» в Excel

«SQL запросы» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

xltools ribbon overview ru

download

Как превратить данные Excel в реляционную базу данных и подготовить их к работе с SQL запросами

По умолчанию Excel воспринимает данные как простые диапазоны. Но SQL применим только к реляционным базам данных. Поэтому, прежде чем создать запрос, преобразуйте диапазоны Excel в таблицу (именованный диапазон с применением стиля таблицы):

Выберите таблицу arrow to right primary mainОткройте вкладку «Конструктор» arrow to right primary mainНапечатайте имя таблицы.

Повторите эти шаги для каждого диапазона, который планируете использовать в запросах.

Напр., «КодТовара», «ЦенаРозн», «ОбъемПродаж», т.д.

sql format as table ru e1433761298151

Как создать и выполнить запрос SQL SELECT к таблицам Excel

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

В левой части окна находится дерево данных со всеми доступными таблицами Excel.

Нажатием на узлы открываются/сворачиваются поля таблицы (столбцы).

Выберите целые таблицы или конкретные поля.

attention

sql select statement ru e1433761437783

Операторы Left Join, Order By, Group By, Distinct и другие SQLite команды в Excel

XLTools использует стандарт SQLite. Пользователи, владеющие языком SQLite, могут создавать самые разнообразные запросы:

info

Как объединить две и более Excel таблиц с помощью надстройки «SQL запросы»

Вы можете объединить несколько таблиц Excel в одну, если у них есть общее ключевое поле. Предположим, вам нужно объединить несколько таблиц по общему столбцу «КодТовара»:

Нажмите Выполнить SQL на вкладке XLTools arrow to right primary mainВыберите поля, которые нужно включить в объединённую таблицу.

По мере выбора полей, автоматически генерируется запрос SELECT и LEFT JOIN.

Источник

Эффективная работа в MS Office

Экономия 5 минут в час за счет более продуктивной работы дает за год экономию в 4 рабочие недели

Excel VBA. SQL-запросы в подключениях

UPDATE 21.10.15 Добавил «обратный» макрос — VBA в SQL и макрос для доступа к строке запроса SQL

Некоторое время назад я прошел несколько курсов по SQL. И мне было очень интересно — какую часть из мощного инструмента под названием T-SQL можно применять без использования SQL-Server (не дают мне сервачек под мои нужды, хнык-хнык).

Итак… Начнем с простого — подключение через Query Table в VBA. Можно записать через макрорекордер — для этого нужно создать подключение через Microsoft Query.

microsoft query1

Выбираем Excel Files, указываем путь к файлу (пытаясь при этом не ругать разработчиков за интерфейс из 90х годов), фильтруем как-угодно поля. Нам сейчас это не важно — главное получить код, который дальше можно будет корректировать.

Должно получится что-то вроде этого:

Теперь начинаем копаться глубже — какого уровня запросы можно строить из VBA. Самые-самые базовые, основные конструкции — все работает, все ок.

Заполнение нового столбца одинаковым значением

Переименование столбцов

Фильтрация записей

Сортировка

Агрегация записей

Дату можно впрямую через конструкцию

Но я люблю отталкиваться от текущей даты. За пару текущая дата-время отвечает функция SYSDATETIME() и она может вернуть в том числе текущий день. Для этого нужна еще одна функция — CONVERT(type,value)

С функцией DATEFROMPARTS строка запроса в Excel почему-то не дружит, поэтому придется использовать костыли функцию DATEADD:

Эта строчка в любой день октября 2015 вернет значение — 30.11.15 23:59

А теперь — немного best practice!

Объединение + Агрегация + Join + Подзапросы. И самое интересное — подключение к нескольким источникам:

Одна проблема — если осуществлять такого вида запрос для соединения нескольких Excel-файлов, он будет выполняться достаточно медленно. У меня вышло порядка 2 минут. Но не стоит думать что это бесполезно — если подобные запросы выполнять при подключении к SQL-серверу, то время обработки будет 1-2 секунды (само собой, все зависит от сложности запроса, базы, и прочие прочие факторы).

Бонусы

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

Сами запросы просто и удобно создавать, например, используя Notepad++. Создали многострочный запрос SQL, копируете его в буфер обмена, запускаете макрос и вуаля — в буфере обмена строчки кода, готовые для вставки в ваши макросы. При желании вы можете настроить название переменной и количество табуляций.

Источник

Выполнять SQL-запросы к файлам Excel

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

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

Напротив, вы можете реализовать эту функциональность с помощью SQL-запросов, используя только два действия: действие Открыть SQL-подключение и действие Выполнять инструкции SQL.

Откройте SQL-подключение к файлу Excel

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

Чтобы установить подключение, создайте новую переменную с именем %Excel_File_Path% и инициализируйте его, указав путь к файлу Excel. При желании вы можете пропустить этот шаг и использовать жестко заданный путь к файлу позже в потоке.

set variable action

Теперь разверните действие Открыть SQL-подключение и заполните следующую строку подключения в его свойствах.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%Excel_File_Path%;Extended Properties=»Excel 12.0 Xml;HDR=YES»;

Для успешного использования представленной строки подключения вам необходимо скачать и установить Распространяемый пакет ядра СУБД Microsoft Access 2010.

open sql connection action

Откройте SQL-подключение к файлу Excel, защищенному паролем

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

Для этого запустите файл Excel с помощью действие Запустить Excel. Файл защищен паролем, поэтому введите соответствующий пароль в поле Пароль.

launch excel action

Затем разверните соответствующие действия автоматизации пользовательского интерфейса и перейдите к Файл > Информация > Защита книги > Зашифровать паролем. Дополнительные сведения об автоматизации пользовательского интерфейса и о том, как использовать соответствующие действия можно найти в Автоматизировать классические потоки.

ui actions

После выбора Зашифровать паролем заполните пустую строку во всплывающем диалоговом окне, используя действие Заполнить текстовое поле в окнах. Чтобы заполнить пустую строку, используйте следующее выражение: %»»%.

populate text field action

Чтобы нажать на ОК в диалоговом окне и применить изменения, разверните действие Нажать кнопку в окне.

Наконец, разверните действие Закрыть Excel, чтобы сохранить незащищенную книгу как новый файл Excel.

close excel action

После сохранения файла следуйте инструкциям в Открытие SQL-подключения к файлам Excel, чтобы открыть к нему подключение.

Когда работа с файлом Excel будут завершена, используйте действие Удалить файлы для удалению незащищенной копии файла Excel.

delete file action

Чтение содержимого электронной таблицы Excel

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

Более эффективный способ получения определенных значений из электронных таблиц — это рассматривать файлы Excel как базы данных и выполнять на них SQL-запросы. Этот подход быстрее и увеличивает производительность потока.

Чтобы получить все содержимое электронной таблицы, вы можете использовать следующий SQL-запрос в действие Выполнять инструкции SQL.

execute sql statement action

Чтобы применить этот SQL-запрос в ваших потоках, замените заполнитель SHEET именем электронной таблицы, к которой вы хотите получить доступ.

Чтобы получить строки, содержащие определенное значение в определенном столбце, используйте следующий запрос SQL:

Чтобы применить этот SQL-запрос в ваших потоках, замените:

Удалить данные из строки Excel

Хотя Excel не поддерживает SQL-запрос DELETE, вы можете использовать запрос UPDATE, чтобы установить для всех ячеек определенной строки значение NULL.

Точнее, вы можете использовать следующий SQL-запрос:

execute sql statement action delete

При разработке потока вы должны заменить заполнитель SHEET именем электронной таблицы, к которой вы хотите получить доступ.

Заполнители COLUMN1 а также COLUMN2 представляют имена всех существующих столбцов. В этом примере столбцов два, но в реальном сценарии количество столбцов может быть другим.

Часть запроса [COLUMN1]=’VALUE’ определяет строку, которую вы хотите обновить. В вашем потоке используйте имя столбца и значение в зависимости от того, какая комбинация однозначно описывает строки.

Получить данные Excel, кроме определенной строки

В некоторых сценариях может потребоваться получить все содержимое электронной таблицы Excel, кроме определенной строки.

Удобный способ добиться этого результата — установить для значений нежелательной строки значение NULL, а затем получить все значения, кроме нулевых.

Чтобы изменить значения определенной строки в электронной таблице, вы можете использовать SQL-запрос UPDATE, представленный в Удалить данные из строки Excel:

execute sql statement action delete

Затем выполните следующий SQL-запрос, чтобы получить все строки электронной таблицы, не содержащие значений NULL:

Заполнители COLUMN1 а также COLUMN2 представляют имена всех существующих столбцов. В этом примере столбцов два, но в реальной таблице количество столбцов может быть другим.

Источник

Использование 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:

Источник

Adblock
detector