Give Me Access to Word

March 17, 2011 10:15 AM

Pulling data (in label sheet formatting) from Word 2003 to Access 2003. I'm a special snowflake details after the jump...

I have inherited a "database" that is, in fact, just names and addresses in a Word 2003 document formatted to print out on label sheets. Names and addresses have been deleted, put back in, formatted within each label field, and any other mangling you can think of.

I've got all the data lined up in this Word document with uniform formatting, justification, etc, so that it prints out nicely on to label sheets.

But I know that all this info would be better served by being in a database (Access is the only thing I have *access* to). Not being well-versed in Access, I beseech you, MeFi, to help me come up with a way to migrate/export the info that's in each "field" into an Access database that can be more easily maintained.

Everything I've googled/read has been mostly about Access-->Word and not vice-versa.

posted by kuanes to Computers & Internet (8 answers total)

Here's the trouble: what you have there is not data, it is a document. An unstructured document. What you need to do is pull out the data that unstructured document contains; the easiest way I've found is usually to just copy and paste into a text file (i.e. notepad or a better editor).

After you've done that, you can work with the data as you need to. My usual tactic is to use a simple markup system to "tag" each line with the type of data it is, then write a script to import those tags into a database. You don't have to use Access; you can also use something like SQLite, which is what I would recommend (it's free, for one thing).

If you lack the expertise to do this, memail me.
posted by sonic meat machine at 10:41 AM on March 17, 2011

Response by poster: I guess I didn't think about it as document vs. data. Thanks for that. I was considering the whole copypasta thing, but thought there could be some better way.

Unfortunately, the option of NOT using Access is not an option. Health System that I work for requires the Enterprise software, and the backwards IT people will not look kindly on installing any SQL software on the work PC.
posted by kuanes at 10:46 AM on March 17, 2011

That's the beauty of SQLite; it requires no software installation, as its database lives in a single file on disk. You can access it via programming interfaces that are available for most languages (Python, Ruby, Java, etc.), plus email it to a home computer (or wherever) and have a graphical interface.

That said, it's also possible to write a script to convert the text file to something that Access can import, like csv. The real key is getting the data out of the word document and getting it tagged; doing something with it after that is a simple matter of programming.
posted by sonic meat machine at 10:49 AM on March 17, 2011

You can save the document as HTML and then import it into Access. At least it works if your data is in a table.
posted by elgilito at 10:58 AM on March 17, 2011

Best answer: You might try going into Excel first. I'm assuming the Word doc is at least in a "table"? If they used the Word "label" functions I'm pretty sure that creates a table, just with no borders visible.

If you get it into a single column, where each row is the full address block, this could be copy/pasted into Excel easily, and then you can use Excel functions like "Text to columns" and others to split it out into pieces so you could end up with name/address/city/zip columns.

Once you've got an Excel sheet the way you want it, it will import into Access no problem.
posted by dnash at 11:22 AM on March 17, 2011

You can write a procedure in access to read the word doc and then write to the access table (link)
Someone with more expertise in access could provide more guidance
posted by canoehead at 11:25 AM on March 17, 2011

Response by poster: Thanks to dnash. This has made a little better than repetitious copy/paste.

@sonic meat machine - If I had more time to devote to this, I would have definitely picked your brain for script-writing/SQL/etc.
posted by kuanes at 11:42 AM on March 17, 2011

In Word, you can convert a table to text, specifying the delimiter, the character that will tell a database when to go to the next record. Start by making it 1 column wide. Cut-n-paste will help you do that. Then make sure all lines have a paragraph mark at the end. Regularize the data by defining fields like Name, company, address line 1, address line 2, city/state/zip where a field is a full line of text. Use blank lines as needed if a record doesn't have, say, address line 2.

With clever use of the find-n-replace function, you can get this tidied up. Then convert table to text, with tab as the delimiter. Then into Excel.
posted by theora55 at 12:25 PM on March 17, 2011