Organize Your Contacts

Posted by on Sep 12, 2010 in Blog, How-to | 0 comments

A big project of mine lately has been working on organizing my contacts and deduping them. The duplicate contacts really have been getting annoying especially since I have 20,000+ contacts. Some duplicates have an alternate email address, or work information which another similar contact does not have, or a few people at a company may use the same email address and so 3 contacts have different names but same email addresses.

So what is the process for deduplication? Have you tried installing a deduper program for your outlook without any luck because you have way too many contacts and duplicates for it to handle? Believe me, I’ve been very frustrated with all the options out there. There really is no holy grail for this issue right now and if some programmer writes a program that can handle this, he would become very rich.

I found there to be a necessity to delete, remove, or merge duplicate contacts because it helps to minimize clutter and helps to have all contact info in one place instead of trying to figure out which contact information to use, etc.

The closest I’ve seen that could handle deduplicating contacts is Google through Gmail’s contacts interface. These are the steps that I’ve used to organize my contacts. It’s a bit of a pain in the ass but until I manage to get a Scrubly account, this will have to do.

  1. The first thing to do is get all your CSV files from your different contact sources. For me, I have contacts within Gmail, Yahoo, Outlook, Linkedin, my phone (Blackberry) and other CSV files from different sources over the years.
  2. Use Soocial as a way to sync up your phone info if you don’t have another way to sync it. Export your phone contacts from there to a CSV file.
  3. Export the contact from the different sources (Linkedin, Yahoo, Gmail, etc) each into its own CSV file.
  4. If you use your Gmail account and sync it with your phone, create an alternate Gmail to use for the rest of this write-up, otherwise if you don’t sync the data and you’ve exported the Gmail contacts to a CSV already, delete all your contacts out of Gmail. You can use a Macro program like AutoHotKey to make your life easier.
  5. For each CSV:
    1. From a clean Gmail contacts slate, import one CSV and group it to the respective category, i.e. group the Linkedin contacts to a Linkedin group, the Yahoo contacts to a Yahoo group, or if a CSV is from a work list or a customer list, group them to that respective category.
    2. Categorize it
    3. Use the ‘find and merge duplicates’ within Gmail contacts to merge duplicates just in case
    4. Export the CSV from Google contacts to a folder where you’re going to keep all the other CSV’s so they don’t get lost and title the CSV with something appropriate once it’s saved. Export in either as a Gmail contacts CSV or an Outlook format CSV, but stick to that format the whole way through this process.
    5. Delete all the contact information that is there and repeat the import step from the beginning of #5.1

The reason we do all of this this way is because we’re assuming that some of these CSV’s together would go over the 10,000 limit for contacts in Gmail contacts.

On a side note, I wanted to mention that you can get the information from your Facebook contacts now. If you have a few contacts (I think the limit is 500 but it may be at 1000), you can use Yahoo to import your contacts from Facebook and then export them from Yahoo. If you have many contacts like me (2400+), there’s no easy complete solution right now, so here is a trick, it isn’t easy, and you wont get all the information if you do it the automated way. The easiest way (which ironically is a pain), is to import all your Facebook contacts into Windows Live. From there, I wasn’t able to export the data (prob because there were way too many contacts). I think you can export it as long as they add you on to their MSN, but not everyone uses MSN, and I only started using it for work. Use a free tool like Contact Capture and use AutoHotKey to automatically capture the contacts or better yet, manually do it, and if there is more information to be entered than what Contact Capture has available for fields, just write the name down of the contact and do it for the next one. Believe me, I’ve got a lot of contacts, but seemingly, this is the only way I have found to do this so far, so if you find anything in this post that is outdated or you can do more efficiently, please let me know. I’d love to include your suggestion here.

Now going back to what we were doing. Lets say my CSV file has 22,000+ contacts, and if you’re like me, and have been having problems with Outlook crashing on you because of invalid contact information or because of characters/symbols in the data that can’t be read, Gmail contacts is a very good solution, a very tedious one but albeit the best one we have for right now. Since there is a 10,000 contact limit, we have to do a couple of things to fully remove duplicate data.

  1. Take the CSV file that has over 10,000 contacts and open it with Microsoft Excel or OpenOffice Excel
  2. You can delete duplicate data if you believe you have a lot of duplicates (Gmail contacts does this so it’s not a necessary step. Select all cells with your data and then go to Data –> Data Tools –> Remove Duplicates. Make sure that you every column has a check mark next to it and then press Ok.
  3. The next thing to do is to sort the list. I would recommend sorting by ‘last name.’ Go to Data –> Sort, make sure that you have a sort dialog box open that allows you to change the column. Change the column until you get to one that has the last name visible and press Ok.
  4. Splice up the file into equal 10,000 row parts. The way I did it was I created 2 more blank CSV files I copied the header (row 1) to each CSV and then selected row 2 all the way to 10,000 and pasted it into row 2 in the next CSV file. I did this until I had 3 files for my 22,000 contact with each CSV having less than 10,000 contacts in each.
  5. One CSV file is imported into Gmail contacts
  6. Find and Merge Duplicates
  7. Export the file
  8. Delete all the contacts there
  9. Repeat from step #5 until all your broken down CSV files have gone through this process

Now the big step comes in over here. Now that all your contacts should be grouped into some Gmail contacts type grouping, there’s less of a chance of mixing up where the contacts are from (the reason why we grouped the CSV’s earlier into a grouping category).

  1. Open up a new CSV file
  2. Copy the header from one of the CSV files into row #1 of the new CSV
  3. Copy the contents from row #2 and on for each CSV file into the new large CSV file
  4. Sort the data I recommend that it be sorted by last name
  5. Break the CSV into smaller 10,000 parts as shown above
  6. Import each file into Gmail contacts
  7. Find & Merge Duplicates
  8. Export each file
  9. Open up each file and copy all the data (header from one CSV and from row #2 and on for all the CSVs that were just exported) from each CSV file into one new CSV file.
  10. Sort by email address and repeat the steps above

This is just a thorough way of making sure you don’t have duplicates. You can actually be more thorough and sort by phone number and do the process again, but the process really is time consuming and it’s unnecessary to be extremely thorough since most of the duplicates should have been merged or removed.

This is what I have done. Sort each 10,000 CSV into whether I know them or not, and export the ones that I don’t know and the ones that I do know to new CSV files. I do this for each CSV and I then export the ‘unknowns’ to Outlook (which has about a 50,000 contact limit. This is great because any previous customers that I haven’t met or contacts from email farming will go here. The ones that I do know will stay in Gmail contacts all organized and synced up to my Blackberry. 🙂

I hope this article helped you become more productive. This isn’t something that you’re going to have to do all the time, but it’s like spring cleaning, if you use email farming tools for you emails (which I don’t use yet), it may be good to do this once every year.

Leave a Reply