macOS DSN Configuration
This section shows how to set up ODBC connectivity and configure DSNs on macOS.
Minimum macOS Version
The CData ODBC Driver for Excel Services driver requires macOS Sierra (10.12) or above.
Licensing the Driver
In a terminal, run the following commands to license the driver. To activate a trial, omit the <key> input.
cd "/Applications/CData ODBC Driver for Excel Services/bin" sudo ./install-license <key>
You'll be prompted for a name and password. These refer to your name and your machine's password.
Connecting through a Driver Manager
On macOS, the CData ODBC Driver for Excel Services 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.
The driver installation registers the driver with iODBC and creates a system DSN, which you can use in any tools or applications that support ODBC connectivity.
The driver manager loads the driver and passes function calls from the application to the driver. The driver must be registered with the driver manager and DSNs are defined in the driver manager's configuration files.
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 Excel Services is 64-bit only. Alternatively, you can edit the iODBC configuration files.
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.
Configuring a DSN with the iODBC Administrator
You can create user DSNs by opening the iODBC Administrator 64-bit from Launchpad.
To modify the system DSN installed by the driver 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\ Administrator64After opening the iODBC Administrator 64-bit, you will see the CData ExcelServices 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 Excel Services option.
This section provides the details for connecting to SharePoint Online and SharePoint On Premises.
Connecting to a Workbook
Regardless of which edition of SharePoint you are using, set File to the Excel workbook. This path is relative to the following properties:
- Library: The Shared Documents library is used by default. You can use this property to specify another document library in your organization; for example, if you want to connect to OneDrive for Business, set this property to "Documents".
- Folder: You can use this property to specify a path to a subfolder in a library. The path is relative to the library name specified in Library.
Connecting to Spreadsheet Data as Tables
The driver detects the available tables based on the available objects in the underlying API.
The APIs surface different API objects; select the API based on the organization of your spreadsheets and your SharePoint version:
- OData: The OData API allows access to tables defined from Excel table objects (not ranges or spreadsheets), which you create by clicking Insert > Table in Excel. This is the default API. When connecting to the OData API, the driver may not return any tables if you do not have table objects defined in your workbook. Set UseRESTAPI to true to connect to spreadsheets or ranges as tables.
- REST: The REST API enables access to tables defined from Excel table objects, ranges, and spreadsheets. Requesting a large number of rows from ranges and spreadsheets is restricted by the REST API, and the driver limits the number of rows returned to 100 by default. The driver also defaults to detecting column names from the first row; set Header to disable this.
With DefineTables additionally set, you can define tables based on ranges, using the Excel range syntax. A range that is too large will be restricted by the API.
See Data Model for more information on how the driver detects tables and how to query them.
Connecting to SharePoint Online
Set SharePointEdition to "SharePoint Online" and set the User and Password for an Azure Active Directory account.
Set the Url to a site collection to query workbooks in all nested subsites. Set the Url to a site to query workbooks in that site only.
|Site||https://teams.contoso.com/teamA or https://teamA.contoso.com|
Connecting to SharePoint On Premises
Set SharePointEdition to "SharePoint OnPremise" and set the Url to your server's name or IP address. Additionally, set SharePointVersion and the authentication values.
To authenticate to SharePoint OnPremise, set AuthScheme to the authentication type and set User and Password, if necessary.
Note: When connecting to SharePoint On-Premises 2010, you must set UseRESTAPI to true.
- Windows (NTLM): This is the most common authentication type. As such, the driver is preconfigured to use NTLM as the default; simply set the Windows User and Password to connect.
- Kerberos and Kerberos Delegation: To authenticate with Kerberos, set AuthScheme to NEGOTIATE. If needed, provide the User and Password properties. To use Kerberos Delegation, set AuthScheme to KERBEROSDELEGATION. KerberosKDC, KerberosSPN, and KerberosRealm enable control over the components of Kerberos authentication.
- Forms: This allows authentication through a custom authentication method, instead of Active Directory. To use this authentication type, set AuthScheme to FORMS and set the User and Password.
Configuring a DSN in the iODBC INI Files
Configure DSNs in odbc.ini. Register ODBC drivers in odbcinst.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.
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 ExcelServices Source] Driver = CData ODBC Driver for Excel Services Url=https://myorg.sharepoint.com Userfirstname.lastname@example.org Password=password File=Book1.xlsx
Additionally, in the ODBC Data Sources section, the DSN must be set to a driver defined in the odbcinst.ini file. For example, below is the entry for the DSN created during the driver install:
[ODBC Data Sources] CData ExcelServices Source = CData ODBC Driver for Excel Services
You may need to modify the installed driver definition if you change the path to the driver library.
To register an ODBC driver, modify the odbcinst.ini file. With iODBC, drivers can be available to only one user account or drivers can be available system wide.
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 Excel Services.
[CData ODBC Driver for Excel Services] Driver = /Applications/CData ODBC Driver for Excel Services/lib/libexcelservicesodbc.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 Excel Services = Installed
Testing the Connection
You can use the iODBC Demo, available in most iODBC installations, to connect to Excel Services and execute SQL queries.
Complete the following steps to connect from the iODBC Demo:
- Open Launchpad and search for "iODBC".
- If you need to connect to Excel Services 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.
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 Excel Services/lib/cdata.odbc.excelservices.ini':
[Driver] AnsiCodePage = 932
Uninstalling the Driver
The easiest way to uninstall the driver is to open a terminal and run the included uninstall.sh script, located in the installation directory. For example:
cd "/Applications/CData ODBC Driver for Excel Services" sudo ./uninstall.sh
Note: The script needs to be run from the installation directory.