Importing Salesforce data to Microsoft Excel

Introduction

Microsoft Excel is the most popular tool in workplaces around the world enabling organizations to enhance their productivity across different teams with different use cases like planning budget strategy, accounting, data visualization etc.,

Salesforce on other hand is one of the popular CRM’s out there and users want to access Salesforce data in Excel for assorted reasons. Excel provides a lot of different ways to import data in to spreadsheet and one of the easiest ways you can do this is by using Progress DataDirect Salesforce ODBC driver. In this tutorial, we will walk you through on how you can connect and import your Salesforce data to Excel.

Determine your Excel version

  1. To begin, you need to determine if your Excel is a 32-bit installation or 64-bit installation
  2. Open Excel, Go to File - > Account and Click on About Excel. You should now see a window as shown below.


    excel version 

  3. You should now see as highlighted above if your Excel is a 32-bit or 64-bit. Keep this is mind as you need to have this information ahead.

Install and Configure Progress DataDirect Salesforce ODBC driver

  1. Download Progress DataDirect Salesforce ODBC driver from our website. If your Excel version is 32-bit, then download the Windows 32-bit version else if your Excel version is 64-bit, then download Windows 64-bit version of DataDirect Salesforce ODBC Driver.
  2. Extract the setup.exe from the zip file and run it to install the driver.
  3. Stick to default options in installer and you should be done in a minute or two.
  4. Open ODBC Administrator to configure the Salesforce driver. Note that there will be two ODBC Administrators in Windows. One for 32-bit and another for 64-bit ODBC drivers. If you have installed 32-bit DataDirect Salesforce driver then you need to open 32-bit ODBC Administrator else open 64-bit ODBC Administrator
  5. Click on Add and Choose DataDirect x.x Salesforce, you should now see a window like below.

     

    configure salesforce odbc

     

     

  6. Name the data source as you like as shown above.
  7. Optional: Configure Security Token:
    1. Some Organizations require Security Token, while some don’t. If your Organization needs Security Token for accessing Salesforce, then get the security Token by following these instructions.
    2. Back in ODBC Administrator data source configuration, Go to Security Tab and fill in the Security Token.

       

      configure security token

       

  8. Click on Test Connect, and you should now see an authentication window, fill in your Salesforce Username and Password and click on OK. If everything is properly configured, you should be connected successfully.
  9. Click on OK to Save the configuration.

 

Troubleshooting:

When you click on Test Connect, if you see the below Error message:

Details: "ODBC: ERROR [IM003] Specified driver could not be loaded due to system error  193:  (DataDirect 7.1 Salesforce, C:\Program Files (x86)\Progress\DataDirect\Connect_for_ODBC_71\Drivers\ivsfrc27.dll)."

 

This means you don’t have JRE installed. To resolve this

  1. Download Java JRE, if you have installed 32-bit Salesforce ODBC driver, download JRE 32 bit for Windows else download 64-bit JRE and install it.
  2. After installing Java JRE

For the 32-bit Salesforce driver, make sure the paths to the 32-bit JVM are on the system path, for example, C:\Program Files (x86)\Java\jre8\bin;C:\Program Files (x86)\Java\jre8\bin\client;

 

For the 64-bit Salesforce driver, make sure the paths to the 64-bit JVM are on the system path, for example, C:\Program Files\Java\jre8\bin;C:\Program Files\Java\jre8\bin\client;

Connect to Salesforce from Excel

  1. Open MS Excel, go to Data -> Get Data -> From Other Sources and click on From ODBC.

     

    choose odbc

     

  2. Choose the Salesforce Data source that you have configured in the previous section.

     

    choose salesforce

     

     

  3. Click on OK, you should now see authentication window as below. Fill in your username and password and Click on Connect.

     

    authentication

     

  4. You should now see the list of tables as shown below.

     

    list of tables

     

  5. Choose the table that you would like to import, and Click on Load.

     

    salesforce resultsets

     

  6. You should now have all the data in your spreadsheet.

 

If you have any questions or issues, please don’t hesitate to contact us. Also, feel free to try our other SaaS drivers for Eloqua, Oracle Sales Cloud etc.,

ODBC TUTORIAL

Importing Salesforce data to Microsoft Excel

DataDirect
connectors

Connect any data source to any application

DataDirect
Tutorials

Discussions, tips and tricks for
DataDirect Connect drivers