AnsweredAssumed Answered

Copying information from newly imported records to a table in my database

Question asked by RyanAshton on Apr 1, 2015
Latest reply on Apr 1, 2015 by philmodjunk

Title

Copying information from newly imported records to a table in my database

Post

 

Hi All,

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:

Project Name
Project Date
Project Details
Architect/Designer
Developer/Property Manager
Engineer/Technical Consultant
Main Contractor
Subcontractor

The PEOPLE information will be in one of the following fields from the imported data:

Architect/Designer
Developer/Property Manager
Engineer/Technical Consultant
Main Contractor
Subcontractor

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: enquiry@chevalong.com) || Fire Protection Sub-Contractor: ; Mr TANG Frank  (Phone: 852 23183878 Email: enquiry@iauhxlier.com) || Mechanical & Electrical Sub-Contractor: Sandoof Building Contractors Ltd, (Phone: 852 27382111 Email: hskp@iimmooap.com.hk) || Demolition Contractor: U72T Engineering Co Ltd, Mr LAM Kim Chui  (Phone: 852 27889196 Email: info@inna.com) ; Mr NG Tak Hong  (Phone: 852 27728196 Email: info@iamm2.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
Title                                                  Mr
Full Name                                        LAG Tim 
Phone 1                                           852 2258187418
Email                                               enquiry@chevalong.com

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?


 

 

Outcomes