I have a database of company records which all of the pertinent data are, unfortunately, contained in one field, for example (see attached database for live example):
Company (field name) in the Companies.dbf Table
==================================
Company ABC Beverage
Representative Bill Williams
Address 4808 Moorland Lane #802
City Washington
State DC
Zip 20032
Phone 202-555-1010
Fax 202-555-1081
Web www.awidercircle.org
Specialty Drinks
**
Company Software Producers
Representative Jim Jones
Representative2 Larry Smith
Address 1212 Smith Street
Address2 Suite 202
City Washington
State DC
Zip 20009
Phone 202-555-1111
Fax 202-555-1010
Email [email protected]
Web www.software.com
Specialty software design
**
Company Wilson Audio
Representative Jim Wilson
Representative2 Bob Wilson
Representative3 Bill Wilson
Address 444 West Street
City Annapolis
State MD
Zip 22939
Phone 301-555-2920
Email [email protected]
PLEASE NOTE:
1. Not every company uses all of the fields. Some have just one Representative others have three (Representative2 and Representative3). Some have an Address2 and some have Email addresses while others do not.
2. Each company record is separated by a record containing two astericks **
I am looking to parse this information so that I end up with a table with the following fields with the correct information:
Company
Address
Address2
City
State
Zip
Fax
Phone
Representative
Representative2
Representative3
Email
Web
Specialty
Company (field name) in the Companies.dbf Table
==================================
Company ABC Beverage
Representative Bill Williams
Address 4808 Moorland Lane #802
City Washington
State DC
Zip 20032
Phone 202-555-1010
Fax 202-555-1081
Web www.awidercircle.org
Specialty Drinks
**
Company Software Producers
Representative Jim Jones
Representative2 Larry Smith
Address 1212 Smith Street
Address2 Suite 202
City Washington
State DC
Zip 20009
Phone 202-555-1111
Fax 202-555-1010
Email [email protected]
Web www.software.com
Specialty software design
**
Company Wilson Audio
Representative Jim Wilson
Representative2 Bob Wilson
Representative3 Bill Wilson
Address 444 West Street
City Annapolis
State MD
Zip 22939
Phone 301-555-2920
Email [email protected]
PLEASE NOTE:
1. Not every company uses all of the fields. Some have just one Representative others have three (Representative2 and Representative3). Some have an Address2 and some have Email addresses while others do not.
2. Each company record is separated by a record containing two astericks **
I am looking to parse this information so that I end up with a table with the following fields with the correct information:
Company
Address
Address2
City
State
Zip
Fax
Phone
Representative
Representative2
Representative3
Web
Specialty
Comment