Jump to content
Sign in to follow this  
cessna

Eliminate Duplicate Records

Recommended Posts

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 by Guest

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.