Compare database field with external list

[DaCo]

I have a database with some 33.000 records (classical CDs). I have a field "Composer" nad I want to search/compare with this list: http://en.wikipedia.org/wiki/List_of_21st-century_classical_composers. Have I got some of these composers in my database or not?

 

Unfortunately my database has become some kind of a mess over the years. I'm still looking for time to harmonize the entries so that they have a uniform syntax, but they are not at the moment. Here are examples of instances in "composers" in the database:

 

Woods Kenneth / Orch.Of The Swan

Tryon, Valerie / Royal Philharmonic Orchestra / Woods, Kenneth

Minton, Phil / Chen, Audrey

Samuelsen, Stanley

Alda Magna

 

As you will notice I changed practice at a certain time ... from "Surname, First name" to "First name, Surname". If the release involves several artists, it has always been the practice to separate these with a slash.

 

How do I run a compair between the database and a list? Converting the webpage into a list in text format or CSV isn' at problem, but how to run a compair?

[comment]

I think you should ask "how do I clean up my database" before asking how to compare it with another. Here are some of the steps you should take:

 

1. Separate multiple artists into multiple records in a related table. Since the relationship is likely to be a many-to-many, you will need (at least) a three-table arrangement of:

 

Records -< Roles >- Artists

 

 

2. In the Artists table, normalize each name to a uniform format. Separate fields for FirstName and LastName would of course be preferable, but at least get the order of the names to a common base. It looks like the presence of a comma can be used in order to distinguish between the two existing formats?

 

 

Once you have that, you can work on establishing a relationship to the "other" composers table - the one you will import from outside. There is practically nothing you can do with an external list, other than import it.

[DaCo]

Thank you, Consultant

I should have known that I now come to the "moment of truth" - that my database is silted up and must be cleaned up. I have made ​​small attempts several times, but have found it prohibitively to spend many hours.

My flat database consists of many fields. In addition to the title, composer and artist, I have information on the label name, catalog number, EAN code, three price columns, release date , etc.

Extracts of the database are sent as updates to wholesalers and retailers, as well as to update our own web shops. For this reason, I need to have a field for composer / artist that brings together all the names in one string in the respective field as the recipients databases require this.

Ultimately, I have to edit 2x33,000 fields manually. I have lived with the inconvenience until now and have actually rarely use operations of the kind I have described in my question.

Now I'll put my thinking cap on and consider whether I want to invest the necessary work.

[comment]

I need to have a field for composer / artist that brings together all the names in one string in the respective field as the recipients databases require this.

 

The thing is, once you have all the names separated, bringing them together in a calculation field is trivial.

 

 

Ultimately, I have to edit 2x33,000 fields manually.

 

Manually?! Why manually? This needs to be scripted, and - at least going by the examples you have posted - it can be scripted. You may have to inspect the results visually, several times, before making the final switch.

 

And that's the other point: you shouldn't be editing any existing data for this (other than correcting obvious data entry mistakes that will be discovered during the conversion process). You should leave this part of the house as is and build a new wing. When the construction is finished, make the move.  And that, too can be made in stages.