May 7, 2020
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.