In the following sections, you can find how-tos on installing the unixODBC driver manager, setting up the driver for unixODBC, and creating DSNs.

Installing unixODBC

Precompiled binaries for the unixODBC driver manager are available for many operating systems.

On Debian-based systems like Ubuntu, you can install unixODBC by running the following command as root or with sudo:

apt-get install unixodbc unixodbc-dev

On Red Hat Enterprise Linux, CentOS, and Fedora, you can install unixODBC with YUM or DNF. For example, run the following command as root or with sudo:

yum install unixODBC unixODBC-devel

If binaries are not available for your operating system, you will need to compile unixODBC yourself. Please refer to the unixODBC website for more information about obtaining binaries or compiling unixODBC on your operating system.

Using unixODBC Configuration Files

If your unixODBC installation does not include a graphical tool, you can set up connectivity to Microsoft Excel Online by editing the configuration files.

You can determine the location of the configuration files on your system by entering the following command into a terminal:

odbcinst -j

Below is an example of the output of this command:

DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/myuser/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Note: Modifying system-wide settings requires elevated permissions; to do so, you can use root or the sudo command.

Set the Driver Encoding

unixODBC communicates with applications using UTF-16 encoding. This is the default encoding in CData ODBC drivers, but this can be set to alternative options if necessary, in the config file '/opt/cdata/cdata-odbc-driver-for-excelonline/lib/cdata.odbc.excelonline.ini'.

Register the Driver

Register the driver by adding a section to the odbcinst.ini file. You may need to modify the installed driver definition if you change the path to the driver library.

The sections of the odbcinst.ini file map a driver name to the driver library. The section begins with the driver name; the Driver property points to the path to the driver library. The driver library is the .so file located by default in /opt/cdata/cdata-odbc-driver-for-excelonline/lib. A Description property can also be provided.

[CData ODBC Driver for ExcelOnline]
Driver=/opt/cdata/cdata-odbc-driver-for-excelonline/lib/libexcelonlineodbc.x64.so
Description=CData ODBC Driver for Microsoft Excel Online

To check that the driver is registered, list the drivers installed on the system with the following command:

odbcinst -q -d

Define a DSN

Create a DSN by adding an entry to odbc.ini. Below is an example DSN entry in odbc.ini:

[CData ExcelOnline Source]
Driver=/opt/cdata/cdata-odbc-driver-for-excelonline/lib/libexcelonlineodbc.x64.so

Test the Connection

You can use the unixODBC test tool, isql, to execute SQL queries to Microsoft Excel Online from the command line. When testing the connection, use the -v flag to output any messages from the driver manager and the driver.

isql -v "CData ExcelOnline Source"