Regular Expressions for ImportOmatic
You may be wondering what in the world a Regular Expression is, or perhaps you have heard of them, but have never used a Regular Expression for your data processing. Today I hope to explain what a Regular Expression is, when to use one, and provide some Regular Expression samples.
So let’s start with what is a Regular Expression. Regular Expressions, commonly referred to as RegEx, is a way of doing pattern matching with data. It allows you to match or recognize patterns within a string of text, and then manipulate that text. RegEx is an easy way to transform data that is otherwise not a simple search and replace, but might have considerations or patterns to look for.
For example: You are importing the following address into The Raiser’s Edge:
141 St. Hampton St., Apt B1
Many organizations are striving for consistency within their Raiser’s Edge database and therefore might want their St. to be consistent with the rest of their database and be spelled out as Street. That might seem simple to most, just do a quick search and replace in excel before importing, but in this case that will not work. This address is actually 141 Saint Hampton Street, Apartment B1. How do you do a search and replace, but not override data with inaccurate information. Perhaps more importantly, we want this to take place quickly.
In this example, we really need to look for a pattern. We need to identify where “St.” falls at the end of our address line or at the end, but is followed by an Apt., Suite, or PO Box. We couldn’t rely on just finding any “St.” so it needs to be specific; it needs to be a reliable pattern. If our RegEx is looking for “St.” at the end of the address line, then it will identify the second “St.” in the example above, and change it to the desired “Street”. Your address would then look like this:
141 St. Hampton Street, Apt B1.
Of course we could have other Regular Expressions to change St. → Saint or Apt → Apartment as there are no limited to the amount or complexity of Regular Expressions.
There are countless different scenarios when Regular Expressions would be useful, but in general, anytime you need to look for a pattern within your data, RegEx is the way to go.
So why is this important? Here are my top 5 reasons:
- It is a powerful way to transform your data without any manual intervention
- It is safe and reliable
- You have the ability to use Regular Expressions in ImportOmatic, why not take advantage of it!
- It can increase the accuracy and consistency of your Raiser’s Edge data
- It allows for complex pattern matching when a simple search and replace will not cut it
Before we get into samples, if you are new to ImportOmatic or new to Regular Expressions, let me quickly walk you through how to utilize these within ImportOmatic.
- Go to Dictionaries within Configuration
- Open one of your existing dictionaries
- Select one of the items on the left side, under Replacement Value and then check the box in the bottom that states “Use Regular Expressions”
- Add your Regular Expression to the right side, the Values to Match on screen.
- Save and make sure that dictionary is applied to your profile, and you are ready to go!
Now, let’s get to the good stuff. Several of our staff has shared their Regular Expressions on our forums. Below is a list of some of the ones that have been posted (Please note: we always encourage clients to test these before doing a big import to make sure it is exactly the way you want!):
1. Replace a Street Name with its Standard USPS abbreviation – this link has a great amount of address standardization examples and even includes spreadsheets that you could use to copy and paste into dictionaries.
2. Add a leading zero to addresses with only 8 characters & add a dash for a zip +4 – this link provides you the regular expression for adding a leading zero, if they have been stripped off and are now only 8 characters. Additionally, it will split your zip code into 5 characters and 4 characters, adding a – in between the two. From Nic Bourne, one of Omatic’s development team members.
3. Convert a credit card number formatted as V-xxxx to credit card type of Visa – if you are importing credit card information and need to appropriate match the credit card type with Raiser’s Edge, this regular expression will show you how.
4. Convert a date format of YYYYMM to MM/DD/YYYY – need to convert your date format, this post will use the example with 01 as the date to show you how. From Nic Bourne, one of Omatic’s development team members.
5. Add a leading zero to zip codes and convert St → Street (in the example we used above) – Need to add a leading zero to a zip code or convert St → Street when it comes at the end of your address line, similar to the example we walked through above. This post will show you how.
6. Convert a MM/DD/YYYY date to Class of YY – see how Nic transforms a full date to a class of year. From Nic Bourne, one of Omatic’s development team members.
7. Remove the time stamp from a Date/Time String – if your date/time string looks like MM/DD/YYYY HH:MM:SS and you want to remove the time stamp (HH:MM:SS), this post will provide you the RegEx to do just that.
8. Need to replace .xx with : or need to prefix a value with $ – this post, which was initially sent from a blog, shows you how.
9. String everything but the last 5 characters in a string – need to get rid of everything but the very last few characters? This post will show you how.
10. Add a period to a middle initial – ever get those that just come as “R” and you need it to have a period, this RegEx will do just that.
11. Add hyphens to a numeric string – have a string of numbers 123456 and want to add hyphens so it becomes 12-34-56, here is how. From Allison Boulduc, one of Omatic’s professional service team members.
12. Append a value at the end of a string – Tiffany shows us how to add a value at the end of a string.
13. Replace anything that is not blank with a certain value – this post shows you how to replace all values, other than blank, with something different.
14. Replace anything with any other desired value – need to change a value to something else, here is how.
We hope these help, don’t forget to check out the Omatic Knowledge Base, it is a great place for other resources.
