The Raiser's Edge Cleanup Series: Utilizing Import for Clean Data

Hello again everyone, Sean Patrick here for more tips on cleaning up the addresses in your system. In the last post, I spoke about how to use queries and global changes to standardize your addresses. However, if you are an Excel freak like I am, there is another way to take all of your address data, toss it into Excel, and tweak to your heart's desire.

The thought process can be a little unexpected: we will use the Import feature of The Raiser's Edge to Export data into Excel. The reason is simple: by using what is called an Import Parameter File, the data is exported in such a way that it can be effortlessly imported back into the system.

A caveat: Import is a really really powerful tool. Make absolutely certain you have a valid backup of your database before performing any giant changes in your database as there is no undo button.

First, all of the other bits I spoke of in the previous post (creating a standard, communicating details, remaining flexible to required changes) remain as true as ever. This is simply about how to fix your accumulated issues. Similarly, the global change process and the import process can easily be used in conjunction with one another.

We'll begin by going into Import, specifically, creating a new Constituent Address import. This will let us take every field from an address to solve any recurring issue with our data. On the General tab, select "Create Import File." If you wish to clean up every address in your database, ensure "All Records" appears at the top of the General tab next to the Include button.

Next, in the teal bar next to "Import File", click the ellipses (...) button, and choose a file name and location for the export. Two notes: first, ensure that you type .csv on the end of your file name and second, change from "import files (*.imp)" to "Text Files (*.txt, *.csv, *.prn)". Then, click "Create Now" in the lower right corner.

Part 1 is now done. Next, open up the .csv file in Excel, and it's time to clean up your data. Some recommendations:

• 
  
• Use the Replace feature in Excel to search for Street to replace with St, similar to our global change process in the last post. Continue with every other common address change.
  
• Consider taking this opportunity to standardize the capitalization of your address info. The Excel formula PROPER can make this process simple.
  
• If you, like I, deal with New England addresses regularly, ensure that the Zip Code column is formatted as Zip Code. Otherwise, the pesky leading 0 will get lost leading to thousands of 4 digit zip codes.
  
• You may be curious about the "\n" in the address lines. This is how the system designates a line break in address lines.
  
• If you have wrong States (as an example, we had ,A, /A, 0A, in place of MA), now is a good time to clean this info up.
  

Once you have cleaned up your data, it is time to save your .csv file and re-import back into the Raiser's Edge. Go back into a new Constituent Address import, and this time, choose Update Existing Records. Then, mark the checkbox for Validate Only. Select the newly-cleaned .csv file and go to the File Layout tab. This will show a preview of your information at the bottom of the screen. This is especially useful for checking Zip Codes.

Finally, in the lower right corner, click Validate Now. If there are no errors, return to the general tab, deselect Validate Only, and then click Update Now in the lower right corner. Just like that, all of your addresses will be standardized. From this point on, you can easily do periodic updates using queries or global changes, or routinely run an import file to completely tweak all of your data.

One more thing, even if you will be the Data Dictator at your organization, you still must communicate with your minions. Whenever I was about to make a major change inside my database, I would send an email to all Raiser's Edge users a few days prior outlining exactly what was to be changed, followed by a summary of changes immediately following the change. I also implored anyone to tell me of any unintended consequences, like when I accidentally changed every Broadway in my database to BRdway while cleaning up "road".

Of course, Import is an advanced feature in The Raiser's Edge, with many important options and features. If any of these steps were unclear, or you simply wish to become a true Import Guru, I would suggest the Fundamentals of Importing and Importing Addresses and Phone Records classes. The latter might just come up again in a future blog post. Also, you can access the Database Doctor series in Training Central.

At this point, your address data should be cleaned and ready to go. From this point forward, it will only require occasional maintenance to tweak small errors that arise. I hope this has contained some useful information and I'll see you for another post soon!