3.3. Excel#

3.3.1. Introduction#

Microsoft Excel is a powerful spreadsheet application used for various data-related tasks. With the help of our ODBC driver, you can enhance Excel’s capabilities, enabling seamless integration with external data sources.

3.3.2. Why ODBC is Useful with Excel#

ODBC acts as an intermediary, allowing Excel to communicate with databases, systems, and other sources by translating Excel’s requests into commands that these sources understand.

In this tutorial, we will walk you through the process of integrating Excel with ODBC. However, it’s important to note that the driver’s installation isn’t covered here; please refer to the official ODBC documentation for detailed instructions on downloading, installing, and configuring the driver.

3.3.3. Essential Steps for Excel Integration#

Before you can proceed with the Excel integration examples, you need to complete two essential prerequisites:

  1. ODBC Driver Installation: Ensure that the ODBC driver is correctly installed. The ODBC driver is Excel’s connector to your external data source. Detailed instructions for the driver’s installation can be found in the official ODBC documentation.

  2. Creating a Datasource: You must configure a datasource, providing crucial details such as the server’s address, authentication credentials, and other connection parameters. This configuration is vital for Excel to know where and how to retrieve your data.

Without a properly configured datasource, Excel won’t be able to access the external data. These prerequisites are crucial to ensure a successful integration of Excel and ODBC.

3.3.4. Complete List of Parameters#

For a comprehensive list of parameters for the ODBC driver, please refer to Driver parameters section in the official ODBC guideline.

You can set these parameters in the DSN or in the connection string you use.

Any parameters not explicitly mentioned will be filled with default values.

3.3.5. First connection#

For the rest of this tutorial, the screenshot come from Excel 2016, differences could come up if you use a different version.

The ODBC driver use a connection string filled in the Excel prompt, but first we need to get to this prompt.

To begin, open Excel and navigate to the Data tab. You can find it at the top of your Excel window:

../../_images/excel_data_tab.png

Then Get Data -> From Other Sources -> From ODBC:

../../_images/excel_go_to_odbc.png

This action will open a new window. Start by opening the dropdown menu, and select None.

../../_images/excel_initial_odbc_window.png ../../_images/excel_select_dsn_none.png

Select Advanced options:

../../_images/excel_select_advanced_option.png

The first field in the advanced options is the connection string.

The connection string is a semi-colon separated list of arguments.

3.3.5.1. Examples#

  • Driver={qdb_odbc_driver}

  • Driver={qdb_odbc_driver};LogDir=C:\Program Files\quasardb\log\

  • Driver={qdb_odbc_driver};URI=qdb://127.0.0.1:2836

  • Driver={qdb_odbc_driver};UID=tintin;PWD=tintin_key;KEY=cluster_public_key

3.3.5.2. Detail#

Whatever options you need afterward, you need to register which driver you wish to use.

In our case the connection string begins with: Driver={qdb_odbc_driver}

Then you can add the following arguments:

  • LogDir: the path to the folder in which you wish our log file to be stored.

  • URI: the cluster URI you wish to connect to.

And these security parameters:

  • UID: the user name you log in with.

  • PWD: the password associated with the user.

  • KEY: the cluster public key file content.

You can find your UID and PWD in your user private credentials file.

Enter your connection string, and optionnally a query in the next field as shown in the Querying data step of this tutorial.

../../_images/excel_connection_string.png

If you see a warning sign on the right of the field (as it is on the previous picture) it means the driver was not properly installed, or that the driver name is not the correct one.

You can check this information in the ODBC data source administrator in windows under the Drivers tab:

../../_images/excel_check_existing_drivers.png

Connecting to a secured cluster:

In the second screen, if you are connecting to a secured cluster you can fill your UID, PWD, and KEY parameters.

../../_images/excel_connection_secure.png

If you are not connecting to a secured cluster, don’t fill anything and just click on connect.

3.3.6. Querying data#

As an optional field in the advanced options you can set an SQL statement, use our query language directly in this field.

For example, if you have a timeseries named ts you could select everything with this query:

../../_images/excel_query_directly.png

Then click ok, the following window shall appear (possibly after a second window containing login information):

../../_images/excel_query_preview.png

Click on Load

../../_images/excel_data_loaded.png

3.3.7. Exploring your tables#

Click ok from where we left out in the connection step, you will be prompted with the Navigator windows.

It will list all the timeseries in your database, which could be a lot, but you can filter them with the search field at the top left of the window.

Click on it on one table to show a preview of the timeseries:

../../_images/excel_navigator.png

Load and enjoy your freshly imported data.

../../_images/excel_data_loaded.png

3.3.8. Editing your dataset#

You can select which columns should show up by right-clicking on the ts query in Queries & Connections on the right of your screen:

../../_images/excel_edit_query.png

You can then select one or more column(s) with left clicks, and then click on Remove Columns icon:

../../_images/excel_remove_column.png

Click Close & Load on the top left of this window.

Your data shall only contain the columns you did not remove, and this will end this tutorial, congratulations!