Merging databases and stopping duplicates
Discussion
I'm looking to mail out a newsletter to people from our client database, an old prospect database and a bought in database. There are bound to be quite a few duplicates and I don't have the time or energy to go through 5000 names and addresses one by one. Is there any easy way to do this? Any commonly used systems or software which can help?
So. What type of database software is used for each of your three data sources?
Easiest thing to do is export all the names from all three and bang 'em into three tables in an access database. Then create yourself some queries that search for duplicates and delete them. Then import any new names you don't have in your client database back into it.
Which system will you e-mail the newsletter out from? I take it you would like to get the names from the old prospects/external db into your new main system?
This kind of thing is only easy to do if your software packages support import and export and you can get the data into something you can work on.
Easiest thing to do is export all the names from all three and bang 'em into three tables in an access database. Then create yourself some queries that search for duplicates and delete them. Then import any new names you don't have in your client database back into it.
Which system will you e-mail the newsletter out from? I take it you would like to get the names from the old prospects/external db into your new main system?
This kind of thing is only easy to do if your software packages support import and export and you can get the data into something you can work on.
It's two Act! databases and an Excel database being put together into one large Excel database from which to do the postal mailing. I was hoping that there is something out there which will search through an Excel database and highlight and fields which are similar. If not then I'll just A-Z sort each column and look for ones which are the same when they are listed next to eachother, but as it's a quarterly newsletter and the contact database is regularly changing it's a task that will need to be done every 3 months so could be quite time consuming.
dick dastardly said:
It's two Act! databases and an Excel database being put together into one large Excel database from which to do the postal mailing. I was hoping that there is something out there which will search through an Excel database and highlight and fields which are similar. If not then I'll just A-Z sort each column and look for ones which are the same when they are listed next to eachother, but as it's a quarterly newsletter and the contact database is regularly changing it's a task that will need to be done every 3 months so could be quite time consuming.
Are you familiar with the VLOOKUP Excel function? If not, have a check on Help. It may be what you are looking for (without building your hopes too much!!) dick dastardly said:
It's two Act! databases and an Excel database being put together into one large Excel database from which to do the postal mailing. I was hoping that there is something out there which will search through an Excel database and highlight and fields which are similar. If not then I'll just A-Z sort each column and look for ones which are the same when they are listed next to eachother, but as it's a quarterly newsletter and the contact database is regularly changing it's a task that will need to be done every 3 months so could be quite time consuming.
Doable with Excel macros...if not as sweet as using access. Can you write VBA?
You probably want to look for duplicate addresses even when they are spelt slightly differently in the two databases, so that you don't inadvertently send a letter to each of Mr A.Smith and Mr A.J.Smith at the same address. Getting a database to tell you that two entries are "supiciously similar" is a bit more tricky than asking it ig they are identical. However for 5000 rows in Excel, it isn't so hard.
If you can get all of the data into Excel, I'd then do something like the following:
Build a table with one row per address. Sort the table by postcode and name. Add a new column to the table showing whether the last row has the same Postcode as the current row (so if postcode was in column A and we were on Row 3, the formula would be =A2=A3). The use conditional formatting, or filtering to see wherever you get the same postcode appearing twice and manually check if they are duplicate entries.
Clearly you could do similar stuff to highlight rows where the postcode, name and other fields you're interested in are absolutely identical and delete them, before you trawl through for the not-quite-identical duplicates.
If you can get all of the data into Excel, I'd then do something like the following:
Build a table with one row per address. Sort the table by postcode and name. Add a new column to the table showing whether the last row has the same Postcode as the current row (so if postcode was in column A and we were on Row 3, the formula would be =A2=A3). The use conditional formatting, or filtering to see wherever you get the same postcode appearing twice and manually check if they are duplicate entries.
Clearly you could do similar stuff to highlight rows where the postcode, name and other fields you're interested in are absolutely identical and delete them, before you trawl through for the not-quite-identical duplicates.
I am the IT manager for a mailing and print firm and to be honest to do this properly you will need to use software designed to do it = expensive or give the data to someone to do it.
Using the methods outlined you will do it but and get a resonable result. However I would be surprised if you didn't miss some that are close but not exact. For example the postcode miss typed etc.
If you can find somewhere without a minimum charge it shouldn't cost to much, alternatively think about giving the whole mailing out to someone. Have you thought how long it will take you to pack seal and post approx 5000 letters.
If you want any more advice feel free to ask.
Glyn
Using the methods outlined you will do it but and get a resonable result. However I would be surprised if you didn't miss some that are close but not exact. For example the postcode miss typed etc.
If you can find somewhere without a minimum charge it shouldn't cost to much, alternatively think about giving the whole mailing out to someone. Have you thought how long it will take you to pack seal and post approx 5000 letters.
If you want any more advice feel free to ask.
Glyn
Cheers for the advice all. I'll have a play around with Excel but unfortunately any kind of programming isn't my forte.
Believe me, the thought of packing and mailing 5000 envelopes wasn't an idea I entertained for long. I signed up a local mailing house yesterday who I will get to have a peek over the data too.
Believe me, the thought of packing and mailing 5000 envelopes wasn't an idea I entertained for long. I signed up a local mailing house yesterday who I will get to have a peek over the data too.
Did you get this sorted? Isn't that hard really. I'd export all databases to some common text format (like CSV), create a new database and load them all in, and either dedupe the addresses as they're loaded in (ignore on duplicate key error, assuming the email address is the primary key) or deal with it afterwards with a select unique or somesuch into another table. Then you can export as CSV and load into Excel.
How much data is there?
Steve.
How much data is there?
Steve.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff