Docs Menu
Docs Home
/
MongoDB Connector for BI
/
Connect BI Tools

Connect from Microsoft Excel

On this page

  • Prerequisites
  • Procedure

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

To connect Excel to the MongoDB Connector for BI, you must first create a system Data Source Name (DSN).

Connecting Excel to the MongoDB Connector for BI requires the following:

  • 64-bit version of Excel. Run the following command to determine whether the 64-bit or 32-bit version of Excel is installed:

    file -N /Applications/Microsoft\ Excel.app/Contents/MacOS/Microsoft\ Excel

    The following table lists the possible outputs of the command above and their respective meanings:

    Return Value
    Meaning
    Mach-O 64-bit executable x86_64
    64-bit binary
    Mach-O executable i386
    32-bit binary
    Mach-O 64-bit executable x86_64
    Mach-O executable i386
    FAT binary (compatible with both 32-bit and 64-bit processes)

    For information on upgrading to the 64-bit version of Excel, see Microsoft Support.

  • Install iODBC.

    Note

    Both the 64-bit and 32-bit versions of iODBC are included with the installer. If you use iODBC to test your DSN, you must use the 64-bit version of the application.

    iODBC is not recommended for creating or modifying your Data Source Name (DSN). To create or modify your DSN, use the ODBC Manager application that is included with the MongoDB BI Connector ODBC Driver.

  • Create a Data Source Name (DSN).

    Important

    Excel requires the following settings in your Data Source Name (DSN) configuration:

    • The DATABASE keyword must be specified in your DSN. If the DATABASE keyword is not set, Excel will not recognize any collections.

    • TLS/SSL certificates must be stored in the /Library/ODBC/ directory. All TLS/SSL keywords in the DSN must point to the certificates in this directory.

Procedure

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
click to enlarge
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.

1

Start Excel

Start Microsoft Excel and open a blank worksheet.

2

Open the iODBC Data Source Chooser dialog

  1. Select the Data tab to open the Data toolbar.

  2. Click the New Database Query button on the left side of the toolbar.

    If the New Database Query button is not displayed, click Get External Data, then New Database Query.

  3. Click From Database.

Screenshot of the From Database option
click to enlarge
3

Select Your DSN

  1. Click the System DSN tab.

  2. Select the DSN which connects to your BI Connector instance.

  3. Click OK.

Screenshot of the DSN selection dialog
click to enlarge
4

Enter Credentials

If you are running the BI Connector with authentication enabled, in the ensuing dialog enter the username and password used to connect to your BI Connector instance.

Note

When specifying a username, include the authentication database for the user. For example, salesadmin?source=admin.

If you are not running the BI Connector with authentication enabled, leave these fields blank.

Click Ok.

5

Select a Table

  1. In the left side of the dialog, click your server name to expand the list collections in your database.

  2. Select the collection from the list from which contains the data you want to import.

  3. To view your data before importing, click Run to run the generated SQL statement. Your data appears in the table below the statement.

  4. Click Return Data.

Screenshot of the Table selection dialog
click to enlarge
6

Import the Data

  1. Select how you would like to import the data into Excel.

    You can choose to import the data into:

    • An Existing Sheet, specifying in which cell to begin the table.

    • A New Sheet, automatically beginning the table in cell A1.

    • A PivotTable in a new sheet.

  2. Click OK to complete the import process.

Example

The following image shows the results of importing data from the supplySales table into a new sheet:

Screenshot of imported data in a new sheet
click to enlarge

Note

Excel for Mac may not properly display special characters, such as letters with accent marks.