Excel запросы sql с параметрами

Create a parameter query in Microsoft Query

When you query data in Excel, you might want to use an input value — a parameter — to specify something about the query. To do this, you create a parameter query in Microsoft Query:

Parameters are used in the query’s WHERE clause – they always function as a filter for retrieved data.

Parameters can prompt the user for an input value when the query is run or refreshed, use a constant as the input value, or use the contents of a specified cell as the input value.

A parameter is part of the query it modifies, and cannot be reused in other queries.

Note If you want the other way to create parameter queries, see Create a parameter query (Power Query).

Click Data > Get & Transform Data > Get Data > From Other Sources > From Microsoft Query.

Follow the Query Wizard steps. On the Query Wizard – Finish screen, select View data or edit query in Microsoft Query and then click Finish. The Microsoft Query window opens and displays your query.

Click View > SQL. In the SQL dialog box that appears, find the WHERE clause – a line starting with the word WHERE, typically at the end of the SQL code. If there is no WHERE clause, add one by typing WHERE on a new line at the end of the query.

After WHERE, type the field name, a comparison operator (=, , LIKE, etc.), and one of the following:

For a generic parameter prompt, type a question mark (?). No helpful phrase is displayed in the prompt that appears when the query is run.

For a parameter prompt that helps people provide valid input, type a phrase enclosed in square brackets. The phrase displays in the parameter prompt when the query is run.

After you finish adding conditions with parameters to the WHERE clause, click OK to run the query. Excel prompts you to provide a value for each parameter, then Microsoft Query displays the results.

When you are ready to load the data, close the Microsoft Query window to return the results to Excel. The Import Data dialog box opens.

To review your parameters, click Properties. Then in the Connection Properties dialog box, on the Definition tab click Parameters.

The Parameters dialog box displays the parameters used in the query. Select a parameter under Parameter name to review or change How parameter value is obtained. You can change the parameter prompt, enter a specific value, or specify a cell reference.

Click OK to save your changes and close the Parameters dialog box, then in the Import Data dialog box click OK to display the query results in Excel.

Источник

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

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

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

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

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

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

Щелкните Данные > Получить & Преобразование данных > Получить данные > из других источников > из Microsoft Query.

Следуйте шагам мастера запросов. На экране Мастер запросов — готово выберите Просмотр данных или изменение запроса в Microsoft Query и нажмите кнопку Готово. Откроется окно Microsoft Query и отобразит запрос.

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

После where введите имя поля, оператор сравнения (=, , LIKE и т. д.) и одно из следующих данных:

Для запроса generic parameter (?) введите вопросии (?). В подсказке, которая появляется при запуске запроса, не отображается полезная фраза.

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

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

Когда вы будете готовы загрузить данные, закройте окно Microsoft Query, чтобы вернуться к Excel. Откроется диалоговое окно Импорт данных.

Чтобы просмотреть параметры, нажмите кнопку Свойства. Затем в диалоговом окне Свойства подключения на вкладке Определение нажмите кнопку Параметры.

В диалоговом окне Параметры отображаются параметры, используемые в запросе. Выберите параметр в области Имя параметра, чтобы просмотреть или изменить параметр How value is obtained. Вы можете изменить запрос параметра, ввести определенное значение или указать ссылку на ячейку.

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

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

Источник

how to pass parameters to query in SQL (Excel)

I «linked» Excel to Sql and it worked fine — I wrote some SQL script and it worked great. All I want to do is to pass parameter to query. Like every time I make refresh I want to be able to pass parameter (filter condition) to Sql Query. In «Connection Properties» Parameters button is disabled. So I can’t make parameter query. Can Anyone help me?

2 Answers 2

This post is old enough that this answer will probably be little use to the OP, but I spent forever trying to answer this same question, so I thought I would update it with my findings.

This answer assumes that you already have a working SQL query in place in your Excel document. There are plenty of tutorials to show you how to accomplish this on the web, and plenty that explain how to add a parameterized query to one, except that none seem to work for an existing, OLE DB query.

So, if you, like me, got handed a legacy Excel document with a working query, but the user wants to be able to filter the results based on one of the database fields, and if you, like me, are neither an Excel nor a SQL guru, this might be able to help you out.

Most web responses to this question seem to say that you should add a “?” in your query to get Excel to prompt you for a custom parameter, or place the prompt or the cell reference in [brackets] where the parameter should be. This may work for an ODBC query, but it does not seem to work for an OLE DB, returning “No value given for one or more required parameters” in the former instance, and “Invalid column name ‘xxxx’” or “Unknown object ‘xxxx’” in the latter two. Similarly, using the mythical “Parameters…” or “Edit Query…” buttons is also not an option as they seem to be permanently greyed out in this instance. (For reference, I am using Excel 2010, but with an Excel 97-2003 Workbook (*.xls))

What we can do, however, is add a parameter cell and a button with a simple routine to programmatically update our query text.

First, add a row above your external data table (or wherever) where you can put a parameter prompt next to an empty cell and a button (Developer->Insert->Button (Form Control) – You may need to enable the Developer tab, but you can find out how to do that elsewhere), like so:

Next, select a cell in the External Data (blue) area, then open Data->Refresh All (dropdown)->Connection Properties… to look at your query. The code in the next section assumes that you already have a parameter in your query (Connection Properties->Definition->Command Text) in the form “WHERE (DB_TABLE_NAME.Field_Name = ‘Default Query Parameter’)” (including the parentheses). Clearly “DB_TABLE_NAME.Field_Name” and “Default Query Parameter” will need to be different in your code, based on the database table name, database value field (column) name, and some default value to search for when the document is opened (if you have auto-refresh set). Make note of the “DB_TABLE_NAME.Field_Name” value as you will need it in the next section, along with the “Connection name” of your query, which can be found at the top of the dialog.

Close the Connection Properties, and hit Alt+F11 to open the VBA editor. If you are not on it already, right click on the name of the sheet containing your button in the “Project” window, and select “View Code”. Paste the following code into the code window (copying is recommended, as the single/double quotes are dicey and necessary).

Replace “DB_TABLE_NAME.Field_Name” and «Connection name» (in two locations) with your values (the double quotes and the space and equals sign need to be included).

Replace «Cell reference» with the cell where your parameter will go (the empty cell from the beginning) — mine was the second cell in the first row, so I put “B1” (again, the double quotes are necessary).

Save and close the VBA editor.

Enter your parameter in the appropriate cell.

Right click your button to assign the RefreshQuery sub as the macro, then click your button. The query should update and display the right data!

Notes: Using the entire filter parameter name («DB_TABLE_NAME.Field_Name =») is only necessary if you have joins or other occurrences of equals signs in your query, otherwise just an equals sign would be sufficient, and the Len() calculation would be superfluous. If your parameter is contained in a field that is also being used to join tables, you will need to change the «paramPosition = InStr(queryPreText, valueToFilter) + Len(valueToFilter) — 1» line in the code to «paramPosition = InStr(Right(.CommandText, Len(.CommandText) — InStrRev(.CommandText, «WHERE»)), valueToFilter) + Len(valueToFilter) — 1 + InStr(.CommandText, «WHERE»)» so that it only looks for the valueToFilter after the «WHERE».

This answer was created with the aid of datapig’s “BaconBits” where I found the base code for the query update.

Источник

Финансы в Excel

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

Вложения:

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

Последние версии Excel включают в себя много средств для анализа внешних данных – см.ленту Данные, Получение внешних данных и Подключения. Да и в старых версиях также присутствовали различные инструменты для работы с базами данных, например, Microsoft Query.
Насколько мы можем судить, как старые, так и новые средства особой популярности и распространения не получили. Скорее всего из-за нестандартного интерфейса и слабой связи с текущими данными электронных таблиц. Возможно, мы сами просто плохо освоили эти инструменты, но и опыт других пользователей показывает, что до сих пор есть объективные трудности при организации процессов анализа внешних данных в Excel. Привлечение программистов обычно позволяет настроить работу в строго ограниченных рамках, на уровне жестких запросов.

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

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

Основные возможности надстройки Excelfin SQL Data версии 1.0

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

Файл надстройки sqldata.xlam совместим со всеми версиями Excel. Устанавливается стандартными средствами Excel.

Настройка подключения к базе данных

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

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

Именованные ячейки для указания параметров подключения:

• clConnectionString – текстовая строка подключения ODBC/OLE DB. С помощью этого параметра можно подключиться к различным источникам данных. Наиболее полный справочник строк подключения можно найти на тематическом сайте https://www.connectionstrings.com/

Остальные параметры касаются подключения к данным SQL Server, в этом случае именованная ячейка clConnectionString должна отсутствовать в рабочей книге.

• clServer – имя сервера
• clDatabase – имя базы данных
• clUser – пользователь при использовании аутентификации SQL Server
• clPassword – пароль при использовании аутентификации SQL Server

Если на SQL-сервере используется метод аутентификации Windows, то именованные ячейки clUser и clPassword должны отсутствовать в рабочей книге

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

Текст SQL-запроса

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

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

• clSQLFilename – полный путь к файлу с текстом SQL-запроса. Если указано только имя файла, то он должен располагаться в одной папке с рабочей книгой Excel.

Второй вариант тем не менее имеет ряд преимуществ по использованию параметров через формулы Excel. Для хранения запроса непосредственно в файле рабочей книги она должна включать в свой состав лист с названием sql. Текст запроса должен храниться на этом листе в левой верхней ячейке (A1), но может содержать несколько строк (A2, A3 и т.д.), признаком окончания запроса является пустая строка. Окончательный текст запроса может быть вычислен с помощью формул обработки строк — см. Описание демонстрационного примера.

Параметры SQL-запроса

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

Значение для параметров запроса будет браться из именованной ячейки активной рабочей книги с тем же именем, но с добавкой префика param – т.е. paramИмяПараметра.

Получение данных

Для получения данных используется кнопка на отдельном пункте ленты Excelfin – SQL Data Import. Также можно нажать сочетание клавиш Ctrl+t.

При верной настройке всех параметров данные обновятся на листе с названием, указанном в именованной ячейке clDataSheet. Если это имя отсутствует в рабочей книге, то надстройка будет использовать лист с названием data.

Описание демонстрационного примера

Надстройка была разработана для использования в первую очередь с SQL Server, но доступны и другие варианты получения внешних данных. Для условного примера была использована популярная демонстрационная база данных NorthWind формата MDB (Access 2000). Строка подключения в отличие от стандартной позволяет по умолчанию, если не указан полный путь, искать файл с данными в каталоге активной рабочей книги:

Текст SQL-запроса хранится на отдельном листе рабочей книги (sql).

В файле test1.xlsx текст запроса содержит один целочисленный параметр и хранится в виде символьной строки. В результирующий набор выбираются данные с ценой более $25.

В файле test1a.xlsx окончательный текст запроса вычисляется с использованием формулы таким образом, чтобы использовать 2 параметра: целочисленный и символьный. Текст запроса содержит 2 строки. Выбираются данные с ценой более $25 и город, содержащий в названии подстроку «an».

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

Источник