How to connect Salesforce ODBC driver to 2016 Excel for Mac

Simba Technologies allows you to easily connect any ODBC application to Salesforce on the Mac OS X. Today I will walk you through how to connect our Salesforce ODBC driver to Excel for Mac in 4 easy steps. For our following example, we will be using Mac OS X 10.11.2 (El Capitan) and Excel 2016. We will also be using the iODBC driver manager which can be downloaded from their site and installed on your Mac.

Step 1: Download and install the Salesforce ODBC driver

install_salesforce_odbc_driverFirst download the Salesforce ODBC driver and install it on your Mac.

Double-click on the SimbaSalesforceODBC.dmg to mount the image and then double click on the SimbaSalesforceODBC.pkg to run the installer and complete the installation dialogs. The installation program will install the driver in the /Library/simba/salesforceodbc directory by default.

Step 2: Configure a DSN for the driver

We now must configure the driver by modifying the odbc.ini file to define a DSN for it. For this example we are only going to connecting using a DSN so we only need to modify the odbc.ini file. There is another file called odbcinst.ini that is used for defining drivers but this is optional as we can specify the driver location in odbc.ini. Sample files for both odbc.ini and odbcinst.ini for the Salesforce driver can be found in the Setup directory where you installed your Salesforce ODBC driver. By default, the path should be /Library/simba/salesforceodbc/Setup.

First copy over the odbc.ini file to your home directory by opening a Finder window and selecting /Library/simba/salesforceodbc/Setup/odbc.ini and copying it to your home directory.

configure dns for salesforce odbc driver

Alternatively you can copy the file by opening up a Terminal and typing in the following command:

We will work off this version of the file. Configure your ODBC DSN settings by filling out the contents in odbc.ini using a text editor.

Right click on odbc.ini in your home directory and select the “Other…” option in the “Open With” menu.

install salesforce odbc driver folder

You will be prompt to select an application to open this file. We want to select a text editor on your system and by default Mac OS X has a text editor called TextEdit located in your Applications folder. Select TextEdit in the dialog and select “Always Open With” to make all .ini files open using this text editor for convenience. Clicking “Open” will open odbc.ini in the text editor.

install_salesforce_odbc_driver_choose_application

Alternatively you can open the file in a text editor using the following command in the terminal:

When you open the file initially it should look like the following:

The UID, PWD and SecurityToken (if necessary) fields should be filled in with your own credentials or can be left out and supplied in the connection string. Configure the DSN based on your requirements for connecting. For more information regarding all the configuration settings in our Salesforce ODBC driver, please refer to our online documentation.

A possible filled out odbc.ini file could look like the following:

Now that we have a defined a DSN for our driver, we need to let the driver manager know about it. When you install iODBC, there should be a directory at /Library/ODBC/ which contains odbc.ini and odbcinst.ini files.

Open a Finder window and check if there is an odbc.ini file in the folder /Library/ODBC/.

install_salesforce_odbc_driver_library

If there is an odbc.ini file there, then we need to merge to contents of both files into one version. If there is no odbc.ini file, then you may just copy over the odbc.ini file to /Library/ODBC/.

Double click on the odbc.ini file to open the file in a text editor. Then merge the contents of the odbc.ini file in the home directory (~/odbc.ini) and the odbc.ini file used by the whole system (/Library/ODBC/odbc.ini). Save the resulting merged odbc.ini in your home directory to ensure there are no permission errors.

You could open a terminal and type in the following which will open a text editor to modify the system’s DNS configuration file:

If you get an error when opening the system odbc.ini file using the above command saying that “The file /Library/ODBC/odbc.ini does not exist.” then we can simply copy over our filled in odbc.ini file from our home directory.

Now we copy over the modified odbc.ini file to the location that will be used by the whole system. Have 2 Finder windows open with one looking at your home directory and the other one looking at /Library/ODBC. Drag over odbc.ini from your home directory to /Library/ODBC to copy your odbc.ini file over.

install_salesforce_odbc_driver_command

You may alternatively use the following command to copy over odbc.ini from your home directory to the targeted system directory.

Step 3: Testing the connection using iODBC

Before using Excel we want to test the DSN connection using iODBC Test. Open up the iODBC folder in your Applications folder and double click on “iODBC Test Unicode.command”.

install salesforce odbc iodbc test application

This will open up a terminal and start the iODBC Test application. Typing in “?” to the terminal will show you all the DSN defined for your system. We need to see if the system DSN we defined in the previous step is there. If you do not see the DSN you defined then verify that you have modified /Library/ODBC/odbc.ini.

The terminal should look something like the following:

install salesforce odbc mac terminal

To connect the data source type the following connection string into the terminal:

DSN Name is the name of the DSN seen in the DSN column of the table displayed when typing in “?”.

Provide any other connection settings the data source many need such as a username and password. Your final connection string may look like the following:

Once you submit the connection string and have successfully connected you can type in a simple SQL query to test out the driver.

install salesforce odbc mac terminal connection

Step 4: Open Excel and submit a query to Salesforce using our newly defined DSN.

Now that we have installed, configured and tested our ODBC driver we can use it with Excel. Open Excel 2016 and create a new worksheet.
Navigate to the “Data” tab in Excel, select the “New Database Query” button and then select the “From Database” Option.

mac_excel_connect_salesforce_odbc_driver

This will open up the iODBC driver manager dialog box. Select the DSN you want to use. In our case it will be the Salesforce one we defined in step 2. Go to the “System DSN” section, select the Salesforce DSN and click “OK”.

mac excel connect salesforce odbc driver dsn section

A popup dialog should appear that will prompt you to provide the login credentials for your Salesforce account

salesforce_dialog_box

Enter in your Salesforce credentials and select OK. If you have entered the correct credentials then you will be able to see Excel’s query explorer where you can submit your own SQL queries.

mac excels query explorer

Once you have written the query, you can run it by selecting the “Run” button. This will populate the lower right half of the dialog box and will preview the results of the query. Click on the “Return Data” button will bring up the Import Data dialog box where you can specify where to put the Salesforce data on the spreadsheet.

import_data_from_salesforce_to_excel_for_mac

Click OK and now we have our queried Salesforce data in the spreadsheet.

queried salesforce on excel for mac

Troubleshooting:

I cannot edit files directly in /Library/odbc directories:

The issue is related to the permissions of different files in your system. To get around it, copy over the files to somewhere on your user account such as ~/Documents, modify the files there, and copy it to /Library/odbc.

There is no System DSN when I open iODBC:

Verify that there is an ODBC DSN defined in odbc.ini in /Library/ODBC/odbc.ini. You can print out the contents of the file to the terminal using the following command:

If you see that your DSN is not shown then recopy over the odbc.ini file to /Library/odbc/odbc.ini you were working with in step 2.

I issued a query in Excel but nothing comes up:

Excel suppresses some errors the driver may throw so in some cases it may seem like nothing is happening. If this occurs, test out your query in iODBC Test to verify that it is correct before executing it in Excel.

If you want to verify what Excel is sending to the driver then we can enable driver logging by modifying the file /Library/simba/salesforceodbc/lib/simba.salesforceodbc.ini

excel driver logging salesforce odbc driver

Open the file in a text editor and find the following lines:

Change the lines to look like the following:

Restart the application, connect to the DSN and execute the query you were having problems with. The driver log files should appear in the directory you specified in the LogPath. If you do not see any files being generated by Excel then verify that the path exist and that there is writing permissions on that folder. You can use the following command to add write permissions for all users on the folder.

For more information about our driver logging settings, please refer to our Salesforce ODBC driver documentation on our site.