2.4. Excel¶
2.4.1. Introduction¶
The Excel integration works with our ODBC driver.
We will guide you through the process of:
installing the driver
connecting to it in Excel
querying directly to import data
exploring your tables
editing a chosen dataset
2.4.2. Installation¶
You can install the driver with our specific ODBC driver setup: qdb-odbc-driver-[version]-windows-64-bit-setup or its 32bit counter part.
You can choose the folder where you want it installed and that’s it!
Caution: Using the uninstaller will automatically remove the driver.
2.4.3. 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 that you will need to enter in the Excel prompt, but first we need to get to this prompt.
First you should go to the Data tab:
You can then proceed to successively go to Get Data -> From Other Sources -> From ODBC:
Clicking this should produce the following window after some waiting time:
First open the dropdown menu and click on None:
To finally get to the connection string prompt, click on Advanced options:
The first field in the advanced options is what we are looking for, ie: the connection string.
The connection string is a semi-colon separated list of arguments.
There are several options and one mandatory parameter.
Whatever options you need afterward, you first need to register which driver you wish to use.
In our case the connection string will begin 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.
Now you can enter your connection string, and optionnally a query in the next field as shown in the Querying data step of this tutorial.
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:
Connecting to a secured cluster:
Then you are taken to a second screen, if you are connecting to a secured cluster this is where you fill your UID, PWD, and KEY parameters.
If you are not connecting to a secured cluster, don’t fill anything and just click on connect.
2.4.4. Querying data¶
As an optional field in the advanced options you can set an SQL statement, you can use our query language directly in this field.
For example, if you have a timeseries named ts you could select everything with this query:
Then click ok, the following window shall appear (possibly after a second window containing login information):
Click on Load and tada.
2.4.5. Exploring your tables¶
To do that just click ok from where we left out in the connection step, you will be prompted with the Navigator windows.
It will least 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:
Load and enjoy your freshly imported data.
2.4.6. Editing your dataset¶
Now say you don’t want every column from showing up. To do that you can right-click on the ts query in Queries & Connections on the right of your screen:
You can then select one or more column(s) with left clicks, and then click on Remove Columns icon:
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!