Support Center

Scrubbing your data

Last Updated: Apr 03, 2015 07:14AM CDT

​Depending on your current system - you might have to spend a lot of time cleaning up the export of your data. The old saying goes garbage in garbage out not starting with clean data in your new system will drastically limit your success.  


Before you provide us your data it might be helpful to look at our data sheets to see how your data should be formatted.
You should have one sheet with all of the Accounts or Families and another with all of the contacts that are in each family and all relevant data [eg. phone, email, address, etc]. You need to make sure there are no duplicates in each sheet. Not following the format specified and having duplicates will result in your data not importing correctly to Salesforce
If you have duplicates in your data there are a few ways to resolve that.. 
1.    The first step is to highlight all rows in your spreadsheet in excel and use the Remove Duplicates button. This will remove exact duplicates with all the same information in each row. While this might cover some of it, it usually doesn’t cover all of it.
2.    Next you might want to just check for duplicate addresses or email addresses and work from there. To do that, highlight just the one column and then use conditional formatting tool to highlight the duplicates. You can then double check your data and remove those duplicates. 
You can even sort based on cell color to bring them to the top of the list. 
Another item you might need to do is replace certain words with others, for example - you might currently track gender as M and F but in CFS we track them as Male and Female, use the Find and Replace tool to replace all values in a certain column by pressing Control-F.


Keep the data in your columns consistent. Don’t put Emails in phone fields, or Spouse names in Middle Name fields, or URLs in Email fields. Make sure all values are in the proper format. This is especially important for Email fields, Date fields, and Number fields. Salesforce will not allow a record to be imported with a partial date or a badly formed email address. Fix any inconsistencies in the way Organization names are listed. There needs to be one email address per the email address field. If a contact has a second email address you can place it in the Secondary Email address field by creating a new column. The same goes for mailing addresses. If you have a secondary or seasonal address you will need to create a column with Address Type and then Address to input into CFS
Names - For contacts, First Names and Last Names in separate fields. Unless your constituents are pop singers and Brazilian soccer players, they probably have at least two names, and you should load them that way. Last Name are required to be 100% populated. 
Include all required fields. They’re “required” for a reason. You can see which fields are required in our data sheets.


Filtering is a great way to quickly analyze your data. Need to see which columns are and aren’t populated? Filtering is for you. Have users who don’t like to enter the same value, the same way twice? Use a filter to locate the inconsistencies.
You may have used Sorts to handle similar scenarios, but filters give you more control. Using them, you can also avoid having to constantly rearrange the rows of your data, lowering the odds of making a mistake.
What cool things can you do with filters?
Say Goodbye to Empty Columns – A column with no data in it is a column you don’t need to import. I like a lean import file, and that means removing the clutter of blank columns.
Spot a Bad Date or Phone – Filtering is a great way to quickly spot bad or missing dates, which can cause those pesky import errors. It can also clue you in to phone numbers that are inconsistently formatted.
Make Sure It’s the Right List You’re Picking – Picklists in Salesforce are not very forgiving. It may be a small dot, but “Jr” and “Jr.” are not the same suffix. Filters can help you see those data vagaries. Then, a Find and Replace will equal a sparkly clean picklist.
Work the Combinations – Filters are also a great way to check consistency across columns, for example, to check the data integrity of dependent picklists. If I’ve got a foundation type field, I can filter out the blanks in that column to verify all the records have a value of “Foundation” in the account type column. Or, in a donations table, I can check that all of the rows with a credit card type value, have “Credit Card” listed as the payment method.
Use a Smarter Copy/Paste – This is a little more advanced, but I’ll often use filters to pull subsets of data that need to be imported separately such as when I’ve got spouse names in the same row as primary contacts. When you filter a set of columns and copy all, Excel grabs only those rows that meet the filtering criteria. Filtering out blank values for a spouse first name column, I can paste all of my spouse data to a separate tab for data prep.
A word of caution on filters and then trying mass edits to an excel - you want to make sure that you do not make changes to a subset of records that you meant to make to all records. Please double check all of your work.

Contact Us

  • Email Us
  • Congregation Connect support is available via email. Please email and one of our Support Specialists will respond as quickly as possible.
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
Invalid characters found