January 12, 2021
As we all know, Salesforce keeps playing a leading role among integrated CRM platforms. More and more businesses are taking advantage of it nowadays. However, those of us who used Salesforce at least once in our life came across the situation when we needed to quickly update large data volumes and needed to do it quickly and easily with minimum efforts. However, Salesforce built-in tools do not cover all the scenarios, which users may require, and that is why data loaders started gaining more and more pace.
There is a variety of data loaders either desktop or web-based on the Internet, which you can use for this purpose. Today, we would like to focus on such a cloud-based solution as Skyvia. With this data loader, you can mass update Salesforce records in two ways — either by uploading a ready made CSV file or, if the records for updating can be received from Salesforce itself, by selecting Salesforce as source and target and setting the needed filters.
Alternatively, you can choose the Skyvia Query product and use SQL to update data in bulk. With Skyvia Query, you have two options — either enter SQL statements via code editor if you are an SQL professional or compose statements with visual query builder if you are an SQL beginner.
When it comes to the most popular method of updating data in bulk, updating via CSV files takes the first place. No wonder it is so widely used as for those people who have hundreds or thousands of records in a CSV file and who need to update records asap that is a perfect and fastest solution.
Below we describe explicitly how to mass update Salesforce records with Skyvia Data Loader via import operation. As an example we mass update leads in Salesforce. Our leads have changed the employer company, and we would like to change the old company to a new one in bulk in Salesforce. For reliability, we add extra lookup keys to map FirstName and LastName columns.
Taking the below steps, you will be able to mass update tasks in Salesforce, mass update addresses in Salesforce or any other SFDC records in the similar way.
To successfully update Salesforce records via CSV files, follow this simple step-by-step instruction:
Create a new import package in Skyvia by clicking +NEW in the top menu and selecting Import in the Integration column.
In the opened package editor window check whether the source type — CSV upload manually — is selected. Then, select the Salesforce connection from the drop-down list as a target.
If you haven’t had enough time to create the Salesforce connection in Skyvia yet, click +New connection at the bottom of the drop-down list. The detailed procedure for creating a connection is described in our How to Import Data into Salesforce tutorial. Check it out for more details.
When source and target have been selected, add a task to the import package by clicking the Add new link on the right.
When the task editor opens, upload the required CSV file. The columns, which you have in the CSV file, will be displayed on the right of the task editor. Pay attention to the CSV Separator parameter. It should be selected correctly to display columns as a table (as on our screenshot). When everything looks fine, click Next step to proceed with target settings.
In the Target drop-down list, select the Lead object. Then, select Update as an operation type and go further.
On the Mapping Definition tab, map source columns to target columns. If you have Lead IDs in your CSV file, the ID column is mapped automatically.
If you do not have Lead IDs, you can use Target Lookup. To use it, you need to have a column or a set of columns in a CSV file, which will be used to uniquely identify the record you want to update in Salesforce. This can be any other column than Salesforce ID or even multiple columns.
In our CSV file, we don’t have IDs that is why we select the Target Lookup mapping. As a next step we select Lead in the Lookup Object drop-down list and Id value in the Result Column list.
Under the Lookup Key Column, in the first drop-down list, we choose Company. In the second drop-down list, we select Constant and below enter the company name we want to be replaced in Salesforce.
To add another Lookup Key Column, click +Add Lookup Key. In the first drop-down list, we select FirstName. In the second drop-down list, we remain Column as it is. In the third drop-down list, we select FirstName. We click +Add Lookup Key again and repeat the same action but with LastName. Adding extra lookup keys helps you better find required leads and replace an old employer company for a new one.
After you have finished with the ID column, proceed with the Company column. You need to select Constant and enter the company name, which should be inserted instead of the old one. At the end, click Save to complete your task.
Create and run your package by clicking the corresponding buttons in the title bar. You can check your package status on the Monitor tab. Uploading a result CSV will help you to visually check updated records.
As you see everything is quite simple and easy to configure. The same way you can mass update any Salesforce fields or objects you need to.
Another way to update data with Skyvia Data Loader is to do it through filters. This alternative way is the right solution if the records you need to update can be received from Salesforce itself. As an example let us mass update a contact owner in Salesforce.
To find out how to mass update contacts in Salesforce through filters, perform the following steps:
Create a new import package in Skyvia as described above.
When the package editor opens, click Data Source database or cloud app under Source Type and select the Salesforce connection as a source. Then, select the same Salesforce connection as a target. When both connections are selected, proceed with adding a task to the import package. After clicking the Add new link, you are dropped into the task editor window.
On the Source Definition tab of the task editor window, select Contact in the Source drop-down list.
Afterwards, apply filters to select records for the update. In the Filter section, click the +Condition button on the right and set the condition according to which contacts will be updated. In the first drop-down list, we select Owner. In the second drop-down list, we select Email as we want to update a contact owner by email. In the third drop-down list, we select equals and enter an email. In our example, the contact owner we want to change has the following email address: [email protected].
In Skyvia, you are also free to add multiple filters, which can be united in groups. Each group can consist of several filters and/or subgroups united with a logical operator AND or OR. Find out more about it in the Filter Settings topic.
When everything is ready on the Source Definition tab, click Next step to proceed further.
On the Target Definition tab of the task editor window, select the same Contact object in the Target drop-down list. Then, select Update as an operation type and go further.
On the Mapping Definition tab, map the source columns to the target columns.
Columns are actually mapped automatically. However, we only need to map the Id and OwnerId columns. The Id column should be mapped through column mapping — it will be used to search for a record to update, and the OwnerId column should be mapped through target lookup. The Target Lookup mapping allows getting the ID directly from target tables by other fields, identifying rows, such as emails, names, for example.
We click Clear Mapping on the right of the task editor to clear mapping of unneeded fields and map Id column through Column mapping. Next, we select the Target Lookup mapping for OwnerId column. We automatically receive User value in the Lookup Object drop-down list and Id value in the Result Column drop-down list.
Under Lookup Key Column, select the target lookup key column — Email value. Two new drop-down lists are displayed. The first determines how to select the lookup object rows. It is set to Column by default. You need to change it to Constant and enter an email address of a new contact owner. When you are ready with a task, click Save.
If everything is correct, click Create to create an import package and run it. After that, on the Monitor tab, you can check whether your package has been successful or not by clicking the Run ID line.
The third way to update Salesforce data in bulk is through Skyvia Query using SQL. Skyvia Query also allows you to preview changes before applying, so the operation is safe. Skyvia Query can be used by both — experienced SQL users and SQL beginners. Except for UPDATE statements, Skyvia Query also supports SQL SELECT, INSERT and DELETE for cloud sources. You can find more about Skyvia Query in our documentation. Below we describe in simple steps how to mass update opportunities in Salesforce, to be exact how to mass update a Salesforce opportunity owner.
You create a query in a standard way by clicking +NEW and selecting Builder in the Query
The query editor opens. To query data, first you need to create a connection to Salesforce by clicking the +New connection link (in case you have not created it yet) or select the already created Salesforce connection from the drop-down list on the left.
To update an opportunity owner, you need to know the owner ID. If you do not remember it, you can easily extract this ID from the User table, filtering User by email.
So, to query data from a table, simply drag this table from the Salesforce object list to the Result Fields pane. In our example, we drag User to the Result Fields pane.
Next, to filter data by email, we click User and drag the User.Email column from the User table to the Filters pane. Then we click User.Email in the Filters pane and configure the filter in the Details pane on the right side of the query. Finally, we check our query and click Execute to run it.
The query result field displays a record with User ID. You need to copy the ID to use it in your second query.
Now we need to create another query to update the OwnerID. To create the second query, click the + button on the query page tab bar. A new query will be created with the same view opened as the previously active query. To switch to the necessary view, click the corresponding button on the right side of the query toolbar — in our case it is SQL view button. We enter the UPDATE statement to the code editor. It looks like the following:
UPDATE Opportunity SET OwnerId='005A0000001gx22BBG' WHERE OwnerId = '005A0000001gx22IAA'
When you have finished your second query, click Execute to run it and mass update Salesforce opportunities (opportunity owner to be exact).
Those are three ways that can make your life easier when mass updating a large amount of data in Salesforce. Depending on whether you have a CSV file with necessary records or records for updating are received from Salesforce itself, you choose on your own which way to use. With Skyvia Data Loader, you can also schedule your mass update operation for any time you want. We also recommend trying our Query product. With SQL, you will update your SFDC records even quicker, and our technical support is always at hand to assist you in any questions you have.
We hope this article was helpful. We are waiting for your feedback or comment.