Save Your Sanity with Ctrl +C

One of the hardest parts about moving data between systems is figuring out how to translate mismatched terms. Let’s look at an example situation that illustrates this challenge. Rosa’s ticketing system tracks ten different types of season ticket holders. In The Raiser’s Edge, she enters those ten season ticket options under five Membership Categories, each with two Subcategories (Single and Couple):

Currently, Rosa manually inserts The Raiser’s Edge fields into her import file for each row. This process is not only a labor-intensive task, but it also puts her data file at risk for data entry errors. One glorious day Rosa discovers ImportOmatic (IOM) and her day is saved! ImportOmatic’s dictionaries translate each of the season ticket options from Rosa’s ticketing system into the proper Raiser’s Edge Program, Category, and Subcategory! If you’re familiar with using dictionaries in IOM, you know that you can manually type in these translations.

Again, manually typing a large number of translations opens the door for data entry errors.

There’s got to be an easier way, right?

Well, there is!!!

Rosa can make lists in Excel, where it’s easier for her to scan through an entire list in a single view. Then, she can copy and paste the entire dictionary into IOM with just a couple of clicks.

The first step is to create her list in Excel with the replacement values in the left column and the values to match on in the right column. Please note this is a one-to-one relationship:

Then Rosa copies all of the values she wants to include in her dictionary. This is fine to do even if she’s already manually typed some of the values into the dictionary. Please note Rosa has left her column headers out of the selection since those are just for her reference.

In her IOM dictionary screen, Rosa right clicks in the “Replacement values” column, and selects “Paste values from Excel”

Ta-da! All of the replacement values and their values to match on are entered for her in their correct locations.

Rosa can repeat this for multiple translations, based on her ten ticket packages. Since she’s planning on adding new packages to her ticketing website next year, Rosa will save this Excel spreadsheet to make updating her dictionaries easy in the future.

Now that Rosa has her dictionaries created for all three Membership fields, she can use Virtual Fields to copy her MaestroTix Package, which is in column D of her data file, three times (once each for Membership Program, Category, Subcategory). She can then apply the appropriate dictionary to each field.

Bonus tip – You can use this same method of copying a dictionary from Excel when using Regular Expressions. Just make sure to include a third column indicating TRUE for Regular Expressions.

Notice that the third column marks the “Use Regular Expressions” checkbox for you.

Do you have any tips you’d like to share on how you’re using dictionaries to save time and prevent mistakes? Make sure to share your advice with other users in our ImportOmatic Forum!