Salesforce is a multifunctional and highly customizable CRM platform, which is used by millions of users to manage complex business processes and boost the productivity of sales, customer service, marketing teams, etc.
Microsoft Excel is a spreadsheet program, which is so familiar and comfortable for many users worldwide, and there are situations when it is much better and easier to work with data in the Excel worksheets rather than in the native UI of a cloud app.
However, it’s not a trivial challenge to export huge data pieces from Salesforce to the Excel worksheets as you need built-in features or external tools, which allow exporting data to the Excel format.
We briefly describe 5 ways how to connect Salesforce to Excel, easily export data from Salesforce to Excel worksheets, pros and cons of these ways and typical use cases applied.
Table of contents
1. Salesforce Data Loaders
To export data using data loaders is the first and most standard way. Using these tools, you can export data from Salesforce to Excel as CSV files, make changes to these files in Excel and import changed CSV files back to Salesforce. You can find many of such data loaders on the Internet, and many of them have free versions. You can also try native Salesforce built-in data loader (first-party tool), which you can use after installing it on your PC. However, third-party data loaders such as, for example Skyvia Salesforce data loader, can be more robust than Salesforce’s native offerings. For example, you don’t need to download Skyvia on your computer or laptop as it is a cloud-based solution. You need only a web browser to access it and set minimal configuration to safely export and import data as CSV files.
Let us provide such an example: we need to export Account and Contact objects as CSV files from Salesforce to Excel. For this, we create an export package in Skyvia: we select a Salesforce connection, choose CSV as a target type and add required objects from Salesforce.
Then, we click Create to create a package and Run to run a package. The data are obtained.
Pros of data loaders:
- Support of a mass update of data in a single go;
- Support of Insert, Upsert, Update and Delete operations;
- Cross-object data extract to one file;
- Detailed information on the successfully changed/failed rows during export/import;
- Simple interface, no preliminary training is required.
Cons of data loaders:
- Data can be exported/imported via CSV files only;
- Limits of free usage;
- Set up of the initial process for an upload.
This way of getting Salesforce data is a universal one and can be equally profitable for individuals and companies. With simple interface of data loaders, you need minimum configuration and less efforts. Salesforce data loaders support different types of operations and mass update of data. Moreover, after each package run, you get a report on successfully exported/updated rows and on those which failed.
2. Excel Original Interface
To get data from Salesforce to Excel through Excel original interface is a perfect solution for users of Microsoft Office Professional Edition. Unlike data loaders, in this case the data are displayed directly in the Excel UI, you do not need to use intermediate CSV files.
To export data, open a blank Excel workbook, click on the Data tab, then Get Data → From Online Services → From Salesforce Objects.
In the opened window, select between connecting to Production or Custom environment and sign in to Salesforce.
Having signed in to Salesforce, select the objects you want to export to Excel in the Navigator window. If you want to export more than 1 object, click on the Select multiple items check box. On the right, you can preview a table. Click Load to export data to Excel worksheet. As we had selected two objects – Account and Attachment, the corresponding data were downloaded and looked like on the picture below.
Pros of original interface:
- All-in-one solution;
- No need to search for third-party tools;
- Direct data export without using intermediate CSV files.
Cons of original interface:
- High price for a single-user license;
- No possibility to load modified data back to the source.
This way of getting data from Salesforce to Excel is the right one for users and companies that use Microsoft Professional Edition. However, if the company is big and has many employees, licensing each PC will cost a lot. For such companies, third-party data loaders or other tools, mentioned below, may be a much better and more profitable solution.
3. Excel Add-Ins
The third way to pull Salesforce data into Excel is to use Excel Add-Ins for Salesforce. Add-ins help you to connect Microsoft Excel to Salesforce, load data from Salesforce to Excel, instantly refresh data in an Excel workbook from Salesforce, edit these data, and update Salesforce from Excel. It enables you to work with Salesforce contacts, accounts, opportunities, leads, users, attachments, tasks, and other objects like with usual Excel worksheets, as well as to perform data cleansing and de-duplication, and apply all the Excel’s powerful data processing and analysis capabilities to these data.
Pros of Excel add-ins:
- Direct data export/import without using intermediate CSV files;
- No additional settings needed: add-ins integrate into the familiar Excel interface, hence, no need in opening additional windows or configuring extra settings;
- Easy data modification;
- Easy data refreshing;
- Obligatory data validation (add-in validates entered data to make sure it matches to the source column data type);
- Possibility of composing queries manually for both relational databases and cloud applications.
Cons of Excel add-ins:
- Paid tool;
- Add-in is specific to Excel – it cannot be used with any other application;
- Add-in download required.
Summarizing the above, Excel add-in can be a good solution for users who would like to increase the power and functionality of Excel. You can enter SQL SELECT statements of any complexity, you can validate data, modify them or refresh with a single click. Devart Excel Add-in is considered as one of such solutions. You can find a free trial available here. Alternatively, you can try Skyvia Query Excel Add-in, the intuitive visual query builder of which makes this add-in simple in use.
4. ODBC Drivers
Another way of performing Salesforce export to Excel is by using ODBС drivers. With ODBC drivers, the same as with add-ins, you export data directly into an Excel spreadsheet. However, note that unlike add-ins, ODBC drivers do not allow loading data modified in Excel back to the source.
Before installing ODBC driver, make sure that you use matching Excel and ODBC Driver, e.g. if you use the 64-bit version of Excel, you need to install a 64-bit ODBC Drive. Being installed for a specific data source, for example Salesforce, ODBC drivers can work not only with Excel, but also with many other tools – that is a great plus of them.
Pros of ODBC drivers:
- Support for ODBC connectivity across multiple third-party products;
- Salesforce data types and functions are mapped to ODBC data types and functions, which are understood by all ODBC-compatible tools.
Cons of ODBC drivers:
- No possibility to load modified data back to the source;
- Absence of the Salesforce original ODBC driver, i.e. only third-party ODBC drivers available;
- Installation required.
Summing up the information above, we would like to emphasize that the full support for standard ODBC API functions and data types implemented in the drivers makes interaction of Excel and database applications with Salesforce CRM fast, easy and extremely handy. There is a wide variety of ODBC drivers you can find on the Internet, and one of them is the Devart ODBC driver for Salesforce. You can read more about it here.
Another example is a Skyvia’s universal ODBC driver which supports a wide variety of data sources via Skyvia Connect SQL endpoints , including Salesforce. Unlike most ODBC driver on the market, this driver offers pay-for-traffic pricing model, which can be useful if don’t need to load large data volumes. Moreover, Skyvia Connect offers additional security level, allowing you to provide access without sharing Salesforce login and password, and detailed logging of all data access operations.
5. OData Endpoints
OData (Open Data Protocol) is an OASIS standard that defines the best practice for building and consuming queryable and interoperable RESTful APIs in a simple and standard way.
Excel allows you to connect to OData endpoints. Unlike with ODBC, with OData you configure everything in advance – you create an endpoint to receive Salesforce data via OData or use the already created one by simply pasting its URL to Excel. However, creating an OData endpoint yourself is not an easy task, you need to care about OData server hosting, deployment, and administration.
To reduce time, costs and efforts – you can use third-party solutions to simplify use of OData. One of such solutions is Skyvia Connect. Being a cloud solution, Skyvia Connect does not require downloading, hosting, maintenance. You do not need to develop any API – you generate endpoint, configure settings and determine which data can be accessed and which not, and who can access these data.
As a prerequisite, we will use an already configured Salesforce endpoint with enabled HTTP basic authentication and Microsoft Excel 2019.
Creating Connection
First, you need to set up a connection with Skyvia Connect endpoint. For this, perform the following steps:
- Click on the Data tab, then Get Data → From Other Sources.
- Click From OData Feed.
- In the pop-up window, select Basic, paste the required endpoint URL and click OK.
- Specify again a link to the Salesforce endpoint in Skyvia Connect and fill out the User Name and Password fields.
- Please pay attention that Microsoft Excel requires OData connection to be authenticated either through Windows authentication or HTTP basic authentication. You need to use HTTP basic authentication.
- Click Connect.
Exporting Data
Microsoft Excel will attempt to connect to the specified URL and, in case of successful connection, will display the list of specified Salesforce objects added to the endpoint.
- Select Contacts and ContactFeeds tables and click Load.
- Optionally, you can click the Transform Data button. It allows you to make changes to the data being loaded to Excel, i.e. to filter something, add some column, etc. However, this applies only to the data being loaded to Excel. The data in Salesforce will not be changed anyhow.
- The worksheet is filled out with the data from the specified table.
OData as all other methods of data export mentioned above has its pros and cons.
Pros of OData:
- Endpoint authentication: an opportunity to provide access to endpoint data only to authenticated users, possibility to limit IP addresses, from which the endpoint data can be accessed;
- No need to share Salesforce credentials;
- Open standard approach.
Cons of OData:
- Need to build API manually or use paid services;
- No possibility to update Salesforce data from Excel.
This method of data export can be a perfect solution for those users who already have OData endpoints for their data, or who need to publish their data through OData not only for Excel, but also for other applications. Moreover, with such service solution as Skyvia Connect you do not need to build API manually, care about security, hosting and domain, obtain respective certificates, deploy, administer or maintain, Skyvia Connect will do it for you. It provides easy endpoint generation, limited access to endpoint data, hence higher security and control of data. However, please note that this method does not allow loading data modified in Excel back to the source.
That was a brief description of 5 ways, which you can use, depending on your business needs.
What way of Salesforce data export to Excel do you prefer and why? Was this article useful for you? Leave your comment here or send us your feedback.
