When using data loader, it's clear how powerful and useful it can be, especially for large data projects. One of its most limiting factors is that you can’t see your spreadsheet while running your data load, and you have to save the file and upload it into data loader. Tasks like that can become repetitive and quickly infuriating. The XL-Connector tool by Xappex solves any issues that you’ve ever had with data loader. While it may lag behind data loader when it comes to huge data projects, it has data loader beat in all other categories.
Here, we will go over how to pull data from Salesforce into Excel, make updates on it, and then insert back into Salesforce all without leaving your Excel spreadsheet. We will be pulling a list of Leads and their “Status” values, then we will exchange the Lead statuses of “Closed - Not Converted” with “Working - Contacted”.
To get started with XL-Connector, first download it from this page.
It’s worth noting that XL-Connector is a paid application, and we believe it’s worth every penny! You can get the app for $8.25 per computer per month.
Once downloaded, the plugin will appear in the ribbon bar of your Excel window.
Click the log in button and choose your preferred login method.
Now that you are logged in, the tool buttons will no longer be grayed out.
Usually, your data updates might start with running a Salesforce report and downloading the file where updates will be made. Or even the data loader allows you to extract a report into a spreadsheet, but these methods are a bit more tedious and end up creating numerous spreadsheet files on your computer.
Instead, from XL-Connector you can click the Get Data button to pull the records into your current spreadsheet.
Next select the object to pull records from, and which fields you would like to pull. The query window will auto-populate with the SOQL format for the object and fields you selected. Here, we are querying Leads to retrieve their ID, Name, and Status.
Once your query is set up with the necessary fields, click the “Execute” button in the lower right corner and the values are pulled into your spreadsheet.
Now for the data manipulation!
It helps to add an Excel filter to your spreadsheet so that you can easily find the records you are looking for. To do this, click on the Data tab in the Excel ribbon, then click the Filter button.
Now, for our use case we are exchanging the Lead statuses of “Closed - Not Converted” with “Working - Contacted”. With the newly added filters, remove all Leads whose status is not “Closed - Not Converted” by unchecking them from the filter dropdown.
We are left with four Leads whose Status values will be updated.
Next, update the Status values.
Now that the values are updated, you are ready to re-insert them back into Salesforce with the updated values.
Highlight the selection of records to be updated by dragging the cursor over them, then select Update from the XL-Connecter tab and click Update Selected.
You will be prompted to map the column names from your Excel spreadsheet to their corresponding field names in Salesforce. Since we are only updating the Status field and not the Name field, we click the “Clear” button in the row containing the Name field. This tells XL-Connector to ignore that field when making the updates.
For the ID field and Status field, ensure that the checkbox next to it is checked, and ensure that the “Field in Salesforce” column is populated with the correct field API name. If the API name is not populated, you can find the field in the field list and drag it down to that row.
Once you are finished mapping the values, click the Update button in the lower right corner and click Yes on the alert that pops up. This will begin the update.
Once the update has finished running, the columns next to your data in Excel will be populated with the success or error results.
And that's the end of it! This process can save some time when doing data loads and gives you all of the tools and information you need right in your spreadsheet. You can make other updates to the spreadsheet as needed and continue to update, insert, or delete records on the fly.
XL-Connector has many other advanced features such as merging duplicate records and editing picklists, and we encourage you to review the documentation for these here.
To learn more, visit Xappex’s website.