Word To Excel By Way Of HTML

In my day job, I’ve been working a lot the last few weeks 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.