Data imports in Salesforce are one of those things that Salesforce Admins perform frequently – sometimes daily in certain situations. The process itself refers to manipulating data points (such as inserting or updating records and field values on Standard or Custom Salesforce objects) using a tool that allows multiple records to be updated from a file in one operation, rather than having to manually create or edit each of them.
As you likely know by now, everything can work perfectly from start to finish, but what happens when some (or even all) records in a file return Salesforce import errors that prevent the success of the import? In this post, I will share my ‘favorite’ Salesforce import errors and how to successfully fix them.
When Are Imports Needed?
There are a few situations when imports are definitely the way to go, either to remove the need of having to manually input data, or to ensure that the information in Salesforce is up to date. Below are the main situations when, in my opinion, imports will be handy:
- Historical data mapping following a release
- One-time data migration from a legacy system to Salesforce
- Insert new records in either production or a sandbox environment
- Update or even delete existing records
You might also wonder what import tool to use in order to successfully perform this operation, and the reality is that you may choose the import tool which works best for the use case at hand. For example, you should use Data Loader to update over 50K records at a time, but you might as well use Data Import Wizard if you would only like to apply the one of the active Lead Assignment Rules on a couple hundred Leads.
Salesforce Import Errors
When it comes to data imports involving files with a large number of records, one tiny error can become quite frustrating. However, in reality, it’s common to either forget a tiny detail or simply not account for an existing process being triggered at the time of import.
Let’s first establish how you would get hold of the Salesforce import errors, depending on the tool you use for the data import:
- When using Data Loader, you will have a predefined folder where both the success and error spreadsheets will be stored after every import. Instead of navigating to the folder, as soon as the import is completed, you can go ahead and check the error reason directly within the tool (given you didn’t import a large number of records) or open in an external program (Excel).
- For the Data Import Wizard, you will receive a report via email, containing an attachment with the “Errors” for the first one thousand records. For more details, you will have to click the processed job information link from the email, which will take you to the job’s result in Salesforce.
- In Workbench, you will be able to see the error message(s) row by row within the tool, but you also have the option of downloading the report.
- If you opt to use another third-party import tool, this might vary, but in most cases the report will also conclude with an Excel-like file where you can see the error from the duplicate rule. The below example is the format available from ProvenWorks’s SimpleImport tool in case an error occurs.
We recently investigated SimpleImport’s functionality and really enjoyed it: Importing Salesforce Records Just Got Simpler [In-Depth Overview].
Ownership change requests are very common, especially when organizational changes are happening, such as a Business Development Representative being promoted. And now all of the Open Leads they owned should be transferred to a newly hired colleague. Easy right? The errors below might prove you wrong:
- TRANSFER_REQUIRES_READ:The new owner must have read permission
- INSUFFICIENT_ACCESS_ON_CROSS_REFERENCE_ENTITY:insufficient access rights on cross-reference id: Your record’s Id
In the situation of record transfers, both errors would prompt us to check the new owner’s permissions for the object whose records are being updated, as well as the Record Type assignment and record access, for example. In the situation at hand, the new owner did not yet have access to the Lead object, so providing object and field access resolved the error.
For the INSUFFICIENT_ACCESS error specifically, be sure to check out this knowledge article, which contains a few more possible scenarios this error might appear in – assuming the permissions were not the cause.
Mandatory Fields and Validation Rules
In a more complex org with multiple processes, objects, and Salesforce Admins, the two errors below are more likely to occur when performing data imports. This is simply because not everyone will always remember every single required field or validation rule – truth be told, it might just slip someone’s mind while validating the spreadsheet columns.
- FIELD_CUSTOM_VALIDATION_EXCEPTION: Your validation rule’s error message – this is why it is important to make it a habit that the validation rule’s error message is short and specific, to help easily identify what field or other attribute it refers to.
- REQUIRED_FIELD_MISSING:Required fields are missing: [Fieldname__c]:FieldName__c –
The fix is very simple, as long as you pay close attention to the validation error message, as well as the specific required field – keep in mind that in the error file, there might be different required fields missing and multiple validations being triggered. Make sure you not only check the first occurrence of the error, but also each individual one to ensure that all errors are fixed.
In the beginning, I found this error particularly confusing, simply because it doesn’t specifically tell you the reason behind the import for the particular records failing. As you get more experience with the platform and processes within your Salesforce org, you will be able to solutionize this one.
- UNABLE_TO_LOCK_ROW: unable to obtain exclusive access to this record or (x number) records: List of Id examples
Normally, this error doesn’t last long, as the behavior only represents a “block” Salesforce is placing on the record because a different process is making changes on it. Practically, the import might be causing other processes to run – if this didn’t exist, data inconsistencies would be bound to occur.
In most cases, retrying the same operation should be sufficient, especially with a smaller batch size, but there are situations where, for example, when using Bulk API, records related to the same parent record will end up being processed in different batches, with one of them failing.
For example, one situation where locking errors might happen is inserting Campaign Members into Salesforce following a large marketing event. In this situation, the lock is put on the related record, be it Lead or Contact.
One resource I keep going back to, especially when I know my import is affecting multiple objects as well as sharing, is the Record Locking Cheatsheet – I recommend you bookmark this for the next time this error pops up in your results. It’s also worth checking the related resources for a better understanding of this situation.
Even after a few years of performing imports into Salesforce orgs, “bad value for restricted picklist” remains my all time favorite to see when opening the error file.
- INVALID_OR_NULL_FOR_RESTRICTED_PICKLIST:Field Label: bad value for restricted picklist field: Value:Field_API_Name__c –
The most straightforward way to fix the error is, of course, to ensure the value from the spreadsheet and error message exists as an active value in the picklist field, and that spelling is correct. If this is not it, there are few more things you can check:
- If the object whose records are being recorded has Record Types enabled, make sure the picklist value is also added on the Record Type.
- The picklist being updated might be a dependent picklist, and the value from the controlling field does not have this specific value associated with the dependent one.
- If (and only if) you feel comfortable with the impact of not having the picklist values restricted to the predefined list, you can uncheck the below box when navigating to the Object’s picklist field in Setup. For example, while users will not be able to input a value outside the list through the user interface, an admin can mistakenly import a wrong value through the API, which will be added to the list of inactive values.
- Another behavior I have encountered on occasion is the value being copied from a different spreadsheet or document, and pasted into the spreadsheet to be imported into Salesforce. Even if the spelling was correct and the value met all the above criteria, it still returned the “bad value for restricted picklist” error. To avoid this behavior, what’s worked for me is the good old Ctrl + Shift + V (for Windows) or Command + Shift + V (for MacOS) – paste values will simply paste the values without any formatting or formulas from the source.
Date Fields Update
And finally, my least favorite import error, the coveted “Error converting value to correct data type: Failed to parse date”. This one is definitely a case of saving the best for last – while it can be very frustrating to resolve when working with formatting on large volumes of data, date and date/time fields usually prove essential to reporting, even more so if we’re discussing a data migration effort.
If you have worked with both the dd/mm/yyyy and mm/dd/yyyy formats, regardless of whether it included the time or not, you are most likely familiar with the unfortunate situation of importing one format instead of the other – that’s a fun one to clean up.
The easiest way to avoid this error is making sure you check the following before starting the import:
- Date and Date/Time fields are properly formatted in either Excel or Google Sheets before saving your final .csv file – there were situations where I used the custom formatting for this. Be sure to check out the dedicated help page for detailed instructions.
- In Data Loader (this is the tool I always use for date imports), make sure you take a look at the Settings – more specifically, know the timezone you’re in. If you are planning to import a European format that starts with the day, check the “Use European date format (dd/mm/yyyy)” box.
Don’t forget to take a look over all of the accepted date formats, as well as the supported data types for Data Loader here.
Finally, regardless of the reason for performing a data import in your Salesforce instance, or the tool you use, it’s important to pay the same attention to detail as you would with any other Salesforce-related process – this will ensure a fast and error-free import.
As always, Salesforce also comes to the rescue with a very thorough pre-import checklist, so you can double check any specific Salesforce import errors before getting started.
While this post covered just a few of the most common Salesforce import errors, feel free to comment below with some of your own examples… and how you fixed them!