Using the iODBC Driver Manager

Connecting with iODBC

On macOS, the CData ODBC Driver for Google Sheets is preconfigured for use with the iODBC driver manager, as are many other products like Filemaker Pro, Microsoft Excel, and Tableau. You can find the latest version of iODBC on the iODBC site.

After manually registering the driver in the odbcinst.ini and odbc.ini files, you can create a system DSN with iODBC. You can use this DSN in any tools or applications that support ODBC connectivity.

Register the Driver

Before the DSN associated with the driver can be discovered with iODBC, it must be registered in odbcinst.ini and odbc.ini.

odbcinst.ini

To register an ODBC driver, modify the odbcinst.ini file. In iODBC, drivers can be available to either a single user account or all users.


Privileges	Path
User	/Users/myuser/Library/ODBC/odbcinst.ini
System	/Library/ODBC/odbcinst.ini

Modifying iODBC's system-wide settings requires elevated permissions; to do so, you can use the sudo command to open a text editor from the terminal. For example:

sudo nano /Library/ODBC/odbcinst.ini

Drivers are defined in sections in the odbcinst.ini file. The section name specifies the name of the driver. In this section, the Driver property specifies the path to the driver library. The driver library is the .dylib file located in the lib subfolder of the installation directory, by default in /Applications/CData ODBC Driver for Google Sheets.

[CData ODBC Driver for Google Sheets]
Driver = /Applications/CData ODBC Driver for Google Sheets/lib/libgooglesheetsodbc.dylib

The ODBC Drivers section must also contain a property with the driver name, set to "Installed". For example:

[ODBC Drivers]
CData ODBC Driver for Google Sheets = Installed

odbc.ini

Define ODBC data sources in sections in the odbc.ini file. User data sources can only be accessed by the user account whose home folder the odbc.ini is located in. System data sources can be accessed by all users.


Privileges	Path
User	/Users/myuser/Library/ODBC/odbc.ini
System	/Library/ODBC/odbc.ini

Modifying iODBC's system-wide settings requires elevated permissions; to do so, you can use the sudo command to open a text editor from the terminal. For example:

sudo nano /Library/ODBC/odbc.ini

In addition to the connection properties required to connect to your data source, the Driver property specifies either a driver definition in the odbcinst.ini file or the path to the driver library.

[CData GoogleSheets Source]
Driver = CData ODBC Driver for Google Sheets

Spreadsheet=NorthwindOrders

Additionally, in the ODBC Data Sources section, the DSN must be set to a driver defined in the odbcinst.ini file. For example:

[ODBC Data Sources]
CData GoogleSheets Source = CData ODBC Driver for Google Sheets

Configuring DSNs

To configure a DSN, you can use the iODBC Administrator 64-bit, the GUI installed with iODBC. Note that the ODBC Manager must match the bitness of the ODBC driver. The most recent version of the CData ODBC Driver for Google Sheets is 64-bit only.

You can configure User or System DSNs. User data sources are restricted to a user account. System data sources can be accessed by all users.

iODBC Administrator

If you have registered the driver, you can create user DSNs by opening the iODBC Administrator 64-bit from Launchpad.

To modify the system DSN or create a system DSN, open the iODBC Administrator 64-bit with elevated permissions. To do so, enter the following command into a terminal:

sudo /Applications/iODBC/iODBC\ Administrator64.app/Contents/MacOS/iODBC\ Administrator64

After opening the iODBC Administrator 64-bit, you will see CData GoogleSheets Source listed under the System tab. Select the DSN and click the Configure button to set connection properties as name-value pairs.

To create your own DSN, instead click Add on the User or System tab and then select the CData ODBC Driver for Google Sheets option.

OAuth

Some tools, such as the iODBC Administrator 64-bit, require that you set the following connection property to complete the OAuth browser flow. To authenticate from these tools, you need to add the following connection property to the DSN to ensure that the OAuth flow can execute properly:


Keyword	Value
Other	CheckPromptMode=False

Testing the Connection

You can use the iODBC Demo, available in most iODBC installations, to connect to Google Sheets and execute SQL queries.

iODBC Demo

Complete the following steps to connect from the iODBC Demo:

Open Launchpad and search for "iODBC".
If you need to connect to Google Sheets from an application that can use only the ANSI ODBC API, click iODBC Demo Ansi. Otherwise, click iODBC Demo Unicode.
In the Environment menu, click Open Connection.
Select the DSN on the corresponding tab and test the connection.

You can now execute SQL statements to Google Sheets by clicking Execute SQL in the SQL menu.

Set the Driver Encoding

The ODBC drivers need to specify which encoding to use with the ODBC Driver Manager. By default, the CData ODBC Drivers for Mac are configured to use UTF-32 which is compatible with iODBC, but other Driver Managers may require alternative encoding.

Alternatively, if you are using the ODBC driver from an application that uses the ANSI ODBC API, it may be necessary to set the ANSI code page. For example, to import Japanese characters in an ANSI application, you can specify the code page in the config file /Applications/CData ODBC Driver for Google Sheets/lib/cdata.odbc.googlesheets.ini:

[Driver]
AnsiCodePage = 932