Salesforce Dataloading — Common Pitfalls

Don't forget to check out: DataLoader in Salesforce - An Introduction

Pitfall 2: Manual Mapping

Let’s dive into the next phase of imports: mapping columns from your CSV file to Salesforce fields. If you have a very simple object model, this usually won’t be too much of a problem. But there are some things to consider:

• Usually, the mapping of fields from Excel columns to Salesforce Object fields can be daunting for business users. Especially if you have ambiguous field names or complex object types.
• This means that often this is a repetitive task for Salesforce Administrators. Which puts the work of importing at the wrong desk: a business user should be managing his/her own data, you shouldn’t have to do it for them.

NB: SmartUpload allows administrators to create mappings once and assign them to users, where business users can only upload data and not modify the mapping.

Pitfall 3: ID Lookups and Relationships

Within Salesforce, objects can be connected using lookup or master-detail relationships. That’s a great way to keep your data clean, but can be quite a headache if you have a lot of interconnected data to import.

Let’s consider the following example; one of the most complex I have encountered during a consultancy gig:

This is the source Excel that needs to be imported into the ‘SMC__Supplier_Catalogue__c’ object: a supplier catalogue as part of the SCM package of Financial Force. That object has a lot of master-detail relations to other objects, like:

• Catalogue Name: this is the link to the parent Supplier Catalogue record
• Vendor: this is actually a lookup to an Account
• Item Code: this is actually a lookup to an Item Master record
• Currency: this is actually a lookup to the Currency Master object
• Stocking UOM: which points to a unique Stocking Unit Of Measure object
• Incoterm: this is a lookup to an Incoterms object

So what do we do in this case? We create exports for all these 5 objects, put the results into separate sheets and use VLOOKUP formulas to query the data. For instance, for the vendors, a new tab would look like this:

The VLOOKUP formula for this: =VLOOKUP(C2;Vendors!A:C;3;FALSE)

• We are lookup up the value for whatever is in cell C2 (‘BX481’)
• We lookup in the Vendors sheet, columns A:C. The first column is used for finding the correct entry.
• We want to return the 3rd column (which is C), that contains the ID
• The last parameter is set to FALSE, to force an exact match

Doing this for all 6 lookups will end up in the following quite horrible Excel, I have marked all VLOOKUP formulas in yellow:

Now you can finally export this Excel to CSV and run the import. But I think you can agree that doing this on a daily basis when new prices from suppliers come in … it’s a nightmare.

NB: SmartUpload can perform automated lookups, without the need for manual export-vlookup work.

Pitfall 4: Updating records without an ID

Similar to the previous point: if you want to update a record in Salesforce, all existing tools limit you on the field to use as a primary key:

• ID
• Any field with the ‘External ID’ set.

Now sometimes these two don’t cut it. For instance if you have an Excel file with account names: you cannot update Accounts based on their name. So you would have to:

• Either do an export of all accounts, use VLOOKUPs again in Excel to find the matching IDs
• Or: add an extra custom field to the Account object, with the ExternalID flag set.

Neither of these two solutions is very elegant.

NB: SmartUpload can perform updates using any field as unique ID, without any changes to your Salesforce Org.

Check out an amazing Salesforce Tutorial video here: How to Get Started with Data Prep | Salesforce

Pitfall 5: Upserts

The next challenge is how to perform updates and inserts in one go, the so-called ‘upsert’. Building on top of the previous pitfalls, when you have an Excel file where some rows contain new records but others map to existing records, stuff gets really interesting:

• Depending on your object, some fields may be mandatory for inserting new records, but not mandatory when updating specific fields. This means you probably need to export a lot of data first.
• How does Salesforce know which records are existing and which ones are new? This goes back to the previous point: you cannot match on other fields than ‘ExternalID’ and ‘ID’ fields.

Let's re-consider the Excel file we discussed at the 3rd pitfall: the huge Supplier Catalogue import. It’s a very common business scenario that a new pricelist from a vendor contains both updated prices (which should be updated in the existing entries) as well as new products that should generate new records.

But it might be as simple as a list of accounts, where some of them already exist in Salesforce and others do not, and you want to sync it.

NB: SmartUpload can process upserts using a combination of fields to determine whether an entry is new or existing.

Conclusion

Data loading in Salesforce can be a very tricky business, but with these pitfalls in mind, you can get a lot done using free tools.

However, if you need more control, remove tedious manual steps, or want to delegate work to business end-users, consider using a more intelligent tool like SmartUpload. You can use it for free on https://free.smartupload.net/.

Want to receive updates? Follow us on our LinkedIn page.