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
- 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
- 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.
- 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.
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?
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.
- Compare your data to each Salesforce object.
- Create custom fields
- Add picklist values
- Create external IDs
Keep Data Clean in Salesforce and focused on end-users.
Check out another amazing blog by Narendra Sa here: Mobile App for Salesforce - Learn All About it Here