by James Campbell 
This blog post is relevant to anyone importing data into Salesforce from those uploading a list of new leads, to those carrying out a full system migration. It’s not a ‘how to guide’, more of discussion around things that often catch people out and how to avoid them.
Be organised and use file naming conventions. When preparing your import files you often end up with several versions of the same file. This can be confusing unless you use a naming convention that allows you to easily identify each version. If you realise that you have made a mistake during the file preparation process, systematic versioning makes it very easy to identify at what point the error was made and it prevents you from having to start all over again.
Should I clean the data before or after import? I hear this question asked on almost every implementation project and my answer is always ‘both’. It goes without saying that the data must be in a state that Salesforce will accept i.e. the correct format and field length (see next 2 points). However we advise that certain cleansing activities such as merging duplicate records and appending new values should take place once the data is in Salesforce. The reason for this is that carrying out these activities during a data migration process often leads to increased system downtime. It’s also much easier to maintain database relationships once the data has been imported and there are great tools on the appexchange to help you clean the data directly within Salesforce.
Tip: Data cleansing should be an ongoing process and we find that organisations with more than 10,000 contact/lead records benefit from implementing an application like ‘Demand Tools’ or ‘Ringlead’ to clean and maintain their data.
Ensure the values in your import file are the appropriate format/length for the Salesforce fields. This can be done relatively easily prior to importation using excel formulas and it helps to minimise the number of records that will fail to import. The standard IF, LEN and OR functions will suit the vast majority of field types however this article describes how you can add a macro that validates email addresses.
Ensure values being imported into picklists match the defined picklist values in Salesforce. You are able to import a value into a picklist field even if it is not in the defined list however we strongly recommend against doing this. It confuses users when they see a non-standard value in the field and it can also cause problems when this field is used in reports. If the values between your old system and Salesforce are different, you can use a ‘find & replace’ or a vlookup function to replace old system’s values with the new Salesforce picklist values prior to importation.
Tip: If you have State & Country picklists enabled in your org you must ensure that your import values match the defined values otherwise the record will not import.
Be careful when using Salesforce Record IDs in excel. All Salesforce records have a Long ID (18 characters) which IS NOT case sensitive and a Short ID (15 characters) which IS case sensitive. If you are using the Salesforce ID to match records in excel (e.g. vlookup, ‘Find & Replace’, ‘Remove Duplicates’) then you must use the Long ID field, otherwise excel will find incorrect matches.
Tip: The Record ID that you see in the normal user interface is the Short ID however you can export the Long IDs using any data loading tool. Our preferred tool is Jitterbit Data Loader.
Import historical record IDs. If you are migrating the data from another system, it makes life a lot easier if you import that system’s record IDs into Salesforce. In addition to loading the ID of the record you are creating, you should also import related IDs e.g. load old system’s Account ID (as well as Contact ID) onto the Salesforce contact record. This makes it much easier to compare information between systems and append information at a later date.
Tip: When creating a custom field to store another system’s record ID, make sure to select the ‘External ID’ checkbox as this allows the field to be used to match records when importing/updating records.
Load more information onto each record than you think you will need. You may have information in your old CRM that you don’t think you require in Salesforce. If you are in any doubt, we recommend loading this data into fields that will not be displayed to users. The reason for this is that it usually takes very little extra time to import, and saves a lot of time in the future if you change your mind.
Tip: Very few Salesforce customers reach their custom field limit per database object so only delete the field when you are 100% certain that you no longer require this information.
Watch out for Validation Rules. Please be aware that any records that do not meet your validation rules will not import. Some people like to deactivate certain validation rules when importing data. Others do not because they feel that data that doesn’t meet the criteria then it should not be imported. You should make every attempt to clean the data to meet all validation rules prior to import however the decision on whether you should import the records that don’t meet the criteria is entirely your decision.
Tip: If you import data that does not meet a deactivated validation rule and this rule is then activated, you will not be able to modify these records until they are updated to meet the validation rule.
Watch out for Workflow Rules / Apex Triggers. You need to identify each Workflow Rule / Apex Trigger that might fire when importing/updating records and make a decision about each one individually. Let’s take the example of importing a list of opportunities into Salesforce from an old CRM. A rule that you may choose to leave active is one which sets a ‘Customer’ checkbox to TRUE on the related Account record when an Opportunity for that Account is ‘Closed Won’. You would keep this active because as you import all the historical Opportunities, the relevant Accounts will be marked as a ‘Customer’ (as they should be). An example of a rule that you may wish to deactivate is one which sends an email to certain individuals every time an Opportunity is ‘Won’ because if you don’t, they would receive hundreds (maybe thousands) of irelavant emails when the data is imported.
I hope this blog post has provided you with some useful takeaways. If you require further information on anything to do with importing data into Salesforce, please feel free to contact us and we would be more than happy to help.