Запрос odbc в excel
Для начала нужно установить драйвер ODBC для Firebird или InterBase (разумеется, перед этим нужно убедиться, что на компьютер уже установлена клиентская часть InterBase или Firebird).
- Открыть Панель управления
- Администрирование, Источники данных (Data Sources (ODBC)).
%systemdrive%\Windows\SysWoW64\odbcad32.exe
по умолчанию запускается конфигурирование 64-разрядных драйверов
%systemdrive%\Windows\System32\odbcad32.exe
- ODBC-драйверы InterBase и Firebird не работают напрямую с сервером. Для их работы нужна клиентская часть (gds32.dll, ibclient64.dll, fbclient.dll), причем той же разрядности, что и приложение и ODBC драйвер.
Например, если приложение 32-разрядное, значит нужен 32-разрядный ODBC и 32-разрядный клиент. Разрядность InterBase или Firebird при этом не имеет значения, он может быть как 32битным, так и 64битным. - Переключиться на закладку System DSN
- Нажать кнопку Add. Выбрать драйвер.
- Настроить параметры драйвера
Пример для InterBase DataDirect ODBC
Разумеется, если Firebird или InterBase (как сервер) установлены у вас на компьютере, вы можете указывать параметр "База данных" как c:\dir\data.gdb или localhost:c:\dir\data.gdb
Указание localhost: избавит вас от проблем, если вы запутались с клиентскими библиотеками Firebird и InterBase (и получили ошибку unavailable database).
Если на локальной машине Firebird или InterBase нет, то вместо localhost вы указываете имя сервера.
Нажмите на кнопку "Проверка соединения". Если все указано правильно, драйвер сообщит, что соединение прошло успешно. Нажмите Ok. В списке System DSN появится алиас EMPLOYEE.
Помните, что в цепочке приложение-драйвер-клиент все должно быть одной разрядности. Например, 32-разрядный Excel не будет работать с 64разрядным ODBC, а 32разрядный ODBC не будет работать с 64-разрядным клиентом Firebird или InterBase.
Запустите Excel. Выберите меню Данные, Импорт внешних данных, Создать запрос.
Выберите алиас EMPLOYEE.
Теперь можно выбрать таблицы и их столбцы, входящие в запрос.
Можно особо не мучиться, выбрать один столбец любой таблицы и пару раз нажать Next. На третий раз появится диалог:
Легче построить запрос в Microsoft Query, чем заполнять столбцы в самом первом диалоге Мастера запросов.
Здесь уже намного легче выбрать столбцы, добавить условия, добавить таблицы, определить между ними связи, или вообще ввести готовый оператор SQL вручную.
После проверки запроса необходимо обязательно сохранить запрос (кнопка с изображением дискеты). Запрос будет сохранен вместе со всеми параметрами алиаса, поэтому для "распространения" запроса на несколько компьютеров вовсе необязательно создавать алиас ODBC на каждом (конечно, ODBC-драйвер и клиентскую часть IB/FB все равно придется устанавливать на этих компьютерах).
По умолчанию Microsoft Query сохраняет запросы в каталог C:\Documents and Settings\Administrator\Application Data\Microsoft\Запросы в виде текстового файла с расширением qry (вместо Administrator в пути может быть имя пользователя данного компьютера).
После формирования запроса (и его сохранения) нужно нажать на кнопку с изображением двери
И после нажатия OK мы получим данные в таблице Excel:
Надо заметить, что сделать вид данных ближе к пользователю можно (похоже что) только в третьем диалекте, например, заменив имя столбцов Last_Name и First_Name на имена в двойных кавычках – "Фамилия" и "Имя".
Некоторые сведения относятся к предварительной версии продукта, в которую до выпуска могут быть внесены существенные изменения. Майкрософт не предоставляет никаких гарантий, явных или подразумеваемых, относительно приведенных здесь сведений.
Представляет подключение ODBC.
Комментарии
Подключение ODBC можно хранить в Excel книге. Когда micrososft Excel открывает книгу, Excel создает в памяти копию подключения ODBC, известного как ODBCConnection объект.
Объект содержит сведения, связанные с подключением, например имя сервера для подключения к и имя объектов, которые будут открыты ODBCConnection на этом сервере. Кроме того, объект может также включать сведения о учетных данных проверки подлинности или команду, которая должна быть передана серверу и выполнена (например, заявление SELECT, которое будет выполнено ODBCConnection SQL Server).
Свойства
Верно, если файл подключения всегда используется для установления подключения к источнику данных. Для чтения и записи, Boolean.
При применении без квалификатора объекта это свойство возвращает объект, представляю Microsoft Office Excel _Application приложение. При его применении с квалификатором объекта это свойство возвращает объект Application, который представляет создателя указанного объекта. Только для чтения.
True, если запросы для подключения ODBC выполняются асинхронно (в фоновом режиме). Для чтения и записи, Boolean.
Возвращает или задает строку команды для указанного источника данных. Объект Read/write.
Возвращает или задает одну из XlCmdType констант. Чтение и написание XlCmdType .
Возвращает или задает строку, содержаную параметры ODBC, Microsoft Excel подключиться к источнику данных ODBC. Объект Read/write.
Возвращает константу в XlCreator переумериях, которая указывает приложение, в котором был создан этот объект. Только для чтения.
True, если подключение может быть обновлено пользователем. Значение по умолчанию — True. Для чтения и записи, Boolean.
Возвращает родительский объект для указанного объекта. Только для чтения.
Возвращает дату последнего обновления подключения ODBC. Только для чтения DateTime.
True, если фоновый запрос ODBC находится в процессе для указанного подключения ODBC. Для чтения и записи, Boolean.
True, если подключение автоматически обновляется каждый раз, когда книга открывается. Значение по умолчанию — False. Для чтения и записи, Boolean.
Возвращает или задает количество минут между обновлениями. Для чтения и записи, Integer.
Возвращает или задает подключение ODBC к источнику данных. Чтение и написание XlRobustConnect .
True, если сведения о паролях в строке подключения ODBC сохраняются в строке подключения. False, если пароль удален. Для чтения и записи, Boolean.
Возвращает или задает тип учетных данных, которые необходимо использовать для проверки подлинности сервера. Чтение и написание XlCredentialsMethod .
Возвращает или задает один идентификатор приложения для входа (SSO), который используется для поиска в базе данных SSO для учетных данных. Для чтения и записи, String.
Возвращает или задает строку, указывающее Microsoft Office или аналогичный файл, который использовался для создания подключения. Для чтения и записи.
Возвращает источник данных для подключения ODBC, как показано в таблице. Объект Read/write.
Возвращает или задает строку, указывающее исходный файл данных для подключения ODBC. Для чтения и записи.
Методы
Отменяет все операции обновления для указанного подключения ODBC.
Обновляет подключение ODBC.
Сохраняет подключение ODBC в качестве файла Microsoft Office подключения к данным.
Решение Майкрософт для работы с большими данными включает в себя компоненты бизнес-аналитики (БА) Майкрософт с кластерами Apache Hadoop, развернутыми в HDInsight. Примером может служить возможность подключения Excel к хранилищу данных Hive кластера Hadoop. Подключение с помощью драйвера Microsoft Hive Open Database Connectivity (ODBC).
Вы можете подключить данные, связанные с кластером HDInsight, из Excel с помощью надстройки Microsoft Power Query для Excel. Дополнительные сведения см. в статье Подключение Excel к HDInsight с помощью Power Query.
Предварительные требования
Перед началом работы с этой статьей необходимо иметь следующее:
- Кластер HDInsight Hadoop. Дополнительные сведения о создании кластера см. в статье Приступая к работе с Hadoop в HDInsight.
- Рабочая станция с Office 2010 Professional Plus или более поздней версии или Excel 2010 или более поздней версии.
Установка драйвера Microsoft Hive ODBC
Скачайте и установите драйвер Microsoft Hive ODBC. Выберите версию, которая соответствует версии приложения, где будет использоваться драйвер ODBC. В рамках данной статьи используется драйвер для Office Excel.
Создание источника данных Apache Hive ODBC
Ниже показано, как создать источник данных Hive ODBC.
В Windows откройте Пуск > Средства администрирования Windows > Источники данных ODBC (32-разрядная или 64-разрядная версия) . В результате откроется окно Администратор источников данных ODBC.
На вкладке DSN пользователя выберите Добавить, чтобы открыть окно Создание нового источника данных.
Выберите Microsoft Hive ODBC Driver, а затем — Готово, чтобы открыть окно Microsoft Hive ODBC Driver DSN Setup (Настройка DSN Microsoft Hive ODBC Driver).
Введите или выберите следующие значения:
Необязательно: выберите Дополнительные параметры.
| Параметр | Описание |
|---|---|
| Использовать исходный запрос | При выборе этого параметра драйвер ODBC НЕ пытается преобразовать TSQL в HiveQL. Следует использовать только при полной уверенности в отправке действительных инструкций HiveQL. При подключении к серверу SQL Server или базе данных Azure SQL необходимо снять этот флажок. |
| Строки, загружаемые для каждого блока | При получении большого объема записей включение этого параметра может обеспечить оптимальную производительность. |
| Длина столбца строки по умолчанию, длина столбца двоичного кода, масштаб столбца десятичных значений | Длина и точность типа данных может повлиять на способ выведения данных. Это приведет к возврату недопустимой информации из-за потери точности и/или усечения. |
Щелкните Тест для проверки источника данных. При правильной настройке источника результатом теста будет слово УСПЕШНО! .
Импорт данных в Excel из службы HDInsight
Ниже описан способ импорта данных из таблицы Hive в рабочую книгу Excel с помощью источника данных ODBC, созданного в предыдущем разделе.
Откройте новую или существующую рабочую книгу в Excel.
На вкладке Данные перейдите к разделу Получить данные > Из других источников > Из ODBC, чтобы открыть окно Из ODBC.
Из раскрывающегося списка выберите имя источника данных, который вы создали в предыдущем разделе, и щелкните ОК.
При первом использовании откроется диалоговое окно Драйвер ODBC. В меню слева выберите пункт Windows. Затем нажмите кнопку Подключиться, чтобы открыть окно Навигатор.
В окне Навигатор перейдите к HIVE > по умолчанию > hivesampletable, а затем нажмите кнопку Загрузить. Для импорта данных в Excel потребуется несколько секунд.
Дальнейшие действия
В рамках этой статьи вы узнали, как получить данные из службы HDInsight в Excel с помощью драйвера Microsoft Hive ODBC. Аналогичным образом можно получать данные из службы HDInsight в базу данных SQL. Можно также передавать данные в службу HDInsight. Дополнительные сведения см. на следующих ресурсах:
Select the data source to connect to Db2 on IBM i and expand the 'Advanced options' twistie and type in your select statement in the 'SQL statement (optional)' section as shown in Fig. 1b.
Click OK and it makes an ODBC connection, run the query and display the results in a table as shown in Fig. 1c
Press 'Load' and it loads the data into the spreadsheet and saves the query in it as shown in Fig. 1d
Note the 'Queries & Connections' item to the right of the data. If you hover your mouse over the spreadsheet icon just to the left of the query name (it was given a default name of Query1), you get a pop-up window as shown in Fig. 1e. The options in this window provide functions to edit the query and do several other things that are beyond the scope of this technote. Note, there is also a refresh icon to the right of the query name and clicking it refreshes the data in spreadsheet. If you close the 'Queries & Connections' pane, you can reopen it using the 'Queries & Connections' item on the Data ribbon.
Selecting the EDIT option opens the dialog box shown in Fig. 1f. With this dialog you can modify the query, change the query name, and use the 'Advanced Editor' function (near the upper left) to edit the SQL statement.
Connect Without A DSN
In the previous example, a DSN was selected to define the connection. A DSN or data source Name can be thought of as a saved collection of default values for an ODBC connection to use. Ultimately, the application might override most any of the values you specify in a DSN so they aren't strict controls on the settings. While DSNs are a great way to get consistent behavior from one or more applications, they do require the step of creating the DSN. You can freely share a spreadsheet with an embedded data connection defined in it (like our first example). But the recipient must also have that DSN defined with the same name (and connecting to the same system). So you can see how a DSN is also a hindrance to the portability of the solution. Fortunately, we can define the connection without a data source. Then, we can share the spreadsheet with anyone with the ODBC driver installed on their system. They can refresh that data as needed.
We follow the same steps of selecting the option to get data from ODBC. But this time, select '(None)' for the data source and enter a connection string that defines the ODBC driver to use and the system to connect to at a minimum. The ODBC connection string keywords and their default values are defined in the IBM Documentation. Using this interface, you do not need to provide values for anything more that the Driver and System keywords because we are not using any catalog features to browse for files or columns. If you have data in character fields defined with CCSID 65535, add the Translate keyword value pair. This value causes that data to be converted from EBCDIC by using the job CCSID so that's in the example in Fig. 2a.
You might be prompted for an ID and password after you press OK and then you are presented with the query results as in Fig. 1c from the first example. Select the option to load the data in the spreadsheet
Parameterized Query
Let's move on to something a little more dynamic. Frequently an entire table is transferred to a spreadsheet and then several rows are removed because they weren't wanted in the first place. In other words, a selection criteria set on the query ensures that only certain rows are selected and returned. The value for that criteria can be specified dynamically to provide a user with the flexibility to define the limiting values at run time. This next example shows how to create a parameterized query.
For this case, the accounting team is sending refund checks to customers with large credit balances. I need to find data for every customer that has a credit due to them that is greater than the limit that the accounting team set. Presently the limit is $10 so I really don't want to see the accounts that do not have more than a $10 credit due. I might change the query and add a hardcoded selection criteria, 'Where CDTDUE >= 10'. But the amount is known to vary from month to month and at the end of the fiscal year, anyone with a credit due is paid back. I don't want to edit the query every time it changes. I'd want the query to prompt me for a value when it is refreshed. To do accomplish this goal, we have to create the query a bit differently, by using Microsoft Query.
To start, open a new spreadsheet and select the Data ribbon, select 'Get Data', 'From Other Sources', 'From Microsoft Query'. That opens the dialog box shown in Fig. 3a. Select your DSN and clear the box for 'Use the Query Wizard to create and edit queries'.
In this case, it helps to have the library with the table you are querying in the library list of the DSN. That way you can select the schema (library) that the table is in, select the table and click 'Add' then 'Close' in the dialog box shown in Fig. 3b.
Double-click the asterisk to populate the query with all of the columns, or select the columns you want in the order you want them. See Fig. 3c.
Click 'Add Criteria. ' option on the 'Criteria' menu. Add the criteria for your query. In my case, I want to be prompted for a value that the CDTDUE column must be greater than or equal to. So I select the CDTDUE field, the proper comparison operator from the list, and an open and closed bracket for the value as shown in Fig. 3d.Click add and it prompts for a value, type one in and then press 'Close' and then select 'Return Data to Microsoft Excel' from the 'File' menu. I am presented with the 'Import Data' window shown in Fig. 3e.
Press 'Properties' to decide how you want the parameter to be set. The 'Connection Properties' window opens. Check the ' Refresh data when opening the file ' option on the 'Usage' tab (Fig 3f). This setting causes a prompt for a new value every time the spreadsheet is opened. Then, select the 'Definition' tab, press 'Parameters' to open the 'Parameters' window shown in Fig. 3g.
Note, by default, it selected the value you entered when Microsoft Query prompted you for a value to initially run the query. I want the query to prompt the user for a value every time. So I select the first option for how the parameter is to be obtained, ' Prompt for value using the following string:' . I enter a meaningful prompt string that the user is presented with. Finally, click 'OK' on this window and the preceding two windows to embed your query into Excel. Excel prompts you for a value and then it populates your spreadsheet with the query data (See Fig. 3h).Another Way To Enter Parameters - In The Spreadsheet Itself
OK that works great but there are a couple of potential problems. When that spreadsheet opens, it prompts for the minimum credit due value but it doesn't suggest a default. If someone new is using this spreadsheet, it would be nice to see what the last value used was. The other issue is that the minimum credit values required for a refund check change only at the end of my fiscal year and even then, only for one month. How can I keep the flexibility of the parameterized query but not have to enter a value every time? One way is to pull the value from a cell on the spreadsheet itself and refresh the data only when that that value is changed or when I click 'Refresh All' on the Data ribbon.
To start, open a new spreadsheet and create an input cell and a description of what is expected in that cell. Excel also has some default format colors you can apply to the cells to alert the user that a cell is an input value. See Fig. 4a for details.
Proceed as in the previous example and this time when you return the data to Excel, the target location is under the input area (see Fig. 4b) and click 'Properties' again.
Click the 'Definition' tab in the 'Connection Properties' dialog box and click the radio button titled 'Get the value from the following cell:'. Click the arrow button under that text (right side of the input field) then click your input field in the spreadsheet and Excel automatically adds the proper syntax to refer to the cell. In my example that would be =Sheet1!$B$1 meaning the value from Sheet1 of the workbook, Cell B1 (see Fig. 4c)
This solution allows me to easily view what the parameter value is and I can update it and rerun the query by typing the new value into the input cell and pressing enter. At the end of the year, I put in the value 0.01 and press enter to get rows for everyone that has a credit due. (See Fig. 4e).
Читайте также: