Using unixODBC
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-devOn 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-develIf 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 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 -jBelow 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.: 8Note: 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-excel/lib/cdata.odbc.excel.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-excel/lib. A Description property can also be provided.
[CData ODBC Driver for Excel]
Driver=/opt/cdata/cdata-odbc-driver-for-excel/lib/libexcelodbc.x64.so
Description=CData ODBC Driver for Microsoft Excel
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 Excel Source]
Driver=/opt/cdata/cdata-odbc-driver-for-excel/lib/libexcelodbc.x64.so
URI=C:\MyExcelWorkbooks\SampleWorkbook.xlsx
Test the Connection
You can use the unixODBC test tool, isql, to execute SQL queries to Microsoft Excel 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 Excel Source"