Connect to Databricks from Microsoft Excel

This article describes how to use the Databricks ODBC driver to connect Databricks to Microsoft Excel. After you establish the connection, you can access the data in Databricks from Excel. You can also use Excel to further analyze the data.

Before you begin

• Workspace creation options.

• Create a Databricks cluster and associate data with your cluster. See Run your first ETL workload on Databricks.

• Download the 64-bit version of the ODBC driver for your OS.

• Install and configure the ODBC driver (Windows | MacOS | Linux). This sets up a Data Source Name (DSN) configuration that you can use to connect Databricks to Microsoft Excel.

• Token management API.

• Databricks personal access token.

• Install Microsoft Excel. You can use a trial version.

Connect from Microsoft Excel

This section describes how to pull data from Databricks into Microsoft Excel using the DSN you created in the prerequisites.

Steps to connect using OAuth 2.0

Note

The steps in this section were tested using Excel for Microsoft 365 for Windows Server 2022 Datacenter 64 bit.

The following are the steps to allow a user to connect to Databricks in a single sign-on experience.

1. Launch ODBC Data Sources.

2. Go to System DSN tab and select Simba Spark entry in the DSN list (or you can also create a new DSN by following the instructions hosted on Microsoft site).

3. Click on Configure button and you will see the below pop-up window.

4. On Mechanism, select OAuth 2.0.

5. Click on OAuth Options button, you will see the following OAuth Option pop-up window.

6. Select Browser Based Authorization Code and Uncheck IGNORE_SQLDRIVER_NOPROMPT. Close the pop-up window.

7. Click on HTTP Options button and enter the HTTP path in the pop-up window.

8. Close the HTTP Options pop-up window. Click on Advanced Options button, then click on Server Side Properties button in the pop-up window.

9. Add a server side property Auth_Flow and value 2.

Now you have successfully configured an ODBC DSN.

10. Launch Microsoft Excel and create a new blank workbook. Select menu Data > Get Data > From Other Sources > From ODBC

11. Select the DSN you just configured.

Click the OK button to connect. You will be prompted to authenticate yourself on a browser pop-up window.

Connect using a connection URL with OAuth 2.0

Note

The steps in this section were tested using Excel for Microsoft 365 for Windows version 11.

You can also directly connect Excel to Databricks using a connection URL. The connection URL is in the following format:

Driver=Simba Spark ODBC Driver;Host=<hostName>;Port=443;HttpPath=<httpPath>;SSL=1;AuthMech=11;Auth_Flow=2;Catalog=samples;Schema=default

1. Launch Excel and select menu Data > Get Data > From Other Sources > From ODBC

2. In Data source name (DSN), select Simba Spark

3. Click and expand Advanced options section.

4. Enter the above connection URL in the text box of Connection string (non-credential properties)(optional)

5. Click the OK button.

6. Click the Connect button. You should be prompted by a dialog that you can use to authenticate yourself through OAuth 2.0.

Steps to connect using Databricks personal access token

Note

The steps in this section were tested using Excel for Microsoft 365 for Mac version 16.70.

1. Open a blank workbook in Microsoft Excel.

2. In the Data ribbon, click the down caret next to Get Data (Power Query), then click From database (Microsoft Query).

3. In the iODBC Data Source Chooser, select the DSN that you created in the prerequisites, and then click OK.

4. For Username, enter token.

5. For Password, enter your personal access token from the prerequisites.

6. In the Microsoft Query dialog, select the Databricks table that you want to load into Excel, and then click Return Data.

7. In the Import Data dialog, select Table and Existing sheet, and then click Import.

   After you load your data into your Excel workbook, you can perform analytical operations on it.