Start a new topic

RegEx to replace ANY value in a column OR prefix any value OR append any value

We were working with a client that was importing team names into their Raiser's Edge Event Participants with Import-O-Matic, but they wanted to prefix each team name with the current year (like changing "Bronx Bombers" to "2011 Bronx Bombers"). The client had been manually massaging team name data for years. With Regular Expressions and Import-O-Matic's dictionary translations, we were able to make this change automatically. We just matched on the value "^" (no double quotes) and specified the replacement value of "2011 " (notice the extra space after 2011). Worked like a charm!

image



When investigating issues with client data in-house, I've always had to massage the sample data they give me to replace their Appeal/Campaign/Fund ID's with ones that exist in our test Raiser's Edge database. Now, with Regular Expression capabilities in Import-O-Matic's Dictionary Translations, I've set up a dictionary that will match any value in a column using the expression "^.*" (no double quotes) and replace it with my value. No more data massaging! Here is the dictionary I set up for my test appeal (I actually use this appeal as my "Master ID" so I can ignore their fund and campaign information and IOM will pull it from the defaults on the specified RE appeal record).

image



Hope these are useful for you!

Jeff

I hadn't heard of RegEx before we purchased Import o Matic last month. We have a need for a RegEX to help eliminate bogus emails from our files before we import. (Our current process is to sort the email column in a file with 20,000 records and scan for "decline@", "none@", "declined@"...and delete them.) I've dabbled with trying to figure out which characters to use, but haven't had much luck. We're novices and short on resources. If anyone can offer assistance writing a RegEx to find and eliminate the bogus emails, we would extremely appreciative! - Stacey
To append any value onto the end of a string. All quotes ("") should be removed when you put this into the dictionary.

In this case, I am adding "2011" to the end of a string.
Replacement Values = " 2011"
Values to match on = "$"

Note: There is an extra space before "2011" in the Replacement Values so that "Walk-A-Thon" would become "Walk-A-Thon 2011".

image






Login or Signup to post a comment