comparing Excel list to RE

Julie Hiland · April 2016

I have an Excel list of patient names, with no unique identifier… just name and address. (And a patient ID which we do not house in RE.) As a foundation supporting a healthcare system, we get lists like this fairly often.

What would be the easiest way to compare this spreadsheet to records in our database, and either create a query in RE or flag those that are in our database? Outside of exporting a massive list from RE, and doing some kind of complicated Excel maniupation?

We have ImportOmatic, and all of these names are also in ResearchPoint. So I'm sure we have the mechanism to do it, but my brain is having trouble pinpointing the best way to do it.

Marie Stark · April 2016

I had to do something similar, and I used Importomatics. I imported both an alias and an attribute for each record. I had to match them using names, but you could also use address in your duplicate criteria,

Jen Claudy · April 2016

I have this situation on occasion (should be more often, but it gets put on the back burner far too much) and I use MS Access. I essentially export all of RE to an Access database, then in another database (what I call my Front End Database), I paste the new data into a table. The RE data is accessible via a linked table. Then a series of Access queries pull together records that are an exact match or are partial matches of some combination of firstname, lastname, and address. This gets me reasonably close to eliminating duplicate entries into RE, and I then Import data from Access to RE either to update existing records or create new ones.

If you're familiar enough with Access (and I explained well enough) to understand the above, and you do this on a regular basis with a substantial number of records, I suggest you try it. And record the Patient ID in RE somewhere (there are other threads about other IDs, student/patient/etc., in the Community) in case you have the same one come thru later. If you don't know/have Access, it may be worth looking into, and I'm happy to help if needed.

You should be able to do much the same in Excel, but Access lets you store processes and reuse them. So a little more of a capital investment of time and effort in setting up the process, but then running/using it becomes easier and faster.

Cammi Derr · April 2016

We record student ids as an attribute which allows me to quickly find matches in excel from my exports of our student data and exports of our constituents.

After that if there is no student id on the RE record, I compare name fields by concatenating name fields from RE and our export to get closer.

Usually after that, there are only a few (mostly because of nicknames) that we might not have matches for, in which case we will use manual searches.

Faith Murray · April 2016

Three months ago, I would have recommended comparing them outside of RE, in Excel or Access myself. However, you may try reading the RE Blog at https://community.blackbaud.com/blogs/10/754?utm_medium=email&utm_source=transactional&utm_campaign=community%40blackbaud.com, which says:

"Have you ever had a spreadsheet of constituents to add to Raiser’s Edge, but you aren’t sure who is already in the database and who isn’t? Importing is a fairly straightforward process if they’re all new constituents, but can be a bit trickier if some of your constituents already have records and some don’t. ... Fortunately, import gives us the option to search for duplicates when importing, so I can catch any existing constituents before importing them as duplicates. By marking Use duplicate criteria for new records, I can have Raiser’s Edge alert me when I’m adding a likely duplicate to the database."

I have yet to try this with an import to see how well it works. As suggested already, I would highly recommend creating an Attribute for patient number to ease all future imports, unless there is some legal/privacy reason not to do this.

Jen Claudy · April 2016

F Murray: I know RE can use it's duplicate finder process, but I find it clunky. Usually, the list I'm importing has messy data, whether it be names not split out into separate fields/cells/columns or addresses not being formatted per USPS as our RE db is, I don't feel like I have the same level of control over what it's looking for... I don't even like it when I put in email sign ups from OLX! It just never seems to work well for me. Maybe it's me, though. Or maybe it's that I'm familiar enough with Access that I find it faster and more controllable (I like to know exactly what it's doing behind the scenes, which you rarely do if using any RE canned process or report).

Matt Page · April 2016

We have a similar situation although the records I'm looking to import/work with have their own unique idetifier.

I use a variety of approaches. I have used the duplicate tick box in import it's quite useful, but doesn't catch everything my own XL sheets do. Conversely it catches somethings I wouldn't.

In terms of doing it via Excel you an reduce the size of your initial export by cut and pasting the surname and the postcode (ZIP) fields into a "one of" query (i.e. "Surname is one of...x,y,z" AND "Postcode is one of a,b,c").

I then tend to concatenate in excel, usually subbing out any spaces in the address.

And sometimes then I have a formula that I use to grade each of the matches, so I just have to check the poorer ones.

None of it is perfect though so it's all quite a headache.

Matt

Nicole S · April 2016

Jen Claudy:

F Murray: I know RE can use it's duplicate finder process, but I find it clunky. Usually, the list I'm importing has messy data, whether it be names not split out into separate fields/cells/columns or addresses not being formatted per USPS as our RE db is, I don't feel like I have the same level of control over what it's looking for... I don't even like it when I put in email sign ups from OLX! It just never seems to work well for me. Maybe it's me, though. Or maybe it's that I'm familiar enough with Access that I find it faster and more controllable (I like to know exactly what it's doing behind the scenes, which you rarely do if using any RE canned process or report).

I agree that the duplicate feature can be clunky. It lets dupes in and keeps new records out. It's definitley not as good a a human eye.

One thing that i have done is import the records and have them all tagged in some way and marked "inactive". Then after importing I can work my way through them to get them all up to code, so to speak. I use the inactive box to keep them out of mailings or other reports until I can get them cleaned up. Of course, this can depend on how much time you have and the size of your list. I've found this to be a pretty good system that even others in the office can understand so that when they come across one of these imported records in a search they aren't alarmed that it's a dupe or kind of messy and they can even address the issues (sloppy addressees, inconsistent capitalization, etc.) themselves.

Catherine Burns · April 2016

Hi. We also have ImportOmatic and have Excel lists of prospects that we maintain outside of RE. We haven't purchased List Management for ImportOmatic yet. We don't want to import the whole Excel list into RE but we do know who is on the Excel list and already in RE - and then exclude them from mailings targeted to the Excel list.

So what we do is use ImportOmatic to "import" the Excel list, but we do not create new constituents (there is an option to stop this in ImportOmatic), we just import a new action to those records it finds in RE. I then export the details of records that have had that particular action added and then backmatch it against the Excel spreadsheet using vlookups to find who matches. To make it easier to backmatch we import a unique ID number from Excel into the Action title. You could add a unique ID number to your Excel list to do this.

Because the Excel data is not as clean as the RE data it does not find everyone but it is better then no dedupe at all.

Faith Murray · April 2016

Matthew Page:

In terms of doing it via Excel you an reduce the size of your initial export by cut and pasting the surname and the postcode (ZIP) fields into a "one of" query (i.e. "Surname is one of...x,y,z" AND "Postcode is one of a,b,c").

Matthew, I know this is a somewhat silly question, but how do you cut-paste the Excel fields into a "one of" query? I tried doing that for the first time a few weeks ago, and must have done something different, because it would only paste the first line. I ended up having to type them all in manually into the query. (Good thing there weren't very many.)

Alan French · April 2016

You have to scroll down the blank list before pasting. I've seen some people advocate scrolling right to the bottom of the list, but I find that anything more than one row works fine for me.

Jen Claudy · May 2016

Alan French:

You have to scroll down the blank list before pasting. I've seen some people advocate scrolling right to the bottom of the list, but I find that anything more than one row works fine for me.

And, it will only paste the first 500 (I think) into the Query Criteria. There is no warning or anything to let you know that not all of your data is in the criteria line. And for lists longer than 500, just do: ConstID one of... OR ConstID one of...

(It's been a while since I've really paid attention...and RE:7 kicked me out while I was in a meeting and I'm about to head into another hour-long meeting so no time to sign in and test this.) I believe if you scroll down just a few lines and paste, you'll see what is being pasted before clicking [OK], but if you scroll all the way to the bottom, it looks like it's not going to paste anything until you click [OK] and then you see the data in the line of Criteria.

Matt Page · May 2016

F Murray:

Matthew Page:

In terms of doing it via Excel you an reduce the size of your initial export by cut and pasting the surname and the postcode (ZIP) fields into a "one of" query (i.e. "Surname is one of...x,y,z" AND "Postcode is one of a,b,c").

Matthew, I know this is a somewhat silly question, but how do you cut-paste the Excel fields into a "one of" query? I tried doing that for the first time a few weeks ago, and must have done something different, because it would only paste the first line. I ended up having to type them all in manually into the query. (Good thing there weren't very many.)

Sorry, been a bit busy for the last week or so, but basically what Alan and Jen say is correct. You can only paste 500 entries and you do it by scrolling to the bottom line and then clicking on it and then ctrl+V ing it. (I think some people can just click on any line other than the top one, but I've always had to scroll to the bottom one.

If you have more than 500 records you can just add the same criterion again (and again) and join them together with an OR.

If you have A LOT more than 500 you can sometimes grab these using the import tool. There's a Knowledge base article on this here - https://kb.blackbaud.com/articles/Article/38639 - I think you can do this just using the "validate" option, though it doesn't mention that so maybe not. I rarely need to do it for more than 5000 records so it's usually just quicker to string a load of 500s together.

Hope that helps,

Matt

Faith Murray · May 2016

Alan French:

You have to scroll down the blank list before pasting. I've seen some people advocate scrolling right to the bottom of the list, but I find that anything more than one row works fine for me.

Very helpful Alan, Jen, and Matt - thank you!

Jeffrey Montgomery · May 2016

Catherine Burns:

So what we do is use ImportOmatic to "import" the Excel list, but we do not create new constituents (there is an option to stop this in ImportOmatic), we just import a new action to those records it finds in RE. I then export the details of records that have had that particular action added and then backmatch it against the Excel spreadsheet

Catherine, good call! There is also an option in ImportOmatic to create an output file of your imported records. It will be an exact copy of your import file, but it will only contain the imported rows, and it will have an additional column that is the RE Import ID of the record that was created or matched (in your case, only matched, because you have it set to not create new records). Here is the documentation on output files.

Another option (maybe even easier, now that I think abou tit) is to set IOM to create a query of the imported records, then you could just use that query to create a file with whatever data you want

Thanks,

Jeff

Anna Warner · May 2016

F Murray:

Three months ago, I would have recommended comparing them outside of RE, in Excel or Access myself. However, you may try reading the RE Blog at https://community.blackbaud.com/blogs/10/754?utm_medium=email&utm_source=transactional&utm_campaign=community%40blackbaud.com, which says:

"Have you ever had a spreadsheet of constituents to add to Raiser’s Edge, but you aren’t sure who is already in the database and who isn’t? Importing is a fairly straightforward process if they’re all new constituents, but can be a bit trickier if some of your constituents already have records and some don’t. ... Fortunately, import gives us the option to search for duplicates when importing, so I can catch any existing constituents before importing them as duplicates. By marking Use duplicate criteria for new records, I can have Raiser’s Edge alert me when I’m adding a likely duplicate to the database."

I have yet to try this with an import to see how well it works. As suggested already, I would highly recommend creating an Attribute for patient number to ease all future imports, unless there is some legal/privacy reason not to do this.

We use this method all through the fourth quarter when we have hundreds of gifts each day that we don't know if they are new donors or not. We get all of our gifts processed first through a lockbox, and then they transmit data to us in a spreadsheet. For prospect mailings, they don't have constituent IDs and the data is typed in for us. We use the import process to identify if they are constituents or not. We have a high success rate with this and actually don't create many duplicates at all.

Anna Warner · May 2016

Matthew Page:

F Murray:

Matthew Page:

In terms of doing it via Excel you an reduce the size of your initial export by cut and pasting the surname and the postcode (ZIP) fields into a "one of" query (i.e. "Surname is one of...x,y,z" AND "Postcode is one of a,b,c").

Matthew, I know this is a somewhat silly question, but how do you cut-paste the Excel fields into a "one of" query? I tried doing that for the first time a few weeks ago, and must have done something different, because it would only paste the first line. I ended up having to type them all in manually into the query. (Good thing there weren't very many.)

Sorry, been a bit busy for the last week or so, but basically what Alan and Jen say is correct. You can only paste 500 entries and you do it by scrolling to the bottom line and then clicking on it and then ctrl+V ing it. (I think some people can just click on any line other than the top one, but I've always had to scroll to the bottom one.

If you have more than 500 records you can just add the same criterion again (and again) and join them together with an OR.

If you have A LOT more than 500 you can sometimes grab these using the import tool. There's a Knowledge base article on this here - https://kb.blackbaud.com/articles/Article/38639 - I think you can do this just using the "validate" option, though it doesn't mention that so maybe not. I rarely need to do it for more than 5000 records so it's usually just quicker to string a load of 500s together.

Hope that helps,

Matt

I LOVE using import to create a query of records when you have the IDs! It is really easy to do by importing the constituent IDs directly over their current IDs...in other words, you change nothing, but get an output query of records! It is so helpful and quick! If you haven't tried this, you should!

comparing Excel list to RE

Comments

Categories