CSV Files in Salesforce | Data Import | How-to Guide

Data Import: Clean and Prepare Your Data Using Excel, Data Import: Clean Up Your Import File

Learning Objectives

Data Import: Clean and Prepare Your Data Using Excel
Data Import: Clean Up Your Import File

Data Import: Clean and Prepare Your Data Using Excel

Excel functions can help clean up data and prepare import files to save time and prevent manual data edit errors.

Sort and Filter

Locate Duplicates>> use Sort to clean up duplicates
Isolate certain groups of data:

Filter to enforce naming conventions
Apply Phone or address formatting
Find blank and identify incomplete records.

Don't forget to check out: Object Records Creation Using CSV File | Salesforce Guide

Vlookup

Match Salesforce user IDs to the names of record owners
Match parent record IDs to child records.

Import new accounts only with email and assign them to the correct user who owns them:

Use Data Loader export usernames and IDs>> Use Vlookup Match each email in the import file with the export of user ID. lookup up the email>>Click the function button >> find Vlookup >> click OK>>Click Lookup Value( the value that matches with the lookup table)>>Click Table Array(the location of the lookup table)>> Click Column Index Number (the column of information we want to be returned, once the lookup value is matched)>> Click Range lookup (asking if we want an exact match) >>save.

Paste as Special Values

Cells can contain formulas that are dependent on the cells they reference. Convert the formulas to values, so we can delete columns we no longer need. Select the formulas>>Right click and choose copy>>Right click again and choose Paste Special… as Values.

Format Cells Format zip codes, social security numbers, and phone numbers that begin with a zero.

Find + Replace

To replace one value with another within a selected area.

To remove characters such as dashes or empty spaces.

Concatenate

Combine area code and phone number.
Combine 2 address fields into one.
Combine a user’s name with an email suffix.

Text to Columns Separates 1 cell into 2 cells.

Save as CSV

Prepare files for import into Salesforce. Save as a comma-separated values format to import data into Salesforce org.
Converts all formulas to values.

Reference Link

Use Data Import Wizard

Data Import: Clean Up Your Import File

3 main steps to prepare for importing data.

Clean up data
Prepare the import file itself

Configuring in Salesforce.

So why take time to clean up data?

Accuracy
Usability

Users depend on clean data. Dirty data makes your data untrustworthy.

What should you clean up?

Resolve duplicates and delete the information you don’t need.
Correct spelling and punctuation errors.
Develop and enforce naming conventions.
Fill in incomplete records.

Prepare import file with cleaned data on Data Import Wizard and the Data Loader.

Match field names.
Importing contacts, opportunities, or other objects that have parent records, you’ll need to add a column for the parent record.

Prepare Salesforce after preparing the import file.