How do you do this???? Parse Records

[ronwills]

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

[Steve Wood]

This script works on your sample file. You need to replace the value in ADD_BLANK_RECORDS(10) with the number of records (or more) that you expect in the final database.

dim tbl as p
dim tblb as p

tbl = table.open("companies")
tblb = table.open("new_table")
tblb.ADD_BLANK_RECORDS(10)
tbl.fetch_first()
tblb.fetch_first()
WHILE .not. tbl.fetch_eof()
WHILE tbl.Company <> "**"
if tbl.fetch_eof() then
end
end if
txt = "tblb."+ word(tbl.Company,1) + " = " + quote(alltrim(word(tbl.Company,2," ",10)))
'showvar(txt)
tblb.change_begin()
result = evaluate_template(txt)
tblb.change_end(.T.)
tbl.fetch_next()
END WHILE
tbl.fetch_next()
tblb.fetch_next()
END WHILE
tbl.close()
tblb.close()

[ronwills]

Could not have worked any better!

Steve,

Thanks for the help. I imported 1105 records without a single hitch. I added a ui_get_number() dialog box to ask how many records are to be added and a ui_msg_box() dialog to tell me when the routine has completed.

dim tbl as p
dim tblb as p
dim Number_of_Records as n

Number_of_Records = ui_get_number("Size of Imported Database","Enter in the number of records to import. Be sure to enter a number greater than you think you will need")
if Number_of_Records < 1 then
end
end if
tbl = table.open("bcchamber raw list")
tblb = table.open("bccchamber upload")
tblb.ADD_BLANK_RECORDS(Number_of_Records)
tbl.fetch_first()
tblb.fetch_first()
WHILE .not. tbl.fetch_eof()
WHILE tbl.Company <> "**"
if tbl.fetch_eof() then
ui_msg_box("Process Completed","The table has been uploaded",UI_OK)
end
end if
txt = "tblb."+ word(tbl.Company,1) + " = " + quote(alltrim(word(tbl.Company,2," ",10)))
'showvar(txt)
tblb.change_begin()
result = evaluate_template(txt)
tblb.change_end(.T.)
tbl.fetch_next()
END WHILE
tbl.fetch_next()
tblb.fetch_next()
END WHILE
tbl.close()
tblb.close()