Import Database into existing database, without the duplicates!

Posted on 2004-11-23
Last Modified: 2010-04-27
My working database contains nearly 300,000 records and one of my clients gave me their database of 40,000 records, but I know that there are many, many duplicates. I got great help the last time I did this and was able to fix a duplicate problem AFTER I imported the data. This time, I would like to get rid of the duplicates before I import them. First Name, Last Name and Company could be a good way to compare the records in both databases, but many of these people have left their companies, or I have the old information. For now, I would just like to see which names in my clients database DO NOT exist in my database and import them. Of course, this will not help with the Mikes and Michaels, or the Susans and Sues, or the misspelled names, but it's a start...

Any suggestions?

Question by:johnmoed
    LVL 28

    Accepted Solution

    create a calculated field in both data bases called "check" for instance.
    check = name&firstname&company (and any other if you'd like to improve dup detection.

    make a link in between the bases using this field

    create a calculated field, say 'link_true' in the small database (the 40k lines one) = if (database1::check ; "ok" ; "")
    search all records in small database where link_true="ok"
    go to main database, import the found set you just did from the small database.

    Author Comment

    I tried a couple different relationships and was able to delete thousands of exact duplicates. E-mail address is a really good one to use for this and I also used direct dial number.

    Thanks again!
    LVL 28

    Expert Comment

    the more fields you use, the more accurate the dups will be!
    the real problem is to take into account trailing spaces, phone nbs with different formats, so it is also a good idea to remove all double, leading and trailing spaces from all text fields and keep the deleted ones in a corner for a while just in case.
    till next, thanks for the points, at least 3 weeks I had not got anything, and bill is chasing me!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
    Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now