The Raiser's Edge Data Cleanup Series: Linking Import Data to Existing Constituents
Published
Hello everyone, and welcome to another installment in our Data Cleanup Series. Today we’ll discuss how to make importing in the Raiser’s Edge usable in far more circumstances.
Every import into Raiser’s Edge requires some unique identification information. For example, you may need to import a new gift, address or attribute to an existing constituent record. The import will need a unique identifier for the constituent being updated, most often the Import ID. The problem one most often encounters when importing data is locating the unique identifier for every record. How can we speed up this linking process?
To do so, we’re going to use my favorite Excel feature: Vlookup. For those of you unfamiliar with this greatest of all tools, take a look back at my post on Almighty Vlookup for either an intro or a refresher.
We’ll begin by reviewing our data, to see what we can use to uniquely identify our constituents. Then we’ll export some pertinent information out of Raiser’s Edge, and use Vlookup along with other formulas inside Excel to stitch our info together. Let’s get started.
Reviewing our Data
The first step is to check what info you have within your import data. Even if we don’t yet have the Import ID, you should have something to uniquely identify your constituents. Most often, you should have at least a first and last name, often title, middle name, and address information as well.
For this example, we’re importing new committee assignments, which exist as an attribute inside Raiser’s Edge:
Looking at our information, we will use First Name and Last Field to uniquely identify our constituents.
Creating our Export
To perform our import, we’ll need an Import ID or a Constituent ID for each of our constituents receiving a new attribute. Instead of opening each record, going to file->properties, and copying the Import ID, I’d rather export a giant list of every constituent in the database, and make the computer do the hard work.
We have a first name and last name in our original data. Now we’ll run a constituent export for All Records, taking the first name, last name, and constituent Import ID.
To recap, in our committee list, we have First Name and Last Name, but we need Import ID. As our newly-exported data for all constituents has First Name, Last Name, and Import ID, a simple vlookup provides the necessary Import ID information.
Creating our Keys
Now it’s time to start manufacturing our unique identifiers, known as a key. First we need to think about the necessary format for our data. vlookup will look for data in only a single column. As such, we’ll need to have first and last name (your unique identifiers) all in a single column. We’ll use a simple excel function called Concatenate to combine all of our required fields together:
Concatenate simply combines multiple fields together, all separated by commas in the formula. But, you can see I put in field B2, then “,”, then C2. If I had just typed =concatenate(B2,C2), our resulting field would look like “BenjimanConnor.” By inserting a “,”, instead we get the following data:
I then repeat the process for our second table containing the Import ID, so there are keys in both of our required tables.
The location of the key column matters. It must be in the leftmost column in both our imported data, and the table we just exported from Raiser’s Edge. Secondly, after you manufacture the key for each table, I highly recommend sorting (ascending) by the key for each table. This will greatly help the vlookup process.
The Vlookup
Now we have our two tables of information with our unique key in the leftmost columns. From here, we have a pretty standard vlookup, so you might want to check the aforementioned Vlookup blog post. Copy the exported table into the second sheet of the imported excel file. Next, highlight this second table and right click to name the range (I chose “records”).
Then it’s formula time! Our first formula will look as follows: =VLOOKUP(A2, records,4,FALSE).
From here, all that’s left is to finish formatting your import file, and push it into the system.
Every import into Raiser’s Edge requires some unique identification information. For example, you may need to import a new gift, address or attribute to an existing constituent record. The import will need a unique identifier for the constituent being updated, most often the Import ID. The problem one most often encounters when importing data is locating the unique identifier for every record. How can we speed up this linking process?
To do so, we’re going to use my favorite Excel feature: Vlookup. For those of you unfamiliar with this greatest of all tools, take a look back at my post on Almighty Vlookup for either an intro or a refresher.
We’ll begin by reviewing our data, to see what we can use to uniquely identify our constituents. Then we’ll export some pertinent information out of Raiser’s Edge, and use Vlookup along with other formulas inside Excel to stitch our info together. Let’s get started.
Reviewing our Data
The first step is to check what info you have within your import data. Even if we don’t yet have the Import ID, you should have something to uniquely identify your constituents. Most often, you should have at least a first and last name, often title, middle name, and address information as well.
For this example, we’re importing new committee assignments, which exist as an attribute inside Raiser’s Edge:
| First Name | Last Name | Committee | As Of | Notes |
| Benjiman | Connor | Development | 07/01/2014 | |
| Orla | McGrann | Strategic Planning | 07/01/2014 | Chair |
| Travis | Inman | Finance | 07/01/2014 | |
| David | Korbin | Finance | 07/01/2014 | |
| Jennifer | Kennick | Development | 07/01/2014 | Chair |
| Roger | Langley | Future Planning | 07/01/2014 | |
| Lucy | Avdeenko | Strategic Planning | 07/01/2014 | |
| Otto | Montgomery | Future Planning | 07/01/2014 |
Looking at our information, we will use First Name and Last Field to uniquely identify our constituents.
Creating our Export
To perform our import, we’ll need an Import ID or a Constituent ID for each of our constituents receiving a new attribute. Instead of opening each record, going to file->properties, and copying the Import ID, I’d rather export a giant list of every constituent in the database, and make the computer do the hard work.
We have a first name and last name in our original data. Now we’ll run a constituent export for All Records, taking the first name, last name, and constituent Import ID.
To recap, in our committee list, we have First Name and Last Name, but we need Import ID. As our newly-exported data for all constituents has First Name, Last Name, and Import ID, a simple vlookup provides the necessary Import ID information.
Creating our Keys
Now it’s time to start manufacturing our unique identifiers, known as a key. First we need to think about the necessary format for our data. vlookup will look for data in only a single column. As such, we’ll need to have first and last name (your unique identifiers) all in a single column. We’ll use a simple excel function called Concatenate to combine all of our required fields together:
| key | First Name | Last Name | Committee | As Of | Notes |
| =CONCATENATE(B2,",",C2) | Benjiman | Connor | Development | 07/01/2014 | |
| =CONCATENATE(B3,",",C3) | Orla | McGrann | Strategic Planning | 07/01/2014 | Chair |
| =CONCATENATE(B4,",",C4) | Travis | Inman | Finance | 07/01/2014 | |
| =CONCATENATE(B5,",",C5) | David | Korbin | Finance | 07/01/2014 | |
| =CONCATENATE(B6,",",C6) | Jennifer | Kennick | Development | 07/01/2014 | Chair |
| =CONCATENATE(B7,",",C7) | Roger | Langley | Future Planning | 07/01/2014 | |
| =CONCATENATE(B8,",",C8) | Lucy | Avdeenko | Strategic Planning | 07/01/2014 | |
| =CONCATENATE(B9,",",C9) | Otto | Montgomery | Future Planning | 07/01/2014 |
Concatenate simply combines multiple fields together, all separated by commas in the formula. But, you can see I put in field B2, then “,”, then C2. If I had just typed =concatenate(B2,C2), our resulting field would look like “BenjimanConnor.” By inserting a “,”, instead we get the following data:
| key | First Name | Last Name | Committee | As Of | Notes |
| Benjiman,Connor | Benjiman | Connor | Development | 07/01/2014 | |
| Orla,McGrann | Orla | McGrann | Strategic Planning | 07/01/2014 | Chair |
| Travis,Inman | Travis | Inman | Finance | 07/01/2014 | |
| David,Korbin | David | Korbin | Finance | 07/01/2014 | |
| Jennifer,Kennick | Jennifer | Kennick | Development | 07/01/2014 | Chair |
| Roger,Langley | Roger | Langley | Future Planning | 07/01/2014 | |
| Lucy,Avdeenko | Lucy | Avdeenko | Strategic Planning | 07/01/2014 | |
| Otto,Montgomery | Otto | Montgomery | Future Planning | 07/01/2014 |
I then repeat the process for our second table containing the Import ID, so there are keys in both of our required tables.
The location of the key column matters. It must be in the leftmost column in both our imported data, and the table we just exported from Raiser’s Edge. Secondly, after you manufacture the key for each table, I highly recommend sorting (ascending) by the key for each table. This will greatly help the vlookup process.
The Vlookup
Now we have our two tables of information with our unique key in the leftmost columns. From here, we have a pretty standard vlookup, so you might want to check the aforementioned Vlookup blog post. Copy the exported table into the second sheet of the imported excel file. Next, highlight this second table and right click to name the range (I chose “records”).
Then it’s formula time! Our first formula will look as follows: =VLOOKUP(A2, records,4,FALSE).
| key | First Name | Last Name | Committee | As Of | Notes | ImportID |
| Benjiman,Connor | Benjiman | Connor | Development | 07/01/2014 | 00001-079-0000193 | |
| David,Korbin | David | Korbin | Finance | 07/01/2014 | 00001-079-0000225 | |
| Jennifer,Kennick | Jennifer | Kennick | Development | 07/01/2014 | Chair | 00001-079-0000226 |
| Lucy,Avdeenko | Lucy | Avdeenko | Strategic Planning | 07/01/2014 | 00001-079-0000232 | |
| Orla,McGrann | Orla | McGrann | Strategic Planning | 07/01/2014 | Chair | 00001-079-0000204 |
| Otto,Montgomery | Otto | Montgomery | Future Planning | 07/01/2014 | 00001-079-0000233 | |
| Roger,Langley | Roger | Langley | Future Planning | 07/01/2014 | 00001-079-0000229 | |
| Travis,Inman | Travis | Inman | Finance | 07/01/2014 | 00001-079-0000219 |
From here, all that’s left is to finish formatting your import file, and push it into the system.
News
Raiser's Edge® Blog
07/03/2014 6:00am EDT
Leave a Comment