Sql запрос в excel: Импорт данных из базы данных с помощью запроса на ее языке (Power Query)

Импорт данных из базы данных с помощью запроса на ее языке (Power Query)

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Еще…Меньше

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

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

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

  1. org/ListItem»>

    Выберите Данные> Получить данные > из баз данных > из SQL Server базы данных.Появится База данных SQL диалоговое окно.

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

  3. Выберите элемент Дополнительные параметры .

  4. Введите запрос базы данных в поле SQL базы данных.


    В зависимости от базы данных могут быть доступны дополнительные параметры.

  5. org/ListItem»>

    Нажмите ОК.

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

  7. Данные возвращаются в редактор Power Query.

  8. Вы можете сформировать данные, а затем нажать кнопку Применить & Закрыть, чтобы сохранить изменения и импортировать данные на этот таблицу.


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

Чтобы изменить содержимое SQL заявления:

  1. Выберите ячейку в запросе, основанном на источнике базы данных.

  2. Выберите запрос> изменить. Появится редактор Power Query.

  3. В области Параметры запроса слева в области Примененные шаги щелкните правой кнопкой мыши шаг Источник и выберите изменить Параметры. Появится диалоговое окно Навигатор.

  4. org/ListItem»>

    Внести изменения в поле выписки SQL, а затем выберите ОК.

    Новые результаты появятся в области Предварительный просмотр данных.

  5. Завершив редактирование запроса, выберите главная > Закрыть и загрузить.

    Новые результаты появятся на этом же.

См. также



Справка по Power Query для Excel


Импорт данных из внешних источников


Обновление подключения к внешним данным в Excel


Импорт данных с помощью запроса на языке базы данных (docs.com)


Формируем SQL запросы в Excel

Время прочтения: 5 мин.

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

Выйти из подобной ситуации поможет Excel.

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

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

Создаем в MS Excel книгу, на листе которой размещаем таблицу с идентификаторами, по которым будем в дальнейшем формировать запрос (если у нас есть уникальный идентификатор, для обеспечения максимальной скорости обработки таблицу лучше представить в виде одного столбца), сохраняем книгу в формате *.xlsm, после чего приступаем к созданию макроса.

Через меню «Разработчик» открываем встроенный VBA редактор и начинаем творить.

Sub job_sql()  — Пусть наш макрос называется job_sql.

Пропишем переменные для подключения к БД, записи данных и запроса:

Dim cn As ADODB. Connection
Dim rs As ADODB.Recordset
Dim sql As String

Опишем параметры подключения:

sql = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=Storoge.company.ru\ Storoge."

Объявим процедуру свойства, для присвоения значения:

Set cn = New ADODB.Connection
    cn.Provider = " SQLOLEDB.1"
    cn.ConnectionString = sql
    cn.ConnectionTimeout = 0
    cn.Open

Вот теперь можно приступать непосредственно к делу.

Организуем цикл:

For i = 2 To 1000

Как вы уже поняли конечное значение i=1000 здесь только для примера, а в реальности конечное значение соответствует количеству строк в Вашей таблице. В целях унификации можно использовать автоматический способ подсчета количества строк, например, вот такую конструкцию:

Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

Тогда открытие цикла будет выглядеть так:

For i = 2 To LastRow

Как я уже говорил выше MS Excel является мощным инструментом для аналитики, и возможности Excel VBA не заканчиваются на простом переборе значений или комбинаций значений. При наличии известных Вам закономерностей можно ограничить объем выгружаемой из БД информации путем добавления в макрос простых условий, например:

If Cells(i, 2) = "Ваше условие" Then

Итак, мы определились с объемом и условиями выборки, организовали подключение к БД и готовы формировать запрос. Предположим, что нам нужно получить информацию о размере ежемесячного платежа [Ежемесячный платеж] из таблицы [PAYMENTS].[refinans_credit], но только по тем случаям, когда размер ежемесячного платежа больше 0

sql = "select [Ежемесячный платеж] from [PAYMENTS].[refinans_credit] " & _
"where [Ежемесячный платеж]>0 and [Номер заявки] ='" & Cells(i, 1) & "' "

Если значений для формирования запроса несколько, соответственно прописываем их в запросе:

"where [Ежемесячный платеж]>0 and [Номер заявки] = '" & Cells(i, 1) & "' " & _
" and [Дата платежа]='" & Cells(i, 2) & "'"

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

Cells(i, 3) = sql

в третьем столбце записываются запросы.

Выполняем SQL запрос:

Set rs = cn.Execute(sql)

А чтобы хоть как-то наблюдать за выполнением макроса выведем изменение i в статус-бар

Application.StatusBar = "Execute script ..." & i
Application.ScreenUpdating = False

Теперь нам нужно записать полученные результаты. Для этого будем использовать оператор Do While:

j = 0
Do While Not rs.EOF
For ii = 0 To rs.Fields.Count - 1
Cells(i, 4 + j + ii) = rs.Fields(0 + ii) '& ";"

Указываем ячейки для вставки полученных данных (4 в примере это номер столбца с которого начинаем запись результатов)

Next ii
j = j + rs.Fields.Count
s.MoveNext
Loop
rs.Close
End If

— закрываем цикл If, если вводили дополнительные условия

Next i
cn.Close
Application.StatusBar = "Готово"
End Sub

— закрываем макрос.

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

В приведенном примере для авторизации при подключении к БД используется доменная аутентификация.

А как быть если для аутентификации необходимо ввести логин и пароль? Ничего невозможного нет. Изменим часть макроса, которая отвечает за подключение к БД следующим образом:

sql = "Provider= SQLOLEDB.1;Password=********;User ID=********;Data Source= Storoge.company.ru\ Storoge;APP=SFM"

Но в этом случае при использовании макроса возникает риск компрометации Ваших учетных данных. Поэтому лучше программно удалять учетные данные после выполнения макроса. Разместим поля для ввода пароля и логина на листе и изменим макрос следующим образом:

sql = "Provider= SQLOLEDB.1;Password=" & Sheets("Лист аутентификации").TextBox1.Value & ";UserЛист аутентификации ").TextBox2.Value & ";Data Source= Storoge.company.ru\ Storoge;APP=SFM"

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

Sheets("Выгрузка").TextBox1.Value = ""
Sheets("Выгрузка").TextBox2.Value = ""

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

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

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

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

Надстройка XLTools SQL Queries расширяет возможности Excel с помощью языка структурированных запросов:

  • Запуск SQL-запросов в интерфейсе Excel и непосредственно в таблицах Excel

  • Автоматически генерировать операторы SELECT и JOIN

  • Используйте JOIN, ORDER BY, DISTINCT, GROUP BY, SUM и другие операторы SQLite

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

  • Адресация любых таблиц Excel из древовидного списка

Прежде чем начать, добавьте SQL-запросы в Excel

SQL-запросы — это одна из более чем 20 функций надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, настольном Office 365.

— бесплатная пробная версия дает вам 14 дней полного доступа ко всем функциям.

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

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

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

  2. Выберите таблицу. Откройте вкладку «Проект». Введите имя таблицы.

    «ID продукта»

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

    таблицы «RetailPrice», «SalesVolume» и др.

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

Как создать и запустить SQL SELECT для таблиц Excel

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

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

  2. С левой стороны найдите древовидное представление всех доступных таблиц.

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

  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 Queries

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

  1. Нажмите «Выполнить SQL» на вкладке XLTools. Выберите поля, которые вы хотите видеть в объединенной таблице.

    Когда вы отмечаете поля, SELECT и LEFT JOIN генерируются автоматически.

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

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

Есть лучший способ использовать SQL в Excel. Вот как.

Вы когда-нибудь работали с данными в Excel и думали про себя: «Это было бы намного проще сделать с помощью SQL»?

С помощью инструмента, который я собираюсь вам показать, вы действительно можете использовать SQL в Excel. Это удобно, мощно и даже поставляется с IntelliSense.

Первый взгляд

Инструмент, о котором я говорю, называется QueryStorm. Позвольте мне показать вам, как это выглядит…

Аккуратно, правда?

Механизм SQL QueryStorm работает с таблицами Excel так, как если бы они были таблицами базы данных!

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

Выберите диапазон ячеек, содержащий данные, и нажмите Ctrl+T, чтобы преобразовать его в таблицу

Запрос к таблицам

Чтобы начать запрос к таблице Excel, просто нажмите кнопку SQL на ленте Excel.

Запуск нового SQL-скрипта

Это откроет новый SQL-скрипт в QueryStorm IDE, который можно использовать для запуска запросов к таблицам Excel.

Запрос таблиц Excel с помощью SQL

Под капотом QueryStorm использует механизм базы данных SQLite . Поскольку SQLite — это полноценный механизм базы данных SQL, все функции, которые вы ожидаете от базы данных SQL, доступны в QueryStorm. Сюда входят объединения, группировка, агрегирование и даже оконные функции.

Обновление таблиц

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

Это означает, что когда вы используете операторы INSERT/UPDATE/DELETE для изменения данных в таблице, изменения немедленно отображаются в Excel .

Это упрощает очистку и обработку данных рабочей книги без необходимости импортировать данные в базу данных и экспортировать их обратно в Excel.

Пользователи, более знакомые с вкладкой Data на ленте Excel, безусловно, могут использовать Power Query для выполнения таких операций, как объединение и слияние таблиц и очистка данных, но использование SQL таким образом часто оказывается более удобным и более мощным.

IDE

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

Вот где редактор кода QueryStorm сияет такими расширенными функциями, как:

  • завершение кода
  • подсветка синтаксиса
  • подсветка ошибок
  • совпадение скобок
  • фрагменты кода
  • форматирование кода
  • звездочка

S сниппеты, подсветка синтаксиса, автозавершение кода, подсветка ошибок. Устранение неоднозначности столбца. Звездное расширение

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

Что насчет производительности?

На производительность влияет количество возвращаемых данных и объем работы, выполняемой SQL-запросом. Давайте рассмотрим оба аспекта…

Пропускная способность данных

В качестве примера пропускной способности данных, на моем однолетнем ноутбуке (i7, 11850h) простой запрос на выборку, возвращающий 100 тыс. строк и 10 столбцов, занимает около 0,6 секунды. выполнять. Это достаточно быстро, чтобы вам редко приходилось об этом беспокоиться.

При работе с большими наборами данных важно учитывать, является ли установленная версия Excel 32-разрядной или 64-разрядной. 32-разрядная версия Excel имеет жесткое ограничение в ~3 ГБ доступной памяти, что может иметь значение при работе с большими наборами данных. Во избежание подобных проблем рекомендуется использовать 64-разрядную версию Excel.

Автоматическое индексирование столбцов

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

Однако для ускорения выполнения таких запросов все столбцы автоматически индексируются . Это делает объединение таблиц (и фильтрацию таблиц в целом) чрезвычайно быстрыми.

По-прежнему можно написать запрос, который будет работать плохо (например, объединение больших таблиц путем частичного совпадения строк не позволит использовать индекс), но ситуации, когда вам нужно это сделать, довольно редки.

Функция Query() в Excel

До сих пор я обсуждал QueryStorm со стороны IDE. Но вы также можете использовать SQL в Excel как функцию с помощью функции Query(), которую вы можете получить в магазине расширений QueryStorm.

Работает аналогично функции query() в таблицах Google, за исключением того, что она более мощная, поскольку позволяет работать с несколькими таблицами и полностью поддерживает SQL.

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

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

Вот видео, показывающее функцию в действии:

Использование функции Query() не требует лицензии для QueryStorm IDE. Для этого даже не требуется установка IDE. Все, что требуется, — это бесплатная среда выполнения QueryStorm, которую вы используете для установки и запуска пакета Windy. Query .

Функция Query использует под капотом тот же движок SQLite, поэтому производительность такая же, как и в IDE.

Подключение к внешним базам данных

Помимо использования механизма SQLite, QueryStorm также поддерживает подключение к внешним базам данных SQL, включая SQL Server, MySQL, Postgres, Oracle, Access и т. д. Это упрощает запрос и импорт данных базы данных из Excel. .

Двусторонняя видимость данных

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

Функции базы данных Excel

Еще одна полезная вещь, которую вы можете сделать, — использовать SQL для написания пользовательских функций Excel, которые возвращают данные из баз данных.

Вы можете использовать эти функции самостоятельно, но вы также можете опубликовать их, чтобы сделать их доступными для ваших коллег и клиентов.

Для их установки и использования достаточно установить среду выполнения QueryStorm.

Цены

Для использования QueryStorm IDE требуется лицензия. Тем не менее, бесплатная версия сообщества доступна для личного использования и для небольших компаний. Компаниям с годовым доходом более 1 миллиона долларов США требуется платная лицензия, однако в крупных компаниях для ознакомления доступна бесплатная 14-дневная пробная версия, которая открывает все функции.