Cleaning your data and preparing your import file can be a time-consuming process. Always remember that the quality of the data in your database impacts on the effectiveness of your entire organisation.
Salesforce, specifically the Data Loader, Data Import Wizard, and the Fundraising Accelerator BDE Staging Object process are not data manipulation or data cleansing tools. This work should be done before you import the data into Salesforce.
You can purchase a specialised data manipulation tool for this, or fortunately, there are some great tools in Excel that you can use to make the process easier and faster as well.
If you’re not using a specialised application, make sure that you are familiar with these Excel tools. There are some great Trailheads that will take you through them if you’re not already familiar with them.
- Sort and Filter- is the easy way on how to locate the duplicates in your data and filter it to isolate a certain group of records.
- Vlookup- is a tool that can match Salesforce user IDs to the names of record owners or to match parent IDs to child records. Make sure you are using the 18 digit Salesforce ID, as the 15 digit ones may have double ups due to Vlookups not being Case Sensitive
- Paste Special…as Values- cells can contain formulas, even though they display like a normal value. These must be converted before they can be imported. This allows you to convert formulas to their values.
- Format Cells- Excel often drops leading zeros in fields like phone numbers and postal codes. The Format cells feature is a way to resolve this.
- Find and Replace- can be used to help clean up data. You can use it to replace one value with another and you can also use it to remove things like empty spaces.
- Concatenate- allows you to combine text from different cells together into one cell. Use it to combine 2 name fields for example.
- Text to Columns- sometimes data such as usernames are in one column but need to be broken into two columns in order to be imported into Salesforce. The “text to column” features separates them quickly.
- Save As .csv- lets you save an Excel file into a comma separated values format, so you can use it to import data into your Salesforce organisation. CSV does not support Excel formulas or formatting so it’s important you understand that when you save a file as csv, all formulas will be converted to values.