Data Loader in Salesforce Part 2 – Useful tools and Questions

This is a continuation of Data Loader in Salesforce Part I in Data Management Salesforce.

Table of Contents II
Updating Data to Salesforce Using Data Loader
Upserting Data to Salesforce Using Data Loader
Data Deletion Using Data Loader in Salesforce
Types of Record ID we have in Salesforce
Drawbacks of Data Loader
Data Loader vs Data Import Wizard
Data Export and Mass Delete records

1. Updating Data to Salesforce Using Data Loader

We create the updated file with compulsory ID fields to map data correctly.

Step 1. Click on UPDATE,
Step 2. Select the target object and browse the update file.
Step 3. Map the columns to be updated properly. For example, the New price column is to be updated to the Price column. To avoid manual matching in the future you can click on save mapping as well.
Step 4. Browse the location folder for success and error files and Finish!

2. Which is the column/field, on which Update activity is relying?

ID field.

3. Upserting Data to Salesforce Using Data Loader

data loader in salesforce
Data Loader in Salesforce

What is UPSERT?
UPdate + inSERT = UPSERT. It means, by using one command/button and using one file, we can do insert as well as update with different records. This kind of work is useful in real-time project scenarios when you don’t have a chance to check for old or new records. Thereby insert new and update old records in one go. It compares ID fields to make updates/inserts.

UPSERT requires External ID as update makes use of Salesforce ID

External ID can be created while creating a custom field by selecting a checkbox. It’s specifically designed for integration to contact Salesforce applications from an external system.

For example, Salesforce records are being connected with the SAP/.NET platform. It works only with the Upsert command (not insert/update) allowing you to work with other external systems’ record Ids instead of using Salesforce ID.

Fields marked with External ID get indexed and are included in Global Search. (Salesforce record ID which we see in URL, is for internal purposes). 25 such fields are possible per Salesforce Object.

4. What types of fields e can create as External ID fields?

Text, Number, and Email fields.

External Id in Salesforce
mark the field as unique and case sensitive

5. What is the best practice for using an External Id?

To also mark the field as unique and case sensitive.

Steps to Upserts in Data Loader

Step 1. Click on Upserts
Step 2. Select the object and browse the upsert file.
Step 2a. Choose the field having the external ID, to use it for data matching. Next and Finish!

Hereby the record is compared making use of an External Id.

An example of the case is when we receive a file for let’s said an oracle system to be updated in our salesforce system. The oracle file’s Id field can be made as External Id in Salesforce that can be compared to check for new and old records whenever we need to update ( add new and update old records) in the Salesforce database.

6. Data Deletion Using Data Loader in Salesforce

How many minimum columns do we need for the Delete action?

1 column, i.e ID. We can delete records with the Id field alone. So the file for deletion contains ID fields columns

Where to find ID?

Easily from the URL as in URL/ID

Step 1. Click on Delete.
Step 2. Select the object and browse the deletion file.
Step 3. Map field in file to that of the data table.
Step 4. Finish with browsing location of success and error files.

You receive a warning to ask if you want the deletion to happen and voila.

Difference between Delete and Hard Delete?

Data form Delete stays in the Recycle bin for a minimum of 15 days. While Hard Deleted data bypasses the Recycle Bin and gets permanently deleted.

Thereby Hard Delete stays deactivated in the Data Loader window. It can be activated via Manage Users: System Administrator settings (enable bulk API Hard delete) though not advised. Preferably used in the Sandbox environment for sample and testing data projects.

7. Can we bypass a mandatory field during the data loading?

It’s not possible and Salesforce will throw an error!

8. Can we deactivate the Trigger as we don’t want them to work on some specific records which we are loading in DL?

(Deactivation of trigger not advised ON PROD as we are removing a business logic on production by that.)

We can bypass this by adding one more column while we are loading the data with DL. Example: “DataFromDataLoader” with binary logic: Yes/No. And further, add a rule to check for this value in the automation.

9. Working with the Error file

Remove the last Error Column. Modify the data that caused an error and re-upload the same file.

Benefits of Error File: No need to separate successful and failed records.

10. Types of Record ID we have in Salesforce

We have two types broadly.

18 letter Id: This Id is used externally by salesforce for example when data is exported through Data Loader. This is made Case insensitive to match case insensitive platforms outside Salesforce.

15 letter Id: As for internal use, Salesforce deploys 15 letter Ids as such used in the URL. This is Case sensitive.

11. While coding, within Salesforce, what digit ID I will get?

15 Digit.

12. Again to send data to Oracle, How to convert 15 digit ID to 18 digit ID?

Using CASESAFEID(id) function.

This 0056g000062V3oa (case-sensitive) converts to 0056g000062V3oaKWs (case-insensitive).

To capture the 18-character non-case sensitive Id for records in a field, utilize the CASESAFEID() function in a Formula field.

1. Navigate to the  Custom Fields and Relationships of the Object:
Setup> Customize> Object> Fields> Custom Fields and Relationships> New
2. Select Formula as the Field Type
3. Name the Field and select Text as the Return Type
4. Input the following Formula into the Formula Editor:

13. Drawbacks of Data Loader

If there are duplicate records in the file to be uploaded, the data loader itself is not capable of getting rid of duplicate records.

To resolve we need to identify a field that remains unique for every record in the database, like employee Id, Email Id, Transaction Number, etc. and we create the field to be unique in the Salesforce database.

mark the field as unique

Other than Data Loader in Salesforce we also have a not-so-famous tool Data Import Wizard for similar uses.

14. Differentiate Data Loader vs Data Import Wizard

Data LoaderData Import Wizard
designed for technical usersdesigned for less-technical users
(like business users)
Can process up to 50,00,000 (50 lac) records at a time and can be used for complex imports of large sizeUsed for smaller and simpler imports of up to 50,000 records
Can work with any Object standard as well as any custom.Can only import data of Account, Contact, Leads, Solution, Campaign Member, and Custom Objects.
Can do insert,delete, hard delete,update, and upsertCan only insert, update, and upsert.
Can be scheduled to automate via external knowledge.Can not be scheduled.

15. Data Export Salesforce

We also have a Data Export option available in salesforce to export as well as schedule backup from all or selective objects.

Data Export in Salesforce
Data Export in Salesforce

16. Mass Delete Records in Salesforce

Mass Delete Record

Another point of notice in the Data Management scope is the Mass Delete Record option. This Mass Delete Record option comes in handy for cases when Data Loader can’t delete the records like Reports in Salesforce.

17. Can we import history objects in Salesforce using a data loader?

History cannot be created since objects like AccountHistory, CaseHistory, OpportunityHistory, and ContactHistory don’t support create/update operations. If we need that data we should migrate it into a Read Only Custom Object.

What more cannot be migrated using data loader?

  1. Custom Metadata being a metadata record.
  2. Platform Events cannot be migrated.
  3. history of the object are automatically created when any changes are made by user on record and don’t have creatable and updatable attributes.

18. When to use Soap API (default mode) vs bulk api in data loader

Using SOAP API ,we do not need to write any code to use Data loader.By default Data Loader is using the SOAP-based API.

Salesforce officially recommends using the Bulk API when we are processing more than 250K records. You might also use this when you are having issues hitting API call limits. The Bulk API is optimized to load or delete a large number of records asynchronously. It is faster than the SOAP-based API due to parallel processing and fewer network round-trips.

Bulk API does not allow zip file size more than 10 MB.

5 thoughts on “Data Loader in Salesforce Part 2 – Useful tools and Questions”

  1. hi,very good collection for interview purpose.
    it would be more helpful if i could copy the questions.


Leave a comment

error: Content is protected !!