Queries and connections excel что это

Обновлено: 02.07.2024

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

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

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

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

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

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

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

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

Итак, мы определились с объемом и условиями выборки, организовали подключение к БД и готовы формировать запрос. Предположим, что нам нужно получить информацию о размере ежемесячного платежа [Ежемесячный платеж] из таблицы [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) & "'"

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

Power Query offers several ways to create and load Power queries into your workbook. You can also set default query load settings in the Query Options window.

Tip To tell if data in a worksheet is shaped by Power Query, select a cell of data, and if the Query context ribbon tab appears, then the data was loaded from Power Query.

Know which environment you're in Power Query is well-integrated into the Excel user interface, especially when you import data, work with connections, and edit Pivot Tables, Excel tables, and named ranges. To avoid confusion, it’s important to know which environment you are currently in, Excel or Power Query, at any point in time.

The familiar Excel worksheet , ribbon, and grid

The Power Query Editor ribbon and data preview

For example, manipulating data in an Excel worksheet is fundamentally different than Power Query. Furthermore, the connected data that you see in an Excel worksheet, may or may not have Power Query working behind the scenes to shape the data. This only occurs when you load the data to a worksheet or Data Model from Power Query.

Rename worksheet tabs It’s a good idea to rename worksheet tabs in a meaningful way, especially if you have a lot of them. It’s particularly important to clarify the difference between a worksheet of data, and a worksheet loaded from the Power Query Editor. Even if you have only two worksheets, one with an Excel table, called Sheet1, and the other a query created by importing that Excel table, called Table1, it’s easy to get confused. It’s always good practice to change the default names of worksheet tabs to names that make more sense to you. For example, rename Sheet1 to DataTable and Table1 to QueryTable. Now it’s clear which tab has the data and which tab has the query.

You can either create a query from imported data or create a blank query.

Create a query from imported data

This is the most common way to create a query.

Import some data. For more information, see Import data from external data sources.

Select a cell in the data and then select Query > Edit.

Create a blank query

You may want to just start from scratch. There are two ways to do this.

Select Data > Get Data > From Other Sources > Blank Query.

Select Data > Get Data > Launch Power Query Editor.

At this point, you can manually add steps and formulas if you know the Power Query M formula language well.

Or you can select Home and then select a command in the New Query group. Do one of the following.

Select New Source to add a data source. This command is just like the Data > Get Data command in the Excel ribbon.

Select Recent Sources to select from a data source you have been working with. This command is just like the Data > Recent Sources command in the Excel ribbon.

Select Enter Data to manually enter data. You might choose this command to try out the Power Query Editor independent of an external data source.

Assuming your query is valid and has no errors, you can load it back to a worksheet or Data Model.

Load a query from the Power Query Editor

In the Power Query Editor, do one of the following:

To load to a worksheet, select Home > Close & Load > Close & Load.

To load to a Data Model, select Home > Close & Load > Close & Load To.

In the Import Data dialog box, select Add this data to the Data Model.

Tip Sometimes the Load To command is dimmed or disabled. This can occur the first time you create a query in a workbook. If this occurs, select Close & Load, in the new worksheet, select Data > Queries & Connections > Queries tab, right click the query, and then select Load To. Alternatively, on the Power Query Editor ribbon select Query > Load To.

Load a query from the Queries and Connections pane

In Excel, you may want to load a query into another worksheet or Data Model.

In Excel, select Data > Queries & Connections, and then select the Queries tab.

In the list of queries, locate the query, right click the query, and then select Load To. The Import Data dialog box appears.

Decide how you want to import the data, and then select OK. For more information about using this dialog box, select the question mark (?).

There are several ways to edit a query loaded to a worksheet.

Edit a query from data in Excel worksheet

To edit a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit.

Edit a query from the Queries & Connections pane

You may find the Queries & Connections pane is more convenient to use when you have many queries in one workbook and you want to quickly find one.

In Excel, select Data > Queries & Connections, and then select the Queries tab.

In the list of queries, locate the query, right click the query, and then select Edit.

Edit a query from the Query Properties dialog box

In Excel, select Data > Data & Connections > Queries tab, right click the query and select Properties, select the Definition tab in the Properties dialog box, and then select Edit Query.

Tip If you are in a worksheet with a query, select Data > Properties, select the Definition tab in the Properties dialog box, and then select Edit Query.

A Data Model typically contains several tables arranged in a relationship. You load a query to a Data Model by using the Load To command to display the Import Data dialog box, and then selecting the Add this data to the Data Model check box. For more information about Data Models, see Find out which data sources are used in a workbook data model, Create a Data Model in Excel, and Use multiple tables to create a PivotTable.

To open the Data Model, select Power Pivot > Manage.

At the bottom of the Power Pivot window, select the worksheet tab of the table you want.

Confirm that the correct table displays. A Data Model can have many tables.

Note the name of the table.

To close the Power Pivot window, select File > Close. It may take a few seconds to reclaim memory.

Select Data > Connections & Properties > Queries tab, right click the query, and then select Edit.

When finished making changes in the Power Query Editor, select File > Close & Load.

The query in the worksheet and the table in the Data Model are updated.

Loading a query to a Data Model takes unusually long

If you notice that loading a query to a Data Model takes much longer than loading to a worksheet, check your Power Query steps to see if you are filtering a text column or a List structured column by using a Contains operator. This action causes Excel to enumerate again through the entire data set for each row. Furthermore, Excel can’t effectively use multithreaded execution. As a workaround, try using a different operator such as Equals or Begins With.

Microsoft is aware of this problem and it is under investigation.

You can load a Power Query:

To a worksheet. In the Power Query Editor, select Home > Close & Load > Close & Load.

To a Data Model. In the Power Query Editor, select Home > Close & Load > Close & Load To.

By default, Power Query loads queries to a new worksheet when loading a single query, and loads multiple queries at the same time to the Data Model. You can change the default behavior for all your workbooks or just the current workbook. When setting these options, Power Query doesn't change query results in the worksheet or the Data Model data and annotations.

You can also dynamically override the default settings for a query by using the Import dialog box which displays after you select Close & Load To.

Global settings that apply to all your workbooks

In the Power Query Editor, select File > Options and settings > Query Options.

In the Query Options dialog box, on the left side, under the GLOBAL section, select Data Load.

Under the Default Query Load Settings section, do the following:

Select Use standard load settings.

Select Specify custom default load settings, and then select or clear Load to worksheet or Load to Data Model.

Tip At the bottom of the dialog box, you can select Restore Defaults to conveniently return to the default settings.

Workbook settings that only apply to the current workbook

In the Query Options dialog box, on the left side, under the CURRENT WORKBOOK section, select Data Load.

Do one or more of the following:

Under Type Detection, select or clear Detect column types and headers for unstructured sources.

The default behavior is to detect them. Clear this option if you prefer to shape the data yourself.

Under Relationships, select or clear Create relationships between tables when adding to the Data Model for the first time.

Before loading to the Data Model, the default behavior is to find existing relationships between tables, such as foreign keys in a relational database and import them with the data. Clear this option if you prefer to do this on your own.

Under Relationships, select or clear Update relationships when refreshing queries loaded to the Data Model.

The default behavior is to not update relationships. When refreshing queries already loaded to the Data Model, Power Query finds existing relationships between tables such as foreign keys in a relational database and updates them. This might remove relationships created manually after the data was imported or introduce new relationships. However, if you want to do this, select the option.

Under Background Data, select or clear Allow data previews to download in the background.

The default behavior is to download data previews in the background. Clear this option if you can want to see all the data right away.

You can manage your queries either in the Queries pane of the Power Query Editor or on the Queries tab of the Queries & Connections pane in Excel. This is especially helpful if you have many queries and you want to quickly locate a query. Once you find a query, there are a number of additional actions you can do, such as duplicate, reference, merge, append, share, and delete a query.

When you create a query, connection information is created behind the scenes. The role of the query is to shape the data. The role of the connection information is to maintain data source information and to refresh the data according to refresh settings, such as the frequency.

Not all queries may be loaded to a worksheet. This can occur when you import data from an external data source, shape the data in Power Query Editor, select Home > Load To, and then use the Import Data dialog box to put the query either in a Data Model or an Office Data Connection (ODC) file.

The following is a summary of the commands you can use listed in the order they are displayed on their respective context menus of the Queries tab of the Queries & Connection pane in Excel or the Queries pane in the Power Query Editor. Some commands are only available on one pane. Many of these commands are also available on the context Query tab in the ribbon. All queries in the workbook are displayed and sorted by the date and time when they were last modified starting with the most recent.

Do one of the following:

In Excel Select Data > Queries & Connections > Queries tab.

In the Power Query Editor Select Data > Get Data > Launch Power Query Editor, and view the Queries pane on the left.

Select a query management command:

Edit Edits the query in the Power Query Editor. Only available on the Queries tab of the Queries & Connections pane.

Delete Removes a query.

Rename Changes the query name.

Refresh Brings in up-to-date data from external data sources. Only available on the Queries tab of the Queries & Connections pane. For more information, see Refresh an external data connection in Excel.

Load To Displays the Import Data dialog box so you can select how you want to view the data, where you want to put it, and add it to a Data Model. Only available on the Queries tab of the Queries & Connections pane.

Duplicate Creates a copy of the selected query with the same name as the original query appended by (2). You can rename the query to identify it more easily. Subsequent changes to the original query won’t affect the new query.

Reference Creates a new query that uses the steps of a previous query without having to duplicate the query. The new query follows a similar naming convention as the Duplicate command. You can rename the new query to identify it more easily. Subsequent changes to the original query will affect the new query if the output is changed.

Merge Merge columns in a query with matching columns in other queries. For more information about merging see Merge queries. Only available on the Queries tab of the Queries & Connections pane.

Append Append columns in a query with matching columns in other queries. For more information about appending see Append queries. Only available on the Queries tab of the Queries & Connections pane.

Move To Group Moves the query to a group in the list or if there are no groups, select New Group to create one. Give it a meaningful name, such as Budget or Marketing. Use groups to organize many queries into categories. You can even create groups within groups.

Move Up Moves the query up in the list of queries.

Move Down Moves the query down in the list of queries.

Create Function displays the Create Function dialog box. Only available on the Queries pane in the Power Query Editor. For more information, see Power Query M function reference.

Convert to Parameter Converts the query to a parameter and displays it in Preview Data. If the current display is a parameter, then the command toggles to Convert To Query. Only available on the Queries pane in the Power Query Editor. For more information, see Create a parameter query (Power Query).

Advanced Editor Opens the Advanced Editor window. For more information, see Create Power Query formulas in Excel. Only available on the Queries pane in the Power Query Editor.

Export Connection File Saves the query as an ODC connection file. The ODC file contains the query definition, the data source connection information, and all the transformation steps. This is useful when you want to share queries with other users and workbooks. Alternatively, you can use the Properties command on the Query context tab of the Power Query ribbon. For more information, see Create, edit, and manage connections to external data. Only available on the Queries tab of the Queries & Connections pane.

Show the Peek Displays the query flyout with a data preview and other useful information. Only available on the Queries tab of the Queries & Connections pane.

Properties Use this command to manage a query and its data source connection information. On the Queries tab of the Queries & Connections pane, opens the Query Properties dialog box. On the Queries pane in the Power Query Editor, opens the Query Settings dialog box.

You can enable or disable the display of the query flyout when you hover over a query name on the Query tab in the Queries & Connections pane. This option only controls the display of the flyout by hovering. You can still display the query flyout by right clicking on the query name, and then selecting Show the Peek.

Select Data > Get Data > Query Options.

On the left under GLOBAL, select General.

On the right, under Query Preview clear the check box next to Show the peek on hover. The default action is to display the flyout when you hover.

Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: MS SQL Server Management Studio, Aquafold Aqua Data Studio, DBeaver и т.п., а вероятность их установки в краткосрочной перспективе близка к нолю. В то же время, присутствует острая необходимость подключения к этой самой БД и работы с данными. Как оказалось, на помощь может прийти старый добрый MS Excel.

В моем случае требовалось подключиться к MS SQL Server, однако, MS Excel умеет устанавливать соединение не только с ним, но и с большинством современных БД: MySQL, PostgreeSQL, IBM DB2 и даже Oracle и Teradata, а также с файлами данных CSV, XML, JSON, XLS(X), MDB и другими.

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

В новой книге на ленте выбираем «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Из других источников» -> «(4) С сервера SQL Server».

Далее, в окне Мастера подключения к данным, заполняем «(1) Имя сервера» -> «(2) Учетные сведения»[ -> «(3) Имя пользователя» и «Пароль»]. Таким образом, мы сообщаем MS Excel, с каким сервером мы хотим установить соединение и какой метод аутентификации хотим использовать. Я использовал «проверку подлинности Windows», но возможно также указать учетные данные отличные от установленных в Windows.

Выбираем целевую «(1) Базу данных» -> «(2)(3) Определенную таблицу» или «Несколько таблиц» или же базу в целом (тогда оба «чекбокса» оставляем пустыми).

После всех проделанных манипуляций, Мастер подключения предложит сохранить файл подключения. Потребуется задать «(1) Имя файла». Желательно также указать «(2) Описание» и «(3) Понятное имя файла», чтобы спустя время было понятно какой файл подключения к какой базе или таблице обращается.

Теперь выбрать созданное подключение можно будет следующим образом: «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Существующие подключения».

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

Определив таблицы, MS Excel предложит выбрать «(1) Способ представления данных» и «(2) Куда следует поместить данные». Для простоты я выбрал табличное представление и размещение на уже имеющемся листе, чтобы не плодить новые. Далее следует нажать на «(3) Свойства».

В свойствах подключения, нужно перейти на вкладку «(1) Определение». Здесь можно выбрать «(2) Тип команды». Даже если требуется выгружать лишь одну таблицу без каких-либо связей, настоятельно рекомендую выбрать SQL команду, чтобы иметь возможность ограничить размер выгружаемой таблицы (например, с помощью TOP(n)). Так, если вы попытаетесь выгрузить целиком таблицу базы, это может привести в лучшем случае к замедлению работы MS Excel, а в худшем к падению программы, к тому же – это необоснованная нагрузка на сам сервер базы данных и на сеть. После того как «(3) Текст команды» будет введен и нажата кнопка «ОК», MS Excel предложит сохранить изменения запроса – отвечаем положительно.

Читайте также:

Queries and connections excel что это

See Also