An ODBC (Open Database Connectivity) Connection exposes the LabKey schema and queries as a data source to external clients for analysis and reporting. This enables users to continue outside tools they may already be using with data stored in LabKey.
At this time, only commercial ODBC drivers can be used for using Excel from OSX. Learn more in this support topic on the Microsoft support site:
An administrator must already have enabled ODBC connections on LabKey Server following this topic:
View ODBC Access Settings
To confirm that configuration has been completed, and obtain the relevant settings to use when setting up your driver
, users can select (username) > External Tool Access
If you will be using a secure ODBC connection
, you will need to download and install the SSL certificate from this page and install it on your local machine.
Obtain and Install an ODBC Driver
While the free PostgreSQL ODBC driver can successfully be used from Windows, you must currently use a commercial ODBC driver on a Mac.
Obtain and install the driver of your choice.
Create a Data Source Name (DSN)
Follow the instructions provided with the driver you choose to configure and define the connection. You will define a Data Source Name (DSN), typically providing information like the server, port, database name, user/password, etc. The method may vary with different commercial drivers.
For example, depending on the driver, you might see and test settings from a Connection
tab as shown here:
Access Data from Excel
To access your LabKey data from Excel:
- Click Data.
- Select New Database Query > From Database.
- In the popup, click the System DSN tab and select the driver you configured.
- Click OK. You may be prompted to enter your username and password.
You can now browse and query the LabKey data from within Excel.