Eliminate Duplicate Records

[cessna]

I am needing to import two fields from an Excel sheet which contains 25,000 records, of which only about 10,000 are unique. The import will create these records in my database for the first time and I just don't want all the duplicated records to be created as well. How do I only import the unique records OR how do I delete the duplicate records in a script if I have already imported them. Seems easy but I must be getting foggy with my logic skills.

Thanks

Edited July 14, 2007 by Guest

[Anuviel]

Why not get rid of duplicate records in excel prior to import?

If it is only 2 fields you could make a simple excel formula in the third field - something like:

If A1 = B1 "duplicate"

Copy and paste the formula and delete all the ones that say duplicate. Should be like couple of min...

Don't know without seeing the file.

[AudioFreak]

I may be mistaken but when you search a column in Excel does it not just go to the first Row containing what you searched for? This was why I got away from excel in the first place but that was like 5-6 years ago.

See here on the filemaker site on how to mark duplicates. This way after the import you can do a quick find and delete found set. The Import and the find(for deletion) could be scripted so it happens all at once.

Michael

[Anuviel]

Yes, for search but the way I would do it is via formula so basically you make a formula that will compare the values in two fields and if they are same it will put "duplicate" in the third field.

When you copy and paste the formula it dynamically changes so that it will check all of the fields.

So if i set up:

If A1 = B1 "duplicate" - when I copy & paste the formula

It will automatically change to

If A2 = B2 "duplicate"

If A3 = B3 "duplicate"

and so on...

[AudioFreak]

What about the deletion of the row? Manually deleting 10k+ records in short would suck....lol

The filemaker link I provided basically does what you are suggesting in excel, but would allow the deletion to take place instantly after the import and delete all duplicates at once.

I'm not too hip on excel but I would assume the deletion could also be scripted there? If it can I'm curious how cuz it may come in handy sometime

Michael

[Anuviel]

Yes, it can be scripted as well., I will post the reply when I get home, office is closing :(

[JesseSFR]

That FileMaker site post seems like it's a lot of overhead just to delete some duplicate records. I would try something like this:

http://sixfriedrice.com/wp/deleting-duplicate-records-in-filemaker/

You basically create a self join and loop through the records deleting anything that has the relationship present. Works like charm and the only thing you have to add is a relationship which is easily deleted when you're done.

[AudioFreak]

Six,

Thanks for the link...much better. I only used the Filemaker link once a few years ago and remembered it cuz it solved my problem then using 5.5.

Think I need to bookmark your site.

Michael

[JesseSFR]

No problemo, I'm glad you like it. Come back frequently ;-)

[comment]

Instead of deleting duplicates, you could import only unique records by setting the validation on the relevant field as 'Unique, Validate always'.

[cessna]

Thanks comment - your solution worked perfectly. Once I set the import field as unique it only took each ID # once and skipped over any that were duplicates on the import. This was a big help for me and much appreciated.

Thank you also to the rest of you for your suggestions as well. I will definitely study your suggestions and see if there is something I can learn from them as well. Thanks!