How to compare data between a database and a guide which are differently structured?

Question

A rather complicated problem in data exchange between a database and a bookform:

The organisation in which I work has a database in mysql for all social profit organisations in Brussels, Belgium. At the same time there is a booklet created in Indesign which was developed in a different time and with different people than the database and consequently has a different structure.

Every year a new book is published and the data needs to be compared manually because of this difference in structure. The book changes its way of displaying entries according to the need of a chapter. It would help to have a crossplatform search and change tool, best not with one keyword but with all the relevant data for an entry in the book.

An example of an entry in the booklet:

BESCHUTTE WERKPLAATS BOUCHOUT Neromstraat 26 • 1861 Wolvertem • Tel 02-272 42 80 • Fax 02-269 85 03 • Gsm 0484-101 484 E-mail [email protected] • Website www.bwbouchout.be Werkdagen: 8u - 16u30, vrijdag tot 14u45. Personen met een fysieke en/of verstandelijke handicap. Ook psychiatrische patiënten en mensen met een meervoudige handicap. Capaciteit: 180 tewerkstellingsplaatsen.

A problem: The portable phone number is written in another format as in the database. The database would say: 0484 10 14 84 the book says: 0484-101 484

The opening times are formulated completely different, but some of it is similar.

Are there tools which would make life easier? Tools where you would be able to find similar data something like: similar data finder for excel but then cross platform and with more possibilities? I believe most data exchange programs work very "one-way same for every entry". Is there a program which is more flexible?

For clarity: I need to compare the data, not to generate the data out of the database.

It could mean saving a lot of time, money and eyestrain. Thanks,

Erik Willekens

Answer 1

Erik,

The specific problem of comparing two telephone number which are formatted differently is relatively easy to overcome by stripping all non-numeric characters.

However I don't think that's really what you are trying to achieve. I believe you're attempting to compare whether the booklet data is different to the database data but disregard certain formatting.

Realistically this isn't possible without having some very well defined rules on the formatting. For instance formatting on the organisation name is probably very significant whereas telephone number formatting is not.

Instead you should be tracking changes within the database and then manually check the booklet.

Answer 2

One possible solution is to store the booklet details for each record in your database alongside the correctly formatted ones. This allows you to perform a manual conversion once for the entire booklet and then each subsequent year lets you just compare the new booklet values to the old booklet values stored in the DB.

An example might make this clearer. Imagine you had this very simple record:

Org Name    Booklet Org Name  GSM            Booklet GSM
--------    ----------------  ---            -----------
BESCHUTTE   BESCHUTTE WERKP   0484 10 14 84  0484-101 484

When you get next year's booklet, then as long as the GSM number in the new booklet still says 0484-101 484 you won't have to worry about converting it to your database format and then checking to see if it has changed.

This would not be a good approach if a large proportion of details in the booklet changed each year

Answer 3

Can you create an ODBC data source for the bookform? If the bookform is fixed-format or delimited text file then you can use Microsoft Text driver to create an ODBC data source for the bookform (another text driver to consider is DataDirect ODBC text driver). Assuming you can connect to the bookform data source using ODBC then the task becomes much easier.

Create another ODBC data source for the database and then use an ODBC data comparison/sync tool such as CompareData to compare the two data sources table data. Since some of the data may have different format between the bookform and database tables you would need to use a view on the database side data source to normalize the database side data to do any format transformation on the data so in your example the two phone numbers will transform to the same string