Global adding records from Excel report

Hi Christopher (couldn't find you with @)-


Without knowing how many potential duplicates you might create, I would advise against importing them all. You could potentially create hundreds if not thousands of duplicates.


I would, instead, export everyone from your database including their Constituent Id, first name, last name, street, city, state, and zip. I would then create a unique key using that information, something like:


FrankJohnston33SmithRoadBostonMA


I would copy that entire spreadsheet into the second tab of the same workbook where your import file is on the first tab. I would then create the same unique key in the first tab, then use VLOOKUP to see if I can get any matches between the two tabs. Once I was done with that process I might create another unique key with a little looser algorithm and see if I get any more matches (remove first name from the key, for example). I would, however, closely examine the matches using the looser key to ensure they are indeed matches. 


If I had an email address or phone number in the import file, I might also try a VLOOKUP against those values.

Answer #1 - Do you really want to do this?  Is it really necessary?


Actual Answer - I think you should really consider that this is a large undertaking.  It can be done, but not haphazardly.  I think Aaron Rothberg‍'s idea is good, with the caveat of exporting everyone in your database is a very time intensive process.  You may experience timeouts if you're hosted...or the process may never finish.  Depending on the number of records in your database, it might not even really be feasible. Also worth noting that you may have duplicates in your non-constituent records.  A new record that is imported in may already be a contact for an organization, or a spousal/familial record.  Another source of duplication.  The export/vlookup will help eliminate the creation of new records, but may not really remove the problem of duplication of non-constituent records.


But in regards to how to do it, .csv format is your friend.  Be careful of using excel for some numeric values, and make sure that they are not being formatted by the program.  I'd go through and find a record and verify how the data is laid out.  Are the fields proper cased?  You can't import calculated values like age, only birthdate.  And for your sanity, before you import, do a backup of your database and test it.  This will save you from pulling out your hair.


How much information do you know about this excel spreadsheet?  Do you trust the source?  Is the information in the spreadsheet clean and verified?  At the end of the day Christopher Bryant‍, this is your database to manage.  Exporting that many constituents without vetting the data in some way is a recipe for disaster. 


In other words, see Answer #1

Not to scare too much, since it can be done -- I have done it -- but quality of data is a huge deal. We received a file of about 20,000 records from a primitive database, in Excel format. The first step is to have the Excel sheet processed through NCOA. Many of the addresses in our file had been outdated for years, and a match-up process will not work against an old address. After that, put the file through a matching process. You can use the formula below to provide a more flexible lookup region than VLookup can provide.


=if(iserror(match(A1, $B$1:$B$100, 0)), "", "dupe")  --  where A1 is your record to look up, and the B range determines your column lookup range


Always use the least possible match criteria in order to accomodate the greatest number of typos. Our source list was terrible - out of 20,000 records, we retained only about 7,000 after eliminating dupes, deceased people, and unmailable addresses.


Lastly, code all new imported records with a "source" attribute so you can track where the constituent came from in case a dupe slipped through and later pops up in a Duplicate Constituent Tool merge.