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

Подключение Excel к SQL Server

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

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

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

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

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

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

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

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

SQL-запрос

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

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

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

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

• Подключение MySQL в Excel

• Критерий Манна-Уитни
• Подключение MySQL в Excel
• Подключение Excel к SQL Server

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT * FROM [SHEET$] 

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

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

SELECT * FROM [SHEET$] WHERE [COLUMN NAME] = 'VALUE' 

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

• SHEET с именем электронной таблицы, к которой вы хотите получить доступ.
• COLUMN NAME столбцом, содержащим значение, которое вы хотите найти. Столбцы в первой строке листа Excel идентифицируются как имена столбцов таблицы.
• VALUE со значением, которое вы хотите найти.

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

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

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

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE' 

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

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

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

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

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

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

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

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE' 

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

SELECT * FROM [SHEET$] WHERE [COLUMN1] IS NOT NULL OR [COLUMN2] IS NOT NULL 

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

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

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

Создание запроса с параметрами в Microsoft Query

При запросе данных в Excel можно использовать входное значение ( параметр), чтобы указать что-то о запросе. Для этого нужно создать запрос с параметрами в Microsoft Query.

• Параметры используются в предложении WHERE запроса— они всегда работают в качестве фильтра для извлечения данных.
• Параметры могут запрашивать у пользователя входное значение при запуске или обновлении запроса, использовать константы в качестве входного значения или использовать содержимое указанной ячейки в качестве входного значения.
• Параметр является частью запроса, который он изменяет, и его нельзя повторно использовать в других запросах.

Примечание Если вы хотите создать запросы с параметрами другим способом, см. создание запроса с параметрами (Power Query).

Последовательность действий

1. Щелкните Данные >Получить & Преобразование данных >Получить данные >из других источников > из Microsoft Query.
2. Следуйте шагам мастера запросов. На экране Мастер запросов — готово выберите Просмотр данных или изменение запроса в Microsoft Query и нажмите кнопку Готово. Откроется окно Microsoft Query и отобразит запрос.
3. Нажмите кнопку>SQL. В диалоговом SQL найдите предложение WHERE — строку, которая начинается со слова WHERE, обычно в конце SQL кода. Если предложение WHERE не существует, добавьте его, введя WHERE в новой строке в конце запроса.
4. После where введите имя поля, оператор сравнения (=, , LIKE и т. д.) и одно из следующих данных:
   • Для запроса generic parameter (?) введите вопросии (?). В подсказке, которая появляется при запуске запроса, не отображается полезная фраза.
   • Введите фразу, заключенную в квадратные скобки, для запроса параметра, который помогает вводить допустимые данные. Фраза отображается в запросе с параметрами при запуске запроса.
5. Завершив добавление условий с параметрами в предложение WHERE, нажмите кнопку ОК, чтобы запустить запрос. Excel запрос на в качестве значения для каждого параметра, Microsoft Query отобразит результаты.
6. Когда вы будете готовы загрузить данные, закройте окно Microsoft Query, чтобы вернуться к Excel. Откроется диалоговое окно Импорт данных.
7. Чтобы просмотреть параметры, нажмите кнопку Свойства. Затем в диалоговом окне Свойства подключения на вкладке Определение нажмите кнопку Параметры.
8. В диалоговом окне Параметры отображаются параметры, используемые в запросе. Выберите параметр в области Имя параметра, чтобы просмотреть или изменить параметр How value is obtained. Вы можете изменить запрос параметра, ввести определенное значение или указать ссылку на ячейку.
9. Нажмите кнопку ОК, чтобы сохранить изменения и закрыть диалоговое окно Параметры, а затем в диалоговом окне Импорт данных нажмите кнопку ОК, чтобы отобразить результаты запроса Excel.

Теперь в книге есть запрос с параметрами. При запуске запроса или обновлении подключения к данным Excel проверяет параметр, чтобы завершить предложение WHERE запроса. Если параметр запросит значение, Excel отобразит диалоговое окно Введите значение параметра для сбора входных данных. Вы можете ввести значение или щелкнуть ячейку со значением. Вы также можете указать, что указанное значение или ссылка всегда должны использоваться, а при использовании ссылки на ячейку можно указать, что Excel должно автоматически обновлять подключение к данным (то есть повторно выполнить запрос) при внесении изменений в указанную ячейку.