Connect to SAP HANA from Excel for macOS

Published in

OpenLink ODBC, JDBC, ADO.NET Data Access Drivers Blog

4 min readMay 31, 2019

OpenLink Software’s Lite Edition ODBC-JDBC Bridge driver lets macOS users access their SAP HANA databases from Excel by utilizing the JDBC driver provided by SAP. This article covers use of our JDBC-ODBC Bridge Driver, the iODBC Driver Manager, and iODBC Administrator components for macOS with regards to the following:

SAP HANA ODBC Data Source Name setup on macOS
Data Source Name usage from Microsoft Excel for macOS

Prerequisites

Installation of the SAP HANA client and a working JDBC connection to your SAP HANA instance. This can be confirmed using various Workbench tools such as SQLWorkbenchJ and DBeaver
Knowledge of your SAP HANA instance’s default SQL listener port. The default port is 39013.
A Working Installation of OpenLink Software’s Lite Edition ODBC-JDBC Bridge Driver with an Evaluation or Commercial License (Instructions).

Instructions

Add the SAP HANA ngdbc.jar document included in the SAP HANA client package to /Library/Java/Extentsions .

2. Open iODBC Administrator, and click on add. Select OpenLink JDBC Lite Driver or OpenLink JDBC Lite Driver (Unicode), then click Finish.

3. Select a name for your Data Source Name (DSN), add a description if needed, and click Continue.

4. Enter the JDBC Driver Classname , and JDBC String for your SAP HANA instance.
The Classname is:

com.sap.db.jdbc.Driver

The URL scheme is:

jdbc:sap://<server host>:<port>[/?<options>]

5. Check the Connect now to verify that all settings are correct checkbox, enter your SAP HANA username/password, and Click on the Test tab.

If a successful connection is made, a confirmation message will appear. Click OK

6. Click Test Data source. A successful test connection will result in another confirmation message. Click OK, followed by Finish.

7. Open a new workbook or spreadsheet in Excel. Click on the Data tab, and select From Database.

8. Select the ODBC Data Source for your SAP HANA database and click OK .

9. Enter your username and password, and click Connect

10. Microsoft Query will open following a successful connection. Enter your query in the SQL STATEMENT text box, and click run.

11. On successful execution, a table will be generated and populated beneath the text box. Click Return Data to populate the spreadsheet with your results.