This is a continuation of Data Loader in Salesforce Part I in Data Management Salesforce.
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 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 location folder for success and error files and Finish!
Which is the column/field, on which Update activity is relying? ID
Upserting Data to Salesforce Using Data Loader
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 oil or new records and thereby to 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 system’s record Ids instead of using Salesforce ID.
Fields marked with External ID get indexed and are includes in Global Search. (Salesforce record ID which we see in URL, is for internal purposes). 25 such fields are possible per Salesforce Object.
What types of fields e can create as External ID fields? Text, Number, and Email fields.
What is the best practice for using 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.
Example for the case being 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.
Data Deletion Using Data Loader in Salesforce
How many minimum columns do we need for 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 voilla.
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.
Can we bypass a mandatory field during the data loading? It’s not possible and Salesforce will throw an error!
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.
Working with the Error file
Remove the last Error Column. Modify the data that caused error and re-upload the same file.
Benefits of Error File: No need to separate successful and failed records.
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.
As for internal use, Salesforce deploys 15 letter Id: used in URL. This will be Case sensitive. Example .salesforce.com/0056g000062V3oa
While coding, within Salesforce, what digit ID I will get? 15 Digit.
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:
Drawbacks of Data Loader
If there are duplicate records in the file to be uploaded, 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.
Other than Data Loader in Salesforce we also have a not so famous tool Data Import Wizard for similar uses.
Data Loader vs Data Import Wizard
|Data Loader||Data Import Wizard|
|designed for technical users||designed for less-technical users |
(like business users)
|Can process upto 50,00,000 (50 lac) records at a time and can be used for complex imports of large size||Used 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 upsert||Can only insert, update, and upsert.|
|Can be scheduled to automate via external knowledge.||Can not be scheduled.|
Data Export Salesforce
We also have a Data Export option available in salesforce to schedule backup date from all or selective objects.
Mass Delete Records in Salesforce
Another point of notice in the Data Management scope is the Mass Delete Record option. This Mass Delete Record option comes handy for cases when Data Loader can’t delete the records like Reports in Salesforce.