1 Reply Latest reply on Apr 1, 2015 8:08 AM by philmodjunk

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

    RyanAshton

      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?