Word To Excel By Way Of HTML
|
Whitney Potsus (SIG Manager) 25 February 2009 |
Categories: | • Tools of the Trade |
| Comments: | 2 |
In my day job, I’ve been working a lot with requirements documentation and test cases. These have been huge documents for the last year—hundreds of requirements and associated test cases—that were being maintained in Word and Excel until we finally got access to the client’s requirements management (RM) system.
The Excel document was relatively easy to import into the RM system. Spreadsheets always play well with databases.
The Word document was a pain to import into the system. Word files never seem to play well with anything but Word.
After spending hours cleaning out blank lines and extra carriage returns, heavily stylizing the document, and converting a couple of hundred tables to text, the content still wouldn’t import into the RM system cleanly. It became clear that the test documentation had to be moved into Excel in order for us to get it into the RM system, but the content wasn’t going to be easy to get into a spreadsheet (either). Every heading, list item, and test step had to be in its own cell in Column C—and there was a lot going on with Columns A, B, and D as well.
With some trial and error with a couple of the Save As formats in Word, I found the solution in the least likely of formats: HTML.
By saving the Word document to the Web Page format in the Save As dialog box (NOT Web Page, Filtered or Single File Web Page), and then importing that HTML file into Excel, I had a fairly clean file conversion. To be sure, there were some blank rows that had to be deleted, columns that all had to be resized, and maybe a handful of text paragraphs that didn’t fall into the right column cell (though they were on the right row), but all in all, 4,000 rows of data fell into place rather quickly. Start to finish, the whole process took less than an hour.
Writing text—especially large quantities of it—in a spreadsheet is an adjustment. But if you’re creating text that ultimately has to be imported into a database, the time savings achieved by working from a spreadsheet are well worth the adjustment.
Comments
What requirement management system was your client using? We're piloting HP Quality Center, which I think is more of a testing management application, but it is supposed to have requirements management, too. Not that we're looking into that. Yet.Posted by Carol Anne on 26 February 2009 at 10:03 AM
Hi Carol Anne: Telelogic DOORS (recently taken over by IBM). It's a monster of a system, but the projects are huge as is the company. A local acquaintance of mine is currently on a team evaluating RallyDev for her company. You could also look around the Requirements Network (www.requirementsnetwork.com) and see what systems are talked about up there. Registration to the community is free. Thanks for reading! WhitneyPosted by Whitney on 26 February 2009 at 10:45 AM
Submit a comment: