Navigation
This version of the documentation is archived and no longer supported.

Connect from Microsoft Excel

You can import data from a MongoDB collection into a Microsoft Excel spreadsheet with the MongoDB Connector for BI and an ODBC data connection.

Prerequisites

Authentication

New in version 2.2: The MongoDB Connector for BI now offers MySQL authentication plugins allowing you to connect with authentication or TLS/SSL.

There are two options for authenticating your connection:

  1. Use an authentication plugin with either the SCRAM-SHA-1 or PLAIN authentication mechanism.

    Important

    The use of TLS/SSL is recommended as:

    • The SCRAM-SHA-1 mechanism hashes the passwords in the client plugin. However, all other data is in cleartext.
    • The PLAIN mechanism sends the password in cleartext.

    See C Authentication Plugin or Install the JDBC Authentication Plugin for more information on the plugins.

  2. Provide cleartext credentials for simple username/password authentication against a MongoDB database. This method uses MySQL’s built-in mysql_clear_password plugin.

    Note

    Authentication with mysql_clear_password requires the use of TLS/SSL. See Connect from MySQL with Authentication and TLS/SSL for more information.

The MongoDB Connector for BI requires authentication when running with --auth. When the MongoDB Connector for BI receives a connection with authentication credentials from a client, it passes those credentials through to the underlying MongoDB instance.

You can specify the following authentication options after your username as URI-style query parameters:

Connection Option Description
source

Specify the name of the database which stores the user’s credentials. If you do not specify this option, the MongoDB Connector for BI will default to the current database associated with the MySQL connection.

For authentication mechanisms such as PLAIN (LDAP) that delegate credential storage to other services, set the source value to $external.

mechanism

Specify the mechanism that the MongoDB Connector for BI should use to authenticate the connection. Accepted values include:

LDAP requires MongoDB Enterprise. Set the source to $external when using LDAP.

Note

Neither Kerberos nor x.509 are supported.

Example

To authenticate as user grace with the LDAP authentication mechanism, use the following username:

grace?mechanism=PLAIN&source=$external

New in version 2.2.

Connect from Excel with the C Authentication Plugin

Before beginning this tutorial, make sure you have a running mongosqld instance.

1

Start Excel

Start Microsoft Excel and open a blank worksheet.

2

Select the Data Tab

Select the Data tab to open the Data toolbar.

3

Open a Data Connection Wizard Dialog

  1. Click the Get External Data button on the left side of the toolbar.
  2. Click From Other Sources.
  3. Select the From Data Connection Wizard option.
Screenshot of the Data Connection Wizard option
4

Select ODBC DSN

Select ODBC DSN from the list of data source options and click Next.

Screenshot of data source options selection box
5

Select Your DSN

Select the DSN which connects to your BI Connector instance and click Next.

Screenshot of DSN list
6

Select a Database and Table

Select a database from the dropdown list and a collection from which to import data. Click Next when finished.

Screenshot of database and table list
7

Save the Connection File

Save the data connection file and click Finish. If you wish to re-use this connection in the future, you can select it from the Data -> Get External Data -> Existing Connections menu.

8

Specify Worksheet Format

In the final dialog window you can specify a format for your worksheet. Click OK when finished.