Better way to merge two databases?

[ilubmoney]

Database 1 and 2 were created and maintained after some period of time, but after awhile, it was decided that it would be more efficient to just have one database with data from both 1 and 2.

Since Database 1 has far more records than Database 2, it seemed like a simple matter of adjusting 1's layout, exporting 2's data, and importing it into 1's database, right?

Somehow, it wasn't that simple. There are quite a few fields to match up for the import, and it would take a long time just to match them up. Is there a more efficient way to import?

 

[TStriker]

Can you provide a bit more info? The first part of your post implies two completely separate yet similar databases but you mention matching records. Can you elaborate a bit?

Thanks!

-Striker

 

[ilubmoney]

Certainly. Both database 1 and 2 share essential contact information (names, addresses, phone numbers, etc.).

DB1 has roughly 20,000 records and has a lot more fields. We'll call the rest of the fields A, B, C, D, E, F.

DB2 has about 3,000 records, some of which are the same records as DB1, but it's difficult to estimate how much are the same. DB2 has fields X, Y, Z.

When I did an import from DB1 and DB2 on a test database, the results were disastrous. It might be because things were translated oddly to .tab and .csv, but in both kinds of datafiles, some of the records from the import data weren't truncated neatly.

Sorry if this still sounds vague. As you can probably tell, I am very much a newbie with FileMaker; lord knows why I was asked to investigate this issue in the first place.

 

[TStriker]

I should have asked you which version you were on.

Anyway, if you're on a later version (7 or 8) you should be able to import using the native (.fp7) format rather than .csv or .tab or .txt.

There is also a process where you can have FileMaker try to match the data being imported to the existing data and then just add any remaining records that don't match. However, that being said I probably wouldn't take that approach.

Without knowing all the details of your situation, I would probably just import everything into your main DB and then try to systematically remove all of the duplicates once they have been imported. The trouble with contact data is the one that you are probably already painfully aware of in that Susie Doe, Suze, Doe, Sue Doe, Susan Doe can all be the same person. Having a computer figure this out is difficult at best.

I have a feeling that I'm not helping out much. You said that your earlier results were disasterous; how so?

-Striker

 

[ilubmoney]

Thanks for trying to help, Striker. As it is, we decided to do it the "long, arduous" way and give the old DBs an overhaul anyway.