Have you ever heard the term “scrubbing data” and wondered what it meant? Scrubbing data is a review process to remove duplicate records and inconsistent entries prior to importing a file. It's an important step in school database management because the cleaner the data, the less likely it is that inaccuracies will disrupt other workflows. Yes, it takes some time to review files prior to import, but it’s much easier to make changes in bulk in the import file than it is to edit the database online.
Data scrubbing is best with Microsoft Excel. If you're not comfortable with Excel, there are links below to help with some of the scrubbing tasks. You can also use other spreadsheet tools like Google Sheets.
Follow these tips for scrubbing user profile data prior to import:
- Remove duplicate records from the file so that duplicate records are not created in the system.
- Remove redundant ethnicities such as "Caucasian" and "White." Filters in Excel can help you identify redundant entries.
- Remove redundant religions such as "Christian (Non-Denominational)" and "Non-Denominational Christian." Filters in Excel can you identify redundant entries.
- Check to make sure phone numbers are formatted per the school's style guide: 555-555-5555, 555.555.5555, or 5555555555. Controlling how this data is returned in Excel can help standardize phone number values.
- Ensure that the file's column headers match the column headers outlined in Blackbaud's file layout guide. If column headers don't match the correct layouts, then the data won't import. Do not supply additional columns like Role or Username in a General User import file.
- Ensure that all users have a Host ID. Filters in Excel can help you to identify entries without a Host ID.
- Ensure that all users have a unique Host ID. Filtering for duplicate values in the Host ID column is the best way to identify redundant entries.
- Check to make sure that dates are in MM/DD/YYYY format for birthday, hire date, or enrollment date. Excel has a helpful tool to format dates.
- Check to make sure Grad Year is in YYYY format. Filters in Excel can quickly identify if any aren't in the correct four-digit format.
- Ensure that the salutations are for the address record and not the individual people within the household.
- Ensure that the school levels and grade levels match what is in Blackbaud's education management system under Core > Settings > School information > School & Grade levels. Filters in Excel will enable you to identify entries that don't match what is in Core.
- Look for data shifts in your file(s) for both the fields and records. Examples may include:
- Data swapped in two fields.
- Data shifted left or right by one or more positions to the wrong fields; this may occur in all records or in a single record.
- Data shifted up or down in records (e.g. host IDs associated with the wrong users)
Ensuring that data is accurate before import is the first step to having clean data. If you have any questions about data imports, please contact Blackbaud Support!
About the AuthorMore Content by Jacqui Wishart