Copying information from newly imported records to a table in my database
I have a database with two tables - PEOPLE and PROJECTS.
On a regular basis, I need to import new Projects to work on. The trouble is that the information on the people involved in those projects is very badly organised, so I need to copy the information from various fields from the newly imported records, to fields in my PEOPLE table.
The newly imported data has the following fields:
The PEOPLE information will be in one of the following fields from the imported data:
To make things hard, oftentimes some of these fields are empty and to make things WORSE - sometimes there is more than 1 persons contact details in any particular field.
An example of some information that may exist in one of the fields of a newly imported set of records could be as follows:
Mechanical Ventilation and Air Condition (MVAC) Sub-Contractor: Chevlong (Construction) Co Ltd, Mr LAG Tim (Phone: 852 2258187418 Email: email@example.com) || Fire Protection Sub-Contractor: ; Mr TANG Frank (Phone: 852 23183878 Email: firstname.lastname@example.org) || Mechanical & Electrical Sub-Contractor: Sandoof Building Contractors Ltd, (Phone: 852 27382111 Email: email@example.com) || Demolition Contractor: U72T Engineering Co Ltd, Mr LAM Kim Chui (Phone: 852 27889196 Email: firstname.lastname@example.org) ; Mr NG Tak Hong (Phone: 852 27728196 Email: email@example.com)
The information I need to extract and put in my People Table - along with the example from above is as follows:
FIELD EXAMPLE FROM ABOVE
Designation Mechanical Ventilation and Air Condition
Company Chevlong (Construction) Co Ltd
Full Name LAG Tim
Phone 1 852 2258187418
There seem to be a couple of conventions that the imported data follows:
1) Designation is most times followed by ":"
2) Company is after the ":" and before a ","
3) Title is either Mr, Ms, Prof, Dr, etc
4) Full Name is after the Title and before "("
5) Phone 1 is after "Phone: "
6) Email is after "Email: "
Additionally, each Project gets a unique Project_ID which is sequential so that the People can be associated with the Projects.
If I could get 80% or even 70% of the data being transferred to the correct fields, that would be fantastic!!!
The rest I could input and correct manually if need be.
Is there a way I can do this?