Have you ever had a spreadsheet of constituents to add to Raiser’s Edge, but you aren’t sure who is already in the database and who isn’t? Importing is a fairly straightforward process if they’re all new constituents, but can be a bit trickier if some of your constituents already have records and some don’t.
The best way to update existing constituents through import is by using their Constituent ID or Import ID to ensure the updates go onto the right records. However, that’s not always possible, so you may need to update them using only the information you have on hand. Let’s say I recently had an event, and have information for a group of attendees in Excel, but I’m not sure who is already in the database and who isn’t.
As with any import, the first step is to format the spreadsheet for import. This means including the required fields (Constituent ID and Key Indicator), as well as splitting those fields that need to be entered in their own cells (Name into First and Last Name). I’ve left Constituent ID blank, since RE will autofill it for new constituents.
Fortunately, import gives us the option to search for duplicates when importing, so I can catch any existing constituents before importing them as duplicates. By marking Use duplicate criteria for new records, I can have Raiser’s Edge alert me when I’m adding a likely duplicate to the database.
Once that checkbox is marked, the import will pull out as exceptions any records that are probable duplicates. Using this report, we can either manually compare the individual constituent codes, or use formulas in Excel to retrieve the numbers and paste them into a Raiser’s Edge query.
The Process:
· On the General tab of the import, mark the options to Import New Records, Validate Data Only, and Use duplicate criteria for new records. This feature looks at several factors to determine which records are duplicates. The more factors that match, the more likely Raiser’s Edge will recognize them as duplicates (Full duplicate criteria are laid out in https://kb.blackbaud.com/articles/Article/64630).
· Ensure your fields are mapped correctly on the Fields tab, and on the Summary tab, mark the option to Create control report.
· When you Validate, Raiser’s Edge will create a control report that contains an exception for every new constituent who is a likely duplicate already in the database. This will NOT actually import any records at this time.
· If there are only a few, we can search for the constituents by the listed constituent codes and evaluate whether they are duplicates or not.
· If there are many duplicates, Excel formulas can help isolate only the Constituent ID once we export the report. The end goal is a column of cells with only the Constituent IDs in it and nothing else.
A formula and Excel macros to remove non-numeric characters can be found here: http://excelribbon.tips.net/T011750_Getting_Rid_of_Everything_Except_Numbers.html. Records with multiple possible matches will need to be manually checked and edited in the spreadsheet.
Note: This process will vary based on the version of Microsoft Office being used. We provide links to third-party websites in an effort to help resolve problems. We are not responsible for the information on third-party websites, and we cannot assist with implementing the resolution from these websites.
· Once you’ve got a column with only the Constituent IDs in it, you can copy and paste them into a Raiser’s Edge query using the steps in https://kb.blackbaud.com/articles/Article/41666.
· Then, add to the Output of the query: Constituent Name, Import ID, Phone Number, Address, and anything else that will help verify whether it’s a duplicate or not.
· Export the query results into Excel, and sort the query results in the same order as the Import spreadsheet to compare the possible duplicates.
· Copy and paste the Constituent or Import IDs of duplicates into your original Import spreadsheet. This ensures that Raiser’s Edge knows which records are already in Raiser’s Edge and which aren’t.
· Now, change your Import to an update, with the option to Import records not found as new records marked.
· With the Constituent or Import IDs filled in, you can update constituents who are already in the database and import new ones all at once.
Note: Any associated types of records (such as relationships, existing addresses, phone numbers, etc.) will NOT necessarily be updated this way. You may need to export the Import IDs of these separately in order to update existing numbers and addresses.
For more tips and tricks on becoming an Import pro, check out the resources below:
How to import - The Basics of Importing
ImportGuide(PDF)
ImportTutorial(PDF)
How to import new phone numbers/email addresses and update existing ones on constituent records (includes sample data file)
We also have two 3-hour instructor-led trainings on Importing:
Raiser’s Edge: Import 1 - Do you have information in a spreadsheet format that you want to add to Raiser’s Edge? In this three-hour online instructor-led event, learn how to import constituents and gifts in Raiser’s Edge. Through instructor-led scenarios, class discussions, and practices in the sample database, you will learn how to use Microsoft® Excel® tools to properly format your data file.
Raiser’s Edge: Import 2 - Do you have additional contact or relationship information that you want to add to Raiser’s Edge? In this three-hour instructor-led online event, learn how to import multiple addresses and relationships in Raiser’s Edge. Through instructor-led scenarios, class discussion, and hands-on practice in the sample database, you will learn how to use Microsoft® Excel® tools to properly format your data file.
