Data is an integral part of an everyday business and the effectiveness of its management determines whether the business will be a success or not. Data import and export in Salesforce are the most important activities in its management and we will show how to do it like a pro, regardless of its amount.
In this guide, we’ll break the most important phases of the data import procedure into steps like:
- How to select the right Salesforce data import tools
- How to define the nature of your data
- How to prepare your data for migration
- How to set up Salesforce data import
- How to check data import status.
And topped it with a cherry on the cake: our Salesforce data import best practices. So, let’s get it started.
Salesforce Import Data Best Practices: How to Use Data Import Wizard
Whether you are importing your data from Outlook, or various sources, it is best to follow some recommended steps to ensure that your data transfer is seamless, accurate, and (virtually) painless.
1. Decide on the Data
Are you just starting with Accounts & Contacts, or do you also want to import Properties and Lease Comps? Gaining an understanding of how much data you will bring over, helps establish an expectation of the magnitude of effort involved. Some people like to start with the basics, others want to see all of their information for it to tie together nicely.
2. Perform Data Cleanup
Perhaps one of the most subjective exercises you will encounter. Study your data and determine what really needs to be transferred over, and what does not serve you any purpose. This process also includes aggregating all of your data onto one master spreadsheet (see more on data import templates below).
3. Carry Out Data Formatting
This is where most of the heavy lifting takes place. Chances are your data exists in multiple places and the fields are called different things depending on where that data came from. For the import process to be correct, there needs to be an efficient way to ensure the data you have in spreadsheets, has a corresponding place in Salesforce.
The best way to ensure that is to use Data Import Templates. These templates provide you with a guideline on how to move your data over. (Example: your spreadsheet may have a column for the Contact called “Street Address” – but in Salesforce it’s called “Mailing Street Address”) The more “apples-to-apples” the data fields are, the more likely the import process will be quick and simple.
If you have customized fields or columns of data you want to be brought over but you don’t see them in Salesforce, you don’t need to omit this data if it is meaningful to you. You will need to add these new fields in Salesforce before you begin the import process.
Other formatting tips include removing commas from numerical values and ensuring required fields in Salesforce are not blank on your spreadsheet (Examples: Property names, Contact Last Names, Lease Units). Moreover, you need to make sure if you are connecting records and that the match is exact.
For example, if you are first importing an Account called “AlphaGraphics”, and you want to import a lease, where the Tenant is AlphaGraphics, it must be spelled the same way on the lease import template.
Another important point in data formatting is data deduplication. But you can easily solve it by running a duplicate check in Excel. To do it you need:
1. Highlight the column you want to check for duplicates.
2. Click on the Home tab, select “Conditional Formatting”> Highlight Cell Rules > Duplicate Values > “OK”.
Now, you’ll be able to see highlighted duplicate values.
4. Review Your Data
For the most part, the prior steps can be accomplished by you with little guidance from Salesforce consultants. However, once the data formatting process is complete, you may also reach out to CRM consultants to review how the data looks and determine if it’s ready for import.
Besides these hints, you may also read the answers to the most popular Salesforce Import Data FAQs https://help.salesforce.com/articleView?id=sf.faq_import_general.htm&type=5
How to Import Data in Salesforce in 5 Steps
To avoid all the mishaps related to data shift and ensure its smooth infusion into the system, we advise dividing this procedure into 5 stages:
Step #1 — Determine Which Tools to Use: Salesforce Data Import Wizard vs Data Loader
Salesforce has 2 main tools for data import:
1. User-friendly – Data Import Wizard
Accessible through the Setup menu, it lets you import all kinds of data (Contacts, Accounts, and your Commercial Real Estate data such as Properties, Leases, Listings & Deals).
With Data Import Wizard you can specify the configuration criteria, data origin, and field mappings that display the field names in your import file with the field names in Salesforce.
And it is a fairly straightforward process once the rules of data importation are followed and we’ll tell you how to launch it a bit later. But, despite its obvious benefits, the main drawback is that Salesforce Data Import Wizard’s limitations are up to 50,000 records of import at a time.
2. Advanced – Data Loader
This requires a slightly more involved and hands-on approach and should be used to import over 50K records at a time (to be more specific, up to up to five million records at a time). Another big advantage of this tool is that you can import any data type, either from files or a database connection, and you can run it either through the user interface or the command line.
Also, Data Loader enables Salesforce automated data import via API calls and scheduling regular data loads that can be adjusted via Salesforce configuration or customization. So, if you are comfortable with the ‘industrial strength’ of Salesforce data import tools, this is for you.
*Note. With both tools, the number of records you can load depends on your permissions, the type of data you’re going to import, and the total data storage limits for your Salesforce org. The type of objects you can load depends on your Salesforce edition. Moreover, if you need to import over 5 million records, you should look for ready-to-install mass data loading solutions on AppExchange like Jetterbit, dataloader.io, etc., or custom Salesforce app development services.
Don't forget to check out: Salesforce Dataloading — Common Pitfalls
Step #2 — Determine the Nature of Your Data
No matter what source it is coming from, all data must be divided into separate categories, and data MUST be imported in this order.
- Accounts & Contacts ( the first import)
- Properties (the second import)
- Listings, Deals, Leases, and Sales can all be imported after Properties in no specific order
*Important Tip: You May Not Realize It, But Your Data May Be Mixed and Matched.
For example, if you have a Property spreadsheet and it has the price for sale and listing date, those are two separate imports. One is for the Property itself (its address, owner or landlord, etc.) Another is for the listing itself, which discusses the listing price, listing expiration, etc.
This is because the property could be listed for sale multiple times, and the transactional element is tracked through the Listing, not the Property. The same applies to a Lease Listing.
Similarly, if you have an Account (or company) spreadsheet, and there is a field called “Lease Expiration Date” – that data belongs on a Lease record, not on an Account record. Why? The Account might have multiple leases, and using a field to de-mark that on the Account does not take into consideration which Property the lease is in, or any of the rich-lease data you may have.
Step #3 — Prepare Your Data For Import to Salesforce
It’s a good practice to review your import file for data accuracy and consistency and to take necessary actions before moving your data into the system.
If you decide to opt for a DIY approach in preparing your data for transferring and to use your own spreadsheet, it’s wise to conduct an audit of the columns in your spreadsheet and the fields already available within Salesforce.
To see a list of Salesforce data fields and types, click on Setup, and in the Quick Find type Objects and Fields, then go to Object Manager, click the object (i.e. Property) whose fields you’re interested in. There will be a section called Fields & Relationships. This will display every field available on the record and the kind of data it supports (number, text, URL, phone, lookup, etc…)
If there are additional fields you want to include in your import, the appropriate Salesforce fields must be added first. (See instructions on how to add fields to a record)
Scrub the data clean – that means remove duplicates, check spelling, enforce naming conventions and make sure no required fields are missing. How do you know if a field is required? Go into Salesforce and take a look at the record in question. If it has a red asterisk, it is required. (And unfortunately, there is no way to un-require it).
*Important Tip: Always save your data as a CSV file to import data from Excel to Salesforce!
Step #4 — Set the Data Import Wizard
- Click on the gear icon on the top right of your screen, and click Setup
2. On the left-hand side, in the Quick Find box, type Data Import Wizard
3. Scroll down and click Launch Wizard!
4. Choose the data that you want to import.
- To import Accounts, Contacts, Leads, Solutions, Person Accounts, or Campaign Members click the Standard Objects tab. To import Commercial Real Estate data or other custom objects, click on the Custom Objects tab.
- Determine whether you want to add new records to Salesforce, update existing records, or add and update records simultaneously.
- Define matching and other criteria. Hover over the question marks for more information about each option.
- In the middle console, you might notice a blue-shaded area. This is where you would work on some matching. This is especially important if the import file you have is relying on relationships in other areas of the system. For example, if you are importing Properties and the spreadsheet has Owner/Landlord Contact data, you will need to state that this field on the Property must look to the Contacts for matching data. It also presumes that the Contact has already been imported or manually entered. This is why it’s important to ensure spelling and punctuation are exact.
Here is a screenshot of how you would ensure your Property links the Contacts associated with it:
5. Once you’re done tagging relationships, select your CSV file and click Next.
6. Map your data fields to Salesforce data fields. Mapping, in tech speak, is a way to compare the column headers you have on your spreadsheet, to the fields within Salesforce. In an ideal world, there would be an apples-to-apples interpretation. For example, your spreadsheet for a Contact’s email may say “Email Address”, but in Salesforce the field is called “Email” – you don’t necessarily have to go back and change your spreadsheet, save it, and re-load it.
The Data Import Wizard tries to map as many of your data fields as possible to standard Salesforce data fields. If Salesforce can’t distinguish that Email Address and Email are the same things, you can manually choose it. Any fields you leave as unmapped, however, will not be imported over.
Here is what the final step of mapping looks like. According to this, 5 fields were automatically mapped, and 2 were not mapped. Clicking on the highlighted Map link will ask you which field within Salesforce do you want that column to correlate to.
*Disclaimer! The field types need to be the same. If your spreadsheet has a field called “Taxes Per Year” and it’s filled with alphabetical characters such as $30,000 year 1, $45,000 year 2” and you determine Salesforce coincidentally also has a “Taxes Per Year” field – be wary that, the field in Salesforce can only support strictly currency (So, “30000” is the only field it will accept) This goes back to the apples-to-apples concept. It’s not just about field names, it’s about field types.
- Scan the list of mapped data fields and locate any unmapped fields.
- Click Map to the left of each unmapped field.
- In the Map Your Field dialog box, choose the Salesforce field(s) you want to map to and click Map.
- Click Next & Start Import
! Some Tips about Salesforce Fields
New Values for Picklists and Multi-Select Picklists — if you transfer a picklist value that doesn’t match an existing picklist value:
- For an unrestricted picklist, the Data Import Wizard tool applies the value that’s in the import file.
- For a restricted picklist, the Data Import Wizard tool applies the picklist’s default value.
Check out another amazing blog by Angie here: How to Export Data from Salesforce to Excel and Backup Your Data On-Demand
Multi-Select Picklists — To load multiple values into a multi-select picklist, split the values by a semicolon in your import file.
Checkboxes — To import data into a checkbox field, apply 1 for checked values and 0 for unchecked values.
Date/Time Fields — Make sure that the format of any date/time fields you are importing corresponds to how they are displayed in Salesforce per your locale setting.
Default Values — For picklist, multi-select picklist, and checkbox fields, if you do not match the field in the Import Wizard, the default value for the field, if any, is automatically entered into the new or updated record.
Formula Fields — Formula fields cannot recognize imported data because they are read-only.
Field Validation Rules — Salesforce applies validation rules to records before they are imported, so if records fail validation, they aren’t transferred. If validation rules affect the data you are importing, deactivate the appropriate validation rules before performing a Salesforce data import process.
Step #5 — Check Data Import Status
From Setup, enter “Bulk Data Load Jobs” in the Quick Find box, then select Bulk Data Load Jobs
You will be able to see how many of your records were processed (total number of rows on your import spreadsheet), and how many of those failed.
Failures can occur for many reasons, and you will get an email from Salesforce saying what was the reason.
Common Reasons for Record Import Failures:
- Duplicate records
- Required fields in Salesforce were left blank on the spreadsheet
- When records are linked (like Property, and Owner/Landlord Contact), it could not find a name match
- A Salesforce bug
How to Delete Imported Data in Salesforce If You Made a Mistake:
- Go to Set Up > Developer Console
- In the Query Editor type “select id”, created date from Contact
*ascendix__Lease__C” is the API name for the Lease object. Replace this value with the object you were importing (to obtain a list of all API names, go to Objects and Fields >Object Manager and find the API name)
- Select all of the records you imported based on the date/time you imported them, and click on the “Delete Row” tab. It takes some time, so you will need to refresh the grid every few minutes to make sure all records are deleted.