importing Data from a Word file

[Jayjay]

**Crossposted for lack of knowing which group is best. I'll read
followups in all groups.**

Sorry for the repost to mpae -

I'm looking for some brainstorming assistance on how best to convert
data into a format that I can import into an existing database.

Existing Database is "Contacts" and has fields like firstname,
lastname, companyname, address1, address2, city, state, zip, etc.
Its basically a "Contact" address book.

I have 5 people who have data that needs to be brought into this
database. But here's the tricky part. Their data is in Word, and
not in a dataset that is easily imported. Their layout is just like
an envelope address layout:

FirstName LastName
CompanyName
Address1
Address2 (if exists)
City, state, zip
phonenumber
email


Repeat for about 200+ pages worth of data.

And if a company has multiple contats the list would be:

Firstname LastName
Firstname LastName
Firstname LastName
Firstname LastName
CompanyName
Address1
Address2
City, state, zip
phonenumber

Sometimes the email preceeds the phonenumber, sometimes it falls
after. No particular order

Short of manual dataentry into a dataset of some sort - can you think
of any way to manipulate this kind of data.

I have converted all text to table - which then puts all data into 1
column. I've considered playing with Excel's pivot tables. But if
someone can brainstorm and come up with another idea, I'd love to hear
them.

Thanks.

 

[PC Datasheet]

There's going to be some manual data entry of some type so you just got to
minimize that. You have 5 people all with the same type of data so if each does
his part that gets you to 40 pages per person - not so daunting!

1. Put a comma after each FirstName and remove the space
2. Remove the spaces between city and state and state and zip
3. Look at what you have and decide which would be the less work - to make
phonenumber or email to come first and then manually change the exceptions
4 Use VBA in Word to put a comma at the end of each line
5. Use VBA in Word to strip the carriage return and line feed from the end of
each line except the email line )assuming phonenumber comes before email)

At this point you will have:

FirstName,LastName,CompanyName,Address1,Address2 (if
exists),City,state,zip,phonenumber,email,

6. In the lines where there is only Address1, add a comma in place of Address2
7. Use VBA in Word to remove the comma at the end of each line

You now copy and paste the data into a CSV file and then import it into Access.


--
PC Datasheet
A Resource for Access, Excel and Word Applications
(e-mail address removed)
www.pcdatasheet.com

· Design and basic development for new applications
· Additions, Modifications and "Fixes" for existing applications
· Mentoring for do-it-yourselfers who want guidance
· Complete application design and development
· Applications Using Palm Pilot To Collect Data And
Synchronize The Data Back To Access Or Excel