Share via

Connect to Azure Databricks from Microsoft Excel using ODBC driver

Important

This documentation has been retired and might not be updated. The products, services, or technologies mentioned in this content are no longer supported. See Connect to Azure Databricks from Microsoft Excel.

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

Before you begin

Connect from Microsoft Excel

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

Steps to connect using OAuth 2.0

Note

  • This connection method does not work in Excel on macOS.
  • 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 Azure 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 on the Microsoft Support website).

  3. Click Configure and the following window appears.

    Spark DSN

  4. On Mechanism, select OAuth 2.0.

  5. Click OAuth Options and the following OAuth Option window appears.

    OAuth Options

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

  7. Click HTTP Options, enter the HTTP path, and then close the pop-up window.

    HTTP Options

  8. Click Advanced Options, and then click Server Side Properties in the pop-up window.

    Advanced Options

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

    Server Side Properties

    Add a Server Side Property

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

    ODBC Data Source

  11. Select the DSN you just configured.

    From ODBC

    Click OK to connect, and then authenticate.

Connect using a connection URL with OAuth 2.0

Note

  • This connection method does not work in Excel on macOS.
  • The steps in this section were tested using Excel for Microsoft 365 for Windows version 11.

You can also directly connect Excel to Azure 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 Advanced options to expand the section.
  4. In the Connection string (non-credential properties)(optional) text box, enter the connection URL from the previous step and then click OK.
  5. Click Connect and then authenticate through OAuth 2.0.

Steps to connect using Azure 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 caret next to Get Data (Power Query), and 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 Azure 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.

  • Last updated on