Importing Labels, Assigning field names to bulk data?

[Chris]

Howdy!

I'm stumped. Developing a mailing label database and running into trouble
with the data in it's formatted state. I have 3 MS word documents all filled
with hundreds of labels. They are formatted like so:

Mr. John Doe, I/T Director
Agency Name
Additional agency criteria (not always present)
Address_1
Address_2 (not always present)
City, State Zip

I converted the .doc to a .txt format for import into Access. On the .txt
document I have a huge list of labels such as:

label1
info info info
info info info
info info info

label2
info info info
info info info
info info info

etc.

When I import this data into Access, I have no way of specifying fields etc
and all the data just gets lumped into one field. Of course I want the data
sorted in the standard fields last name, first name, etc.

Is there a way to accomplish this w/out tons of data entry? Possibly a
script (hence the programming) specifying how to sort the data into the
desired fields?

Not sure where to start. Any help would be greatly appreciated.

 

[Tim Ferguson]

Is there a way to accomplish this w/out tons of data entry? Possibly a
script (hence the programming) specifying how to sort the data into the
desired fields?

Umm yes: not trivial but not too hard either. You are going to need some
knowledge of WinWord programming model as well as Jet... but it would go
along the lines of

' this line is a guess, but you can see what you are
' aiming for
for each cell in activedocument.tables(1).cells

' you've already opened a dynaset recordset to
' put the data into
With rsAddresses

.AddNew
!FullName = cell.paragraphs(1).range.text
!Agency = cell.paragraphs(2).range.text
' etc with other lines
.Update

End With

next cell


but the main problem is going to be identifying when a line is missing:
how do you know whether "Director of West Team" is an address or
additional agency info? This matters especially when it knocks out the
position of the following lines.

In short you do have a significant amount of work to do -- I would
suggest at first cleaning the label documents with eg blank lines for
missing info, making sure all names conform to a specific pattern, and so
on. Only you can know at what point this becomes more costly than just
redoing the lot... :-(

Best of luck!


Tim F