wwwoldi.ru

About Me

Dashy

Hello word I am dashy Guy

Разное

Excel в sql запрос: Как сделать SQL запрос в Excel

alexxlab
No Comments

Содержание

Как сделать SQL запрос в Excel

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

Читайте также: Как создать базу данных в Экселе

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

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

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

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

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

  1. После того, как вы скачали файл надстройки xltools.exe, следует приступить к его установке. Для запуска инсталлятора нужно произвести двойной щелчок левой кнопки мыши по установочному файлу. После этого запустится окно, в котором нужно будет подтвердить согласие с лицензионным соглашением на использование продукции компании Microsoft — NET Framework 4. Для этого всего лишь нужно кликнуть по кнопке «Принимаю» внизу окошка.
  2. После этого установщик производит загрузку обязательных файлов и начинает процесс их установки.
  3. Далее откроется окно, в котором вы должны подтвердить свое согласие на установку этой надстройки. Для этого нужно щелкнуть по кнопке «Установить».
  4. Затем начинается процедура установки непосредственно самой надстройки.
  5. После её завершения откроется окно, в котором будет сообщаться, что инсталляция успешно выполнена. В указанном окне достаточно нажать на кнопку «Закрыть».
  6. Надстройка установлена и теперь можно запускать файл Excel, в котором нужно организовать SQL запрос. Вместе с листом Эксель открывается окно для ввода кода лицензии XLTools. Если у вас имеется код, то нужно ввести его в соответствующее поле и нажать на кнопку «OK». Если вы желаете использовать бесплатную версию на 14 дней, то следует просто нажать на кнопку «Пробная лицензия».
  7. При выборе пробной лицензии открывается ещё одно небольшое окошко, где нужно указать своё имя и фамилию (можно псевдоним) и электронную почту. После этого жмите на кнопку «Начать пробный период».
  8. Далее мы возвращаемся к окну лицензии. Как видим, введенные вами значения уже отображаются. Теперь нужно просто нажать на кнопку «OK».
  9. После того, как вы проделаете вышеуказанные манипуляции, в вашем экземпляре Эксель появится новая вкладка – «XLTools». Но не спешим переходить в неё. Прежде, чем создавать запрос, нужно преобразовать табличный массив, с которым мы будем работать, в так называемую, «умную» таблицу и присвоить ей имя.
    Для этого выделяем указанный массив или любой его элемент. Находясь во вкладке «Главная» щелкаем по значку «Форматировать как таблицу». Он размещен на ленте в блоке инструментов «Стили». После этого открывается список выбора различных стилей. Выбираем тот стиль, который вы считаете нужным. На функциональность таблицы указанный выбор никак не повлияет, так что основывайте свой выбор исключительно на основе предпочтений визуального отображения.
  10. Вслед за этим запускается небольшое окошко. В нем указываются координаты таблицы. Как правило, программа сама «подхватывает» полный адрес массива, даже если вы выделили только одну ячейку в нем. Но на всякий случай не мешает проверить ту информацию, которая находится в поле «Укажите расположение данных таблицы». Также нужно обратить внимание, чтобы около пункта «Таблица с заголовками», стояла галочка, если заголовки в вашем массиве действительно присутствуют. Затем жмите на кнопку «OK».
  11. После этого весь указанный диапазон будет отформатирован, как таблица, что повлияет как на его свойства (например, растягивание), так и на визуальное отображение. Указанной таблице будет присвоено имя. Чтобы его узнать и по желанию изменить, клацаем по любому элементу массива. На ленте появляется дополнительная группа вкладок – «Работа с таблицами». Перемещаемся во вкладку «Конструктор», размещенную в ней. На ленте в блоке инструментов «Свойства» в поле «Имя таблицы» будет указано наименование массива, которое ему присвоила программа автоматически.
  12. При желании это наименование пользователь может изменить на более информативное, просто вписав в поле с клавиатуры желаемый вариант и нажав на клавишу Enter.
  13. После этого таблица готова и можно переходить непосредственно к организации запроса. Перемещаемся во вкладку «XLTools».
  14. После перехода на ленте в блоке инструментов «SQL запросы» щелкаем по значку «Выполнить SQL».
  15. Запускается окно выполнения SQL запроса. В левой его области следует указать лист документа и таблицу на древе данных, к которой будет формироваться запрос.

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

    Далее пишется текст команды, которую вы хотите применить к выбранным объектам. Команды составляются при помощи специальных операторов. Вот основные операторы SQL:

    • ORDER BY – сортировка значений;
    • JOIN – объединение таблиц;
    • GROUP BY – группировка значений;
    • SUM – суммирование значений;
    • DISTINCT – удаление дубликатов.

    Кроме того, в построении запроса можно использовать операторы MAX, MIN, AVG, COUNT, LEFT и др.

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

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

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

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

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

  1. Запускаем программу Excel. После этого перемещаемся во вкладку «Данные».
  2. В блоке инструментов «Получение внешних данных», который расположен на ленте, жмем на значок «Из других источников». Открывается список дальнейших вариантов действий. Выбираем в нем пункт «Из мастера подключения данных».
  3. Запускается Мастер подключения данных. В перечне типов источников данных выбираем «ODBC DSN». После этого щелкаем по кнопке «Далее».
  4. Открывается окно Мастера подключения данных, в котором нужно выбрать тип источника. Выбираем наименование «MS Access Database». Затем щелкаем по кнопке «Далее».
  5. Открывается небольшое окошко навигации, в котором следует перейти в директорию расположения базы данных в формате mdb или accdb и выбрать нужный файл БД. Навигация между логическими дисками при этом производится в специальном поле «Диски». Между каталогами производится переход в центральной области окна под названием «Каталоги». В левой области окна отображаются файлы, расположенные в текущем каталоге, если они имеют расширение mdb или accdb. Именно в этой области нужно выбрать наименование файла, после чего кликнуть на кнопку «OK».
  6. Вслед за этим запускается окно выбора таблицы в указанной базе данных. В центральной области следует выбрать наименование нужной таблицы (если их несколько), а потом нажать на кнопку «Далее».
  7. После этого открывается окно сохранения файла подключения данных. Тут указаны основные сведения о подключении, которое мы настроили. В данном окне достаточно нажать на кнопку «Готово».
  8. На листе Excel запускается окошко импорта данных. В нем можно указать, в каком именно виде вы хотите, чтобы данные были представлены:
    • Таблица;
    • Отчёт сводной таблицы;
    • Сводная диаграмма.

    Выбираем нужный вариант. Чуть ниже требуется указать, куда именно следует поместить данные: на новый лист или на текущем листе. В последнем случае предоставляется также возможность выбора координат размещения. По умолчанию данные размещаются на текущем листе. Левый верхний угол импортируемого объекта размещается в ячейке A1.

    После того, как все настройки импорта указаны, жмем на кнопку «OK».

  9. Как видим, таблица из базы данных перемещена на лист. Затем перемещаемся во вкладку «Данные» и щелкаем по кнопке «Подключения», которая размещена на ленте в блоке инструментов с одноименным названием.
  10. После этого запускается окно подключения к книге. В нем мы видим наименование ранее подключенной нами базы данных. Если подключенных БД несколько, то выбираем нужную и выделяем её. После этого щелкаем по кнопке «Свойства…» в правой части окна.
  11. Запускается окно свойств подключения. Перемещаемся в нем во вкладку «Определение». В поле «Текст команды», находящееся внизу текущего окна, записываем SQL команду в соответствии с синтаксисом данного языка, о котором мы вкратце говорили при рассмотрении Способа 1. Затем жмем на кнопку «OK».
  12. После этого производится автоматический возврат к окну подключения к книге. Нам остается только кликнуть по кнопке «Обновить» в нем. Происходит обращение к базе данных с запросом, после чего БД возвращает результаты его обработки назад на лист Excel, в ранее перенесенную нами таблицу.

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

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

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

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

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

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

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

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

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

  • Создание запросов SQL в интерфейсе Excel и напрямую к Excel таблицам

  • Автогенерация запросов SELECT и JOIN

  • Доступны JOIN, ORDER BY, DISTINCT, GROUP BY, SUM и другие операторы SQLite

  • Создание запросов в интуитивном редакторе с подстветкой синтаксиса

  • Обращение к любым таблицам Excel из дерева данных

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

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

  1. Выделите диапазон данных На вкладке «Главная» нажмитеФорматировать как таблицу Примените стиль таблицы.

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

    Напр., «КодТовара».

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

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

  4. Готово, теперь эти таблицы будут служить реляционной базой данных и готовы к SQL запросам.

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

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

  1. Нажмите кнопкуВыполнить SQLна вкладке XLTools Откроется окно редактора.

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

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

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

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

    Внимание:редактор запросов SQL автоматически подсвечивает синтаксис.

  4. Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.

  5. Нажмите «Выполнить» Готово!

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

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

  • LEFT JOIN – объединить две и более таблиц по общему ключевому столбцу

  • ORDER BY – сортировка данных в выдаче запроса

  • DISTINCT – удаление дубликатов из результата запроса

  • GROUP BY – группировка данных в выдаче запроса

  • SUM, COUNT, MIN, MAX, AVG и другие операторы

Совет:вместо набора названий таблиц вручную, просто перетягивайте названия из дерева данных в область редактора SQL запросов.

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

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

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

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

  2. Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.

  3. Нажмите «Выполнить» Готово! Объединённая таблица появится в считанные секунды.

Excel VBA. SQL-запросы в подключениях — Эффективная работа в MS Office

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

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

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

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

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


Sub Макрос1()
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=D:\Dropbox\Excel\тест excel_SQL-2015.xlsx;DefaultDir=D:\Dropbox\Excel;DriverId=1046;MaxBufferSize=2048;Page" _
        ), Array("Timeout=5;")), Destination:=Range("$A$1")).QueryTable
        .CommandType = 0
        .CommandText = Array( _
        "SELECT Продажи.F2, Продажи.F3" & Chr(13) & "FROM `D:\Dropbox\Excel\тест excel_SQL-2015.xlsx`.Продажи Продажи" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Таблица_Запрос_из_Excel_Files"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Строчка .CommandText = «SELECT…» — отвечает за SQL запрос. Если хотя бы немного почитать поисковую выдачу google по запросу QueryTable можно упростить код до следующего:


Sub CopyFromRecordset_To_Range()
  
  DBPath = "C:\InputData.xlsx"
  sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"
  Conn.Open sconnect
  sSQLSting = "SELECT * FROM [Sheet1$]"

  rs.Open sSQLSting, Conn
  Set QT1 = ActiveSheet.QueryTables.Add(rs, Range("A1"))
  QT1.Refresh

  rs.Close
  Conn.Close

End Sub

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

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


SELECT  'YTikhonov', * 
FROM    [Sheet1$]

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


SELECT  [Advertiser] AS 'Рекламодатель', [Quantity] AS 'Количество'
FROM    [Sheet1$]

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


SELECT  * 
FROM    [Sheet1$]
WHERE   [Year] = 2014

Сортировка


SELECT   * 
FROM     [Sheet1$]
ORDER BY [Advertiser] DESC

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


SELECT   [Advertiser], Sum([Cost]) 
FROM     [Sheet1$]
GROUP BY [Advertiser]

Работа с датой

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


[SomeDateField] = {ts '2015-01-01 00:00:00'}

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


SELECT CONVERT(date,SYSDATETIME())

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


DATEADD(minute, 59, DATEADD(hour, 23, DATEADD(month, MONTH(SYSDATETIME())+1, DATEADD(year, YEAR(SYSDATETIME()) - 1900, 0))))-1

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

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

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


SELECT [Year], O.Numbers, SCost, SVolume, SQuantity FROM
 (
  SELECT [Year], Month, SUM([Cost RUB]) AS SCost, SUM(Volume) AS SVolume, SUM(Quantity) AS SQuantity FROM
   (
     SELECT Advertiser, 2013 as [Year], Month, [Cost RUB], Quantity, Volume
     FROM [N:\GK\Radio\Маркетинг\Служебный\2013.xlsb].[Мониторинг$]
      UNION
     SELECT Advertiser, 2014 as [Year], Month, [Cost RUB], Quantity, Volume
     FROM [N:\GK\Radio\Маркетинг\Служебный\2014.xlsb].[Мониторинг$]
       UNION
     SELECT Advertiser, 2015 as [Year], Month, [Cost RUB], Quantity, Volume
     FROM [N:\GK\Radio\Маркетинг\Служебный\2015.xlsb].[Мониторинг$]
   )
   WHERE [Advertiser] = 'METRO GROUP'
   GROUP BY [Year], Month
 ) as T INNER JOIN [C:\test\Month.xlsb].[Test$] AS O
ON T.[Month] = O.[Month]

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

Бонусы

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


'работа с буфером обмена http://excelvba.ru/code/clipboard
Private Function ClipboardText() ' чтение из буфера обмена
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .GetFromClipboard
        ClipboardText = .GetText
    End With
End Function
 
Private Sub SetClipboardText(ByVal txt$) ' запись в буфер обмена
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText txt$
        .PutInClipboard
    End With
End Sub

Public Sub SQL_String_To_VBA()
  
    Dim sInput As String, sOut As String
    Dim ArrInput, i As Integer
    
    Dim cIdent As Integer: cIdent = 1 'Count of tabs
    Dim sVar As String: sVar = "strSQL" 'Name of variable
      
    sInput = ClipboardText()
    
    ArrInput = Split(sInput, Chr(13))
    
    For i = LBound(ArrInput) To UBound(ArrInput)
        sOut = sOut & sVar & " = " & sVar & " & " & Chr(34)
        sOut = sOut & String(cIdent, Chr(9))
        sOut = sOut & Replace(ArrInput(i), Chr(10), "")
        sOut = sOut & Chr(34) & "& chr(10)" & Chr(10)
    Next i
    
    SetClipboardText (sOut)

End Sub

Public Sub VBA_String_To_SQL()
 
    Dim sInput As String, sOut As String
    Dim ArrInput, i As Integer, sTemp
 
    sInput = ClipboardText()
 
    ArrInput = Split(sInput, Chr(10))
 
    For i = LBound(ArrInput) To UBound(ArrInput)
      sTemp = Replace(ArrInput(i), "& chr(10)", "")
      If Right(sTemp, 1) = " " Then sTemp = Left(sTemp, Len(sTemp) - 1)
      If Right(sTemp, 1) = Chr(34) Then sTemp = Left(sTemp, Len(sTemp) - 1)
 
      If Len(sTemp) > 0 Then
        sTemp = Right(sTemp, Len(sTemp) - InStr(1, sTemp, Chr(34)))
        sOut = sOut & Chr(10) & sTemp
      End If
    Next i
 
    SetClipboardText (sOut)

End Sub

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

И еще один небольшой бонус. Если у вас есть отчет по менеджерам/руководителям, построенный на запросах, то вам наверняка потребуется получать доступ к строке запроса через VBA. Сделать это можно через замечательную команду .CommandText — работает на чтение и запись. Мне для формирования отчета на 25 человек очень пригодился.


Public Sub ReplaceCommandText()
 
Dim con As WorkbookConnection
Dim sTemp As String
 
  For Each con In ActiveWorkbook.Connections
    sTemp = con.ODBCConnection.CommandText
    con.ODBCConnection.CommandText = sTemp
    con.Refresh
  Next con
 
End Sub

PS Ссылка с ответом на вопрос — как вставить данные из Excel в SQL
https://www.simple-talk.com/sql/t-sql-programming/questions-about-using-tsql-to-import-excel-data-you-were-too-shy-to-ask/

 

Приятного использования!

Поделиться ссылкой:

Понравилось это:

Нравится Загрузка…

Похожее

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

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

Читайте также: Как создать базу данных в Экселе

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

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

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

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

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

  1. После того, как вы скачали файл надстройки xltools.exe, следует приступить к его установке. Для запуска инсталлятора нужно произвести двойной щелчок левой кнопки мыши по установочному файлу. После этого запустится окно, в котором нужно будет подтвердить согласие с лицензионным соглашением на использование продукции компании Microsoft — NET Framework 4. Для этого всего лишь нужно кликнуть по кнопке «Принимаю» внизу окошка.
  2. После этого установщик производит загрузку обязательных файлов и начинает процесс их установки.
  3. Далее откроется окно, в котором вы должны подтвердить свое согласие на установку этой надстройки. Для этого нужно щелкнуть по кнопке «Установить».
  4. Затем начинается процедура установки непосредственно самой надстройки.
  5. После её завершения откроется окно, в котором будет сообщаться, что инсталляция успешно выполнена. В указанном окне достаточно нажать на кнопку «Закрыть».
  6. Надстройка установлена и теперь можно запускать файл Excel, в котором нужно организовать SQL запрос. Вместе с листом Эксель открывается окно для ввода кода лицензии XLTools. Если у вас имеется код, то нужно ввести его в соответствующее поле и нажать на кнопку «OK». Если вы желаете использовать бесплатную версию на 14 дней, то следует просто нажать на кнопку «Пробная лицензия».
  7. При выборе пробной лицензии открывается ещё одно небольшое окошко, где нужно указать своё имя и фамилию (можно псевдоним) и электронную почту. После этого жмите на кнопку «Начать пробный период».
  8. Далее мы возвращаемся к окну лицензии. Как видим, введенные вами значения уже отображаются. Теперь нужно просто нажать на кнопку «OK».
  9. После того, как вы проделаете вышеуказанные манипуляции, в вашем экземпляре Эксель появится новая вкладка – «XLTools». Но не спешим переходить в неё. Прежде, чем создавать запрос, нужно преобразовать табличный массив, с которым мы будем работать, в так называемую, «умную» таблицу и присвоить ей имя.
    Для этого выделяем указанный массив или любой его элемент. Находясь во вкладке «Главная» щелкаем по значку «Форматировать как таблицу». Он размещен на ленте в блоке инструментов «Стили». После этого открывается список выбора различных стилей. Выбираем тот стиль, который вы считаете нужным. На функциональность таблицы указанный выбор никак не повлияет, так что основывайте свой выбор исключительно на основе предпочтений визуального отображения.
  10. Вслед за этим запускается небольшое окошко. В нем указываются координаты таблицы. Как правило, программа сама «подхватывает» полный адрес массива, даже если вы выделили только одну ячейку в нем. Но на всякий случай не мешает проверить ту информацию, которая находится в поле «Укажите расположение данных таблицы». Также нужно обратить внимание, чтобы около пункта «Таблица с заголовками», стояла галочка, если заголовки в вашем массиве действительно присутствуют. Затем жмите на кнопку «OK».
  11. После этого весь указанный диапазон будет отформатирован, как таблица, что повлияет как на его свойства (например, растягивание), так и на визуальное отображение. Указанной таблице будет присвоено имя. Чтобы его узнать и по желанию изменить, клацаем по любому элементу массива. На ленте появляется дополнительная группа вкладок – «Работа с таблицами». Перемещаемся во вкладку «Конструктор», размещенную в ней. На ленте в блоке инструментов «Свойства» в поле «Имя таблицы» будет указано наименование массива, которое ему присвоила программа автоматически.
  12. При желании это наименование пользователь может изменить на более информативное, просто вписав в поле с клавиатуры желаемый вариант и нажав на клавишу Enter.
  13. После этого таблица готова и можно переходить непосредственно к организации запроса. Перемещаемся во вкладку «XLTools».
  14. После перехода на ленте в блоке инструментов «SQL запросы» щелкаем по значку «Выполнить SQL».
  15. Запускается окно выполнения SQL запроса. В левой его области следует указать лист документа и таблицу на древе данных, к которой будет формироваться запрос.

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

    Далее пишется текст команды, которую вы хотите применить к выбранным объектам. Команды составляются при помощи специальных операторов. Вот основные операторы SQL:

    • ORDER BY – сортировка значений;
    • JOIN – объединение таблиц;
    • GROUP BY – группировка значений;
    • SUM – суммирование значений;
    • DISTINCT – удаление дубликатов.

    Кроме того, в построении запроса можно использовать операторы MAX, MIN, AVG, COUNT, LEFT и др.

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

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

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

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

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

  1. Запускаем программу Excel. После этого перемещаемся во вкладку «Данные».
  2. В блоке инструментов «Получение внешних данных», который расположен на ленте, жмем на значок «Из других источников». Открывается список дальнейших вариантов действий. Выбираем в нем пункт «Из мастера подключения данных».
  3. Запускается Мастер подключения данных. В перечне типов источников данных выбираем «ODBC DSN». После этого щелкаем по кнопке «Далее».
  4. Открывается окно Мастера подключения данных, в котором нужно выбрать тип источника. Выбираем наименование «MS Access Database». Затем щелкаем по кнопке «Далее».
  5. Открывается небольшое окошко навигации, в котором следует перейти в директорию расположения базы данных в формате mdb или accdb и выбрать нужный файл БД. Навигация между логическими дисками при этом производится в специальном поле «Диски». Между каталогами производится переход в центральной области окна под названием «Каталоги». В левой области окна отображаются файлы, расположенные в текущем каталоге, если они имеют расширение mdb или accdb. Именно в этой области нужно выбрать наименование файла, после чего кликнуть на кнопку «OK».
  6. Вслед за этим запускается окно выбора таблицы в указанной базе данных. В центральной области следует выбрать наименование нужной таблицы (если их несколько), а потом нажать на кнопку «Далее».
  7. После этого открывается окно сохранения файла подключения данных. Тут указаны основные сведения о подключении, которое мы настроили. В данном окне достаточно нажать на кнопку «Готово».
  8. На листе Excel запускается окошко импорта данных. В нем можно указать, в каком именно виде вы хотите, чтобы данные были представлены:
    • Таблица;
    • Отчёт сводной таблицы;
    • Сводная диаграмма.

    Выбираем нужный вариант. Чуть ниже требуется указать, куда именно следует поместить данные: на новый лист или на текущем листе. В последнем случае предоставляется также возможность выбора координат размещения. По умолчанию данные размещаются на текущем листе. Левый верхний угол импортируемого объекта размещается в ячейке A1.

    После того, как все настройки импорта указаны, жмем на кнопку «OK».

  9. Как видим, таблица из базы данных перемещена на лист. Затем перемещаемся во вкладку «Данные» и щелкаем по кнопке «Подключения», которая размещена на ленте в блоке инструментов с одноименным названием.
  10. После этого запускается окно подключения к книге. В нем мы видим наименование ранее подключенной нами базы данных. Если подключенных БД несколько, то выбираем нужную и выделяем её. После этого щелкаем по кнопке «Свойства…» в правой части окна.
  11. Запускается окно свойств подключения. Перемещаемся в нем во вкладку «Определение». В поле «Текст команды», находящееся внизу текущего окна, записываем SQL команду в соответствии с синтаксисом данного языка, о котором мы вкратце говорили при рассмотрении Способа 1. Затем жмем на кнопку «OK».
  12. После этого производится автоматический возврат к окну подключения к книге. Нам остается только кликнуть по кнопке «Обновить» в нем. Происходит обращение к базе данных с запросом, после чего БД возвращает результаты его обработки назад на лист Excel, в ранее перенесенную нами таблицу.

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

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

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

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

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

Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

Да Нет

Как создать запрос в Экселе? Этот язык программирования уникален тем, что совместим со всеми новыми базами данных. Благодаря своим способностям SQL с Excel позволяет проводить многочисленные анализы и быстро собрать в необходимую последовательность разбросанные данные по таблицам. Способов создания запросов несколько.

Рассмотрим один из них, который делается на базовых инструментах Excel.

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

Нажимая на эту кнопку, запускаем мастер подключений данных.

На экране увидите новое окошко мастера подключений, и выбираем из предложенных вариантов «ODBC DSN». После выбора жмем «далее» и попадаем в следующее окно меню. Делаем выбор в пользу «MS Access Database», подтверждаем выбор, нажимая на кнопку «далее».

После всех вышеописанных действий перед нами выскочит окно

«Выбор базы данных». Переходим в этом окошке в «имя базы данных» и выбираем, как указано на рисунке ниже. Следует отметить, что выбор формата должен быть mdb, accdb. И соответственно выбираем, где лежит файл базы данных сначала диск, смотрим вниз окошка, а затем и нужную папку. Обнаружив необходимую папку – жмем «ОК»

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

В открывшемся меню мастера подключений находим кнопку «Готово». и жмем на нее.

Теперь мы попадаем на лист Excel и видим открытое окно «Импорт данных». Следующим действием нам предлагается выбрать нужный нам вариант просмотра данных. Вариантов три: таблица, отчет сводной таблицы и сводная диаграмма. Выбираем один из предлагаемых вариантов и указываем где мы хотим это видеть. Тут два варианта: текущий лист или новый лист. По умолчанию данные расположатся на текущем листе и начнутся с А1 ячейки. Жмем «ОК».

Мастер переместил данные таблицы из БД на наш лист. Следующим действием идем на «Данные», затем «Подключения» жмем «Подключения»

Таким образом, выходим на окно «Подключения к книге».

Тут видим название уже знакомой нашей базы данных, выбираем ее, если есть еще список других БД, и жмем на кнопку «свойства».

Выскакивает окно под названием «Свойства подключения».

Нам в этом окне нужна кнопка «Определение». Находим «Текст команды» и жмем «ОК».

Excel нас откинет к окну «Подключение к книге». Находим «Обновить»

Запускается операция, в результате которой из нашей базы данных будут выбраны те параметры, что мы заказали, и их результат появится в таблице ранее созданной.

Таким образом, запросы SQL в Excel выполнили свои задачи.

  1. Перейдите на вкладку «Данные» и выберите «Из других источников», как показано ниже.

  2. В выпадающем меню выберите “Из мастера подключения данных”.

  3. Откроется Мастер подключения данных. Из доступных вариантов выберите “ODBC DSN” и нажмите “Далее”.

  4. Появится окно «Подключение к источнику данных ODBC». Там будет показан список баз данных, доступных в вашей организации. Выберите соответствующую базу данных и нажмите “Далее”.

  5. Появится окно выбора базы данных и таблицы.

  6. Мы можем выбрать базу данных и таблицу, откуда хотим получать данные. Соответственно, выберите нужную базу данных и таблицу.

  7. В окне “Сохранить файл подключения к данным и завершить” выберите «Завершить». Это окно выберет имя файла на основе вашего выбора на предыдущих экранах.

  8. Появится окно импортирования данных, где мы можем выбрать нужные варианты и нажать OK.

  9. В выпадающем меню выберите “Из мастера подключения данных”.

  10. Перейдите на вкладку «Данные» и нажмите на «Соединения». В следующем окне нажмите на «Свойства».

  11. В следующем окне перейдите на вкладку «Определения».

  12. В поле “Текст команды” введите SQL-запрос и нажмите OK. Excel отобразит результат согласно запроса.

  13. Теперь перейдите в Microsoft Excel и проверьте, что результаты соответствуют указанному SQL-запросу.

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

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

  • Создание запросов SQL в интерфейсе Excel и напрямую к Excel таблицам
  • Автогенерация запросов SELECT и JOIN
  • Доступны JOIN, ORDER BY, DISTINCT, GROUP BY, SUM и другие операторы SQLite
  • Создание запросов в интуитивном редакторе с подстветкой синтаксиса
  • Обращение к любым таблицам Excel из дерева данных

Добавить «SQL запросы» в Excel 2016, 2013, 2010, 2007

Подходит для: Microsoft Excel 2016 – 2007, desktop Office 365 (32-бит и 64-бит).

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

Как работать с надстройкой:

  • Как превратить данные Excel в реляционную базу данных и подготовить их к работе с SQL запросами
  • Как создать и выполнить запрос SQL SELECT к таблицам Excel
  • Операторы Left Join, Order By, Group By, Distinct и другие SQLite команды в Excel
  • Как объединить две и более Excel таблиц с помощью надстройки «SQL запросы»

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

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

  1. Выделите диапазон данных > На вкладке «Главная» нажмите «Форматировать как таблицу» > Примените стиль таблицы.
  2. Выберите эту таблицу > Откройте вкладку «Конструктор» > Напечатайте имя таблицы.
    Напр., «КодТовара».
  3. Повторите эти шаги для каждого диапазона, который планируете использовать в запросах.
    «КодТовара», «ЦенаРозн», «ОбъемПродаж», т.д.
  4. Готово, теперь эти таблицы будут служить реляционной базой данных и готовы к SQL запросам.

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

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

  1. Нажмите кнопку «Выполнить SQL» на вкладке XLTools > Откроется окно редактора.
  2. В левой части окна находится дерево данных со всеми доступными таблицами Excel.
    Нажатием на узлы открываются/сворачиваются поля таблицы (столбцы).
  3. Выберите целые талицы или конкретные поля.
    По мере выбора полей, в правой части редактора автоматически генерируется запрос SELECT.
    Обратите внимание: редактор запросов SQL автоматически подсвечивает систаксис.
  4. Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.
  5. Нажмите кнопку «Выполнить» > Готово!

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

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

  • LEFT JOIN – объединить две и более таблиц по общему ключевому столбцу
  • ORDER BY – сортировка данных в выдаче запроса
  • DISTINCT – удаление дубликатов из результата запроса
  • GROUP BY – группировка данных в выдаче запроса
  • SUM, COUNT, MIN, MAX, AVG и другие операторы

Совет: вместо набора названий таблиц вручную, просто перетягивайте названия из дерева данных в область редактора SQL запросов.

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

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

  1. Нажмите «Выполнить SQL» на вкладке XLTools > Выберите поля, которые нужно включить в объединённую таблицу.
    По мере выбора полей, автоматически генерируется запрос SELECT и LEFT JOIN.
  2. Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.
  3. Нажмите «Выполнить» > Готово! Объединённая таблица появится в считанные секунды.

Появились вопросы или предложения? Оставьте комментарий ниже.

SQL-запросы к MS SQL Server из Excel. Подключение к внешним источникам данных

Для обработки данных в Excel требуется сначала получить сами данные. Часто такая информация находится во внешних источниках, а именно базах данных. В этой статье описывается подключение электронных таблиц к БД MS SQL Server.

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

Теперь на экране появилось окно мастера подключения к данным. Изначально требуется указать имя сервера и учетные сведения для подключения (если Вы их не знаете, то обратитесь к администратору БД).

После заполнения формы нажмите кнопку «Далее», чтобы перейти к выбору базы данных.

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

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

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

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

SQL-запрос

По умолчанию, при импорте из SQL Server, Excel выгружает всю таблицу. Можно изменить выборку, указав SQL-запрос.

Для этого во время импорта данных (предыдущий рисунок) необходимо кликнуть по кнопке «Свойства», если подключение создается впервые, либо, если файл подключения уже был добавлен к книге, на вкладке «Данные» в области «Получение внешних данных» кликнуть по кнопке «Подключения», выбрать нужное из списка и открыть его свойства.

В свойствах подключения на вкладке «Определение» в поле «Тип команды» поменяйте значение на «SQL», а в поле «Текст команды» введите SQL-код и нажмите «ОК».

Теперь, чтобы получить данные с помощью нового подключения, достаточно вызвать его из существующих подключений на вкладке «Данные» в области «Получение внешних данных». Дальнейшая процедура импорта аналогична рассмотренному выше.

  • < Назад
  • Вперёд >

Похожие статьи:Новые статьи:

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

Добавить комментарий

как передать параметры для запроса в SQL (Excel)

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

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

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

большинство веб-ответов на этот вопрос, похоже, говорят, что вы должны добавить «?»в вашем запросе, чтобы получить Excel, чтобы запросить у вас пользовательский параметр или разместить приглашение или ссылка на ячейку в [скобках], где должен быть параметр. Это может работать для запроса ODBC, но, похоже, не работает для OLE DB, возвращая «нет значения для одного или нескольких требуемых параметров» в первом экземпляре и «недопустимое имя столбца» xxxx «» или «неизвестный объект» xxxx «» в последних двух. Аналогично, использование мифических кнопок «параметры…» или «редактировать запрос…» также не является опцией, поскольку в этом случае они кажутся постоянно серыми. (Для справки, я использую Excel 2010, но с книгой Excel 97-2003 (*.xls))

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

Сначала добавьте строку над вашей внешней таблицей данных( или где угодно), где вы можете поместить приглашение параметра рядом с пустой ячейкой и кнопкой (разработчик- > вставить — > кнопка (управление формой) — вам может потребоваться включить вкладку Разработчик, но вы можете узнать, как это сделать в другом месте), например Итак:

затем выберите ячейку в области внешние данные (синий), затем откройте данные — > обновить все (раскрывающийся список) — >свойства соединения…, чтобы посмотреть на ваш запрос. Код в следующем разделе предполагает, что у вас уже есть параметр в вашем запросе (свойства соединения->определение->текст команды) в форме «где (DB_TABLE_NAME.Field_Name = ‘параметр запроса по умолчанию’)» (включая круглые скобки). Ясно » DB_TABLE_NAME.Имя_поля» и «запрос по умолчанию Параметр » должен отличаться в коде на основе имени таблицы базы данных, имени поля значения базы данных (столбца) и некоторого значения по умолчанию для поиска при открытии документа (если у вас есть набор автоматического обновления). Обратите внимание на » DB_TABLE_NAME.Значение «Field_Name», которое вам понадобится в следующем разделе, а также» имя соединения » вашего запроса, которое можно найти в верхней части диалогового окна.

закройте свойства соединения и нажмите Alt+F11, чтобы открыть редактор VBA. Если вы еще не на нем, щелкните правой кнопкой мыши на названии листа, содержащего вашу кнопку в окне «проект», и выберите»Просмотреть код». Вставьте следующий код в окно кода (копирование рекомендуется, так как одинарные/двойные кавычки рискованны и необходимы).

Sub RefreshQuery()
 Dim queryPreText As String
 Dim queryPostText As String
 Dim valueToFilter As String
 Dim paramPosition As Integer
 valueToFilter = "DB_TABLE_NAME.Field_Name ="

 With ActiveWorkbook.Connections("Connection name").OLEDBConnection
     queryPreText = .CommandText
     paramPosition = InStr(queryPreText, valueToFilter) + Len(valueToFilter) - 1
     queryPreText = Left(queryPreText, paramPosition)
     queryPostText = .CommandText
     queryPostText = Right(queryPostText, Len(queryPostText) - paramPosition)
     queryPostText = Right(queryPostText, Len(queryPostText) - InStr(queryPostText, ")") + 1)
     .CommandText = queryPreText & " '" & Range("Cell reference").Value & "'" & queryPostText
 End With
 ActiveWorkbook.Connections("Connection name").Refresh
End Sub

заменить » DB_TABLE_NAME.Field_Name » и «имя соединения» (в двух местах) с вашими значениями (двойные кавычки и знак пробела и равенства должны быть включены).

заменить » ссылка на ячейку» с ячейкой, куда пойдет ваш параметр (пустая ячейка с самого начала) — моя была второй ячейкой в первой строке, поэтому я поставил «B1» (опять же, двойные кавычки необходимы).

сохранить и закрыть редактор VBA.

введите свой параметр в соответствующую ячейку.

щелкните правой кнопкой мыши кнопку, чтобы назначить суб RefreshQuery в качестве макроса, затем нажмите кнопку. Запрос должен обновлять и отображать правильные данные!

заметки:
Используя весь имя параметра фильтра («DB_TABLE_NAME.Field_Name =») необходимо только в том случае, если в вашем запросе есть соединения или другие вхождения знаков равенства, иначе будет достаточно только знака равенства, и вычисление Len () будет излишним.
Если ваш параметр содержится в поле, которое также используется для объединения таблиц, вам нужно будет изменить строку» paramPosition = InStr(queryPreText, valueToFilter) + Len(valueToFilter) — 1 «в коде на» paramPosition = InStr(Right(.свойство CommandText, Лен.(CommandText) — InStrRev(.CommandText,» где»)), valueToFilter) + Len (valueToFilter) — 1 + InStr(.CommandText, «WHERE») » так что он ищет только valueToFilter после «где».

этот ответ был создан с помощью «BaconBits» datapig, где я нашел базовый код для обновления запроса.

Excel — Подключение и получение данных с SQL сервера | Info-Comp.ru

Мало пользователей, да и начинающих программистов, которые знают о возможности 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 сервера очень удобно и главное быстро, надеюсь, Вам пригодятся эти знания полученные в сегодняшнем уроке. Удачи!

Нравится1Не нравится1 Сервер

sql — преобразование формулы Excel в запрос SQL

Переполнение стека

  1. Около

  2. Продукты

  3. Для команд

  1. Переполнение стека
    Общественные вопросы и ответы

  2. Переполнение стека для команд
    Где разработчики и технологи делятся частными знаниями с коллегами

  3. Вакансии
    Программирование и связанные с ним технические возможности карьерного роста

  4. Талант
    Нанимайте технических специалистов и создавайте свой бренд работодателя

  5. Реклама
    Обратитесь к разработчикам и технологам со всего мира

  6. О компании

.

Передача параметра из ячейки в книге Excel в SQL-запрос

Переполнение стека

  1. Около

  2. Продукты

  3. Для команд

  1. Переполнение стека
    Общественные вопросы и ответы

  2. Переполнение стека для команд
    Где разработчики и технологи делятся частными знаниями с коллегами

  3. Вакансии
    Программирование и связанные с ним технические возможности карьерного роста

  4. Талант
    Нанимайте технических специалистов и создавайте свой бренд работодателя

  5. Реклама
    Обратитесь к разработчикам и технологам со всего мира

  6. О компании

Загрузка…

  2. Авторизоваться
    зарегистрироваться

  3. текущее сообщество

.

Как создать Microsoft Query в Excel (Excel Query)

Вы можете использовать Microsoft Query в Excel для извлечения данных из книги Excel, а также из внешних источников данных с помощью операторов SQL SELECT . Созданные таким образом запросы Excel можно обновлять и запускать повторно, что делает их удобным и эффективным инструментом в Excel.

Microsoft Query позволяет использовать SQL непосредственно в Microsoft Excel , обрабатывая таблицы как таблицы, для которых вы можете запускать операторы Select с JOIN, UNION и т. Д.Часто операторы Microsoft Query более эффективны, чем формулы Excel или макрос VBA.

Microsoft Query (он же MS Query, он же Excel Query) на самом деле является оператором SQL SELECT. Excel, а также Access используют поставщиков Windows ACE.OLEDB или JET.OLEDB для выполнения запросов. Это невероятный, часто неиспользованный инструмент, недооцененный многими пользователями!

Используя MS Query в Excel, вы можете извлекать данные из различных источников, таких как:

  • Файлы Excel — вы можете извлекать данные из внешних файлов Excel, а также выполнять запрос SELECT в текущей книге
  • Access — вы можете извлекать данные из файлов базы данных Access
  • MS SQL Server — вы можете извлекать данные из таблиц Microsoft SQL Server
  • CSV и текст — вы можете загружать CSV или текстовые файлы в табличной форме

Как создать запрос Microsoft в Excel

В этом пошаговом руководстве я покажу вам, как создать Microsoft Query для извлечения данных из вашей текущей книги или внешнего файла Excel .

Я извлечу данные из внешнего файла Excel с именем MOCK DATA.xlsx . В этом файле у меня есть список покупателей мокапов мужского и женского пола. Я хочу создать простой запрос, чтобы вычислить, сколько мужчин и сколько женщин.

В приведенном ниже процессе показано, как можно создать запрос для текущей или внешней книги Excel. Однако процесс создания файлов MS Query for Access, SQL и Text (CSV) очень похож.

Откройте запрос MS (из других источников) мастер

Перейдите на вкладку ленты DATA и щелкните Из других источников .Выберите последний вариант из Microsoft Query .

Выберите источник данных

Затем нам нужно указать источник данных для нашего запроса Microsoft. Чтобы продолжить, выберите Файлы Excel .

Выберите исходный файл Excel

Теперь нам нужно выбрать файл Excel, который будет источником для нашего Microsoft Query. В моем примере я выберу свою текущую книгу , ту же, из которой я создаю свой MS Query.

Выберите столбцы для вашего запроса MS

Мастер теперь просит вас выбрать столбцы для вашего запроса MS.Если вы планируете позже изменить MS Query вручную, просто нажмите OK . В противном случае выберите свои столбцы.

Вернуть запрос или изменить запрос

Теперь у вас есть два варианта:

  1. Вернуть данные в Microsoft Excel это вернет результаты вашего запроса в Excel и завершит работу мастера

  2. Просмотр данных или редактирование запроса в Microsoft Query , это откроет окно Microsoft Query и позволит вам изменить Microsoft Query

Необязательно: Изменить запрос

Если вы выбрали опцию Просмотр данных или редактировать запрос в Microsoft Query , теперь вы можете открыть окно SQL Edit Query, нажав кнопку SQL .Когда вы закончите, нажмите кнопку возврата (ту, с открытой дверью).

Импорт данных

Когда вы закончите изменять свой оператор SQL (как я на предыдущем шаге). Нажмите кнопку Вернуть данные в окне Microsoft Query .
Это должно открыть окно Import Data , которое позволяет вам выбрать, когда данные должны быть выгружены.
Наконец, когда вы закончите, нажмите OK в окне Import Data , чтобы завершить выполнение запроса.Вы должны увидеть результат запроса в виде новой таблицы Excel:

Как и в окне выше, я вычислил, сколько записей в исходной таблице содержит Male и сколько Female.

Как видите, для достижения чего-то потенциально довольно простого требуется довольно много шагов. Следовательно, есть несколько альтернатив, благодаря мощности макроса VBA….

Надстройка Excel SQL

Чтобы упростить использование MS Query в Excel, я разработал надстройку Excel SQL.Надстройка позволяет создавать запросы MS быстрее и с меньшим количеством шагов. Не стесняйтесь проверить это.
Лента надстройки Excel SQL

Уловка с запросом MS

Если вы не хотите использовать AddIn, другой способ — создать эти запросы с помощью макроса VBA. Ниже приведен быстрый макрос, который позволит вам написать запрос в простом поле ввода VBA в выбранном диапазоне на вашем листе.

Просто используйте мой фрагмент кода VBA:

Подраздел ExecuteSQL ()
Атрибут ExecuteSQL.VB_ProcData.VB_Invoke_Func = «S \ n14»
‘AnalystCave.com
При ошибке Перейти к ErrorHandl
Развернуть SQL как строку, sConn как строку, qt как QueryTable
SQL = InputBox («Предоставьте свой SQL-запрос», «Выполните SQL-запрос»)
Если SQL = vbNullString, тогда выйдите из Sub
sConn = «OLEDB; Provider = Microsoft.ACE.OLEDB.12.0 ;; Password =; User ID = Admin; Data Source =» & _
ThisWorkbook.Path & «/» & ThisWorkbook.Name & «;» & _
«Mode = Share Deny Write; Extended Properties =» «Excel 12.0 Xml; HDR = YES» «;»
Установите qt = ActiveCell.Worksheet.QueryTables.Add (соединение: = sConn, назначение: = ActiveCell)
С qt
.CommandType = xlCmdSql
.CommandText = SQL
.Name = Int ((1000000000 — 1 + 1) * Rnd + 1).
.RefreshStyle = xlOverwriteCells
.Refresh BackgroundQuery: = Ложь
Конец с
Выход из подводной лодки
ErrorHandl: MsgBox «Ошибка:» и описание ошибки: Err.Clear
Конечный переводник

1

2

3

4

5

6

7

8

9

10

11

12

13

14

13

14

18

19

20

21

Sub ExecuteSQL ()

Атрибут ExecuteSQL.VB_ProcData.VB_Invoke_Func = «S \ n14»

‘AnalystCave.com

При ошибке GoTo ErrorHandl

Dim SQL As String, sConn As String, qt As QueryTable

SQL = InputBox («Provide your SQL Query» SQL Query «)

Если SQL = vbNullString, то выйдите из Sub

sConn =» OLEDB; Provider = Microsoft.ACE.OLEDB.12.0 ;; Password =; User ID = Admin; Data Source = «& _

ThisWorkbook.Path & «/» & ThisWorkbook.Name & «;» & _

«Mode = Share Deny Write; Extended Properties =» «Excel 12.0 Xml; HDR = YES «»; «

Установить qt = ActiveCell.Worksheet.QueryTables.Add (Connection: = sConn, Destination: = ActiveCell)

With qt

.CommandType = xlCmdSql

.Comm SQL

.

.Name = Int ((1000000000 — 1 + 1) * Rnd + 1)

.RefreshStyle = xlOverwriteCells

.Refresh BackgroundQuery: = False

End With

Exit Sub

ErrorHandl: ErrorHandl: Msg Err.Описание: Err.Clear

Концевой переводник

Просто создайте новый модуль VBA и вставьте приведенный выше код. Вы можете запустить его, нажав CTRL + SHIFT + S Keyboardhortcut или добавив макрос на панель быстрого доступа.

Изучение SQL с помощью Excel

Создание MS Queries — это одно, но вам нужно хорошо разбираться в языке SQL, чтобы использовать его истинный потенциал. Я рекомендую использовать простую базу данных Excel (например, Northwind) и практиковать различные запросы с JOIN.

Power Query (получение и преобразование)

Другой способ выполнения запросов — использование Microsoft Power Query (также известного в Excel 2016 и выше как Get and Transform ). Надстройка, предоставляемая Microsoft, требует знания языка SQL, позволяя вам пролистывать данные, которые вы хотите преобразовать.

MS Query и выводы Power Query

MS Query Pros : Power Query — это замечательный инструмент , однако он не делает полностью недействительным Microsoft Queries.Более того, иногда использовать Microsoft Queries быстрее и удобнее, и вот почему :

  • Запросы Microsoft более эффективны, если вы знаете SQL . Хотя вы можете щелкнуть мышью, чтобы преобразовать данные с помощью Power Query, кто-то, кто знает SQL, вероятно, будет намного быстрее в написании подходящего запроса SELECT
  • Вы не можете повторно запустить Power Queries без надстройки . Хотя это, очевидно, будет менее актуальным, вероятно, через пару лет (в более новых версиях Excel), в настоящее время, если у вас нет надстройки, вы не сможете редактировать или повторно запускать запросы, созданные в Power Query

    • .

MS Query Cons : Microsoft Query отстает от надстройки Power Query в некоторых других аспектах:

  • Power Query имеет более удобный пользовательский интерфейс .Хотя Power Queries относительно легко создать, MS Query Wizard похож на веб-сайт 90-х годов

    • .

  • Power Query складывает операции друг над другом, что обеспечивает более удобные изменения . Пока MS Query работает или просто не компилируется, Power Query складывает каждую операцию преобразования, обеспечивая видимость вашей задачи преобразования данных и упрощая добавление / удаление операций

Короче говоря, я рекомендую изучить Power Query, если вы не чувствуете себя комфортно с SQL.Если вы продвинуты в SQL, я думаю, вам будет удобнее использовать good ole Microsoft Queries. Я бы сравнил это с многовековой дискуссией между разработчиками командной строки и разработчиками графического интерфейса …

.

No related posts.

Share :

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *