Editor's Note: Send your SQL Server questions to Microsoft's SQL Server development team at [email protected].
How can I easily import a table embedded in a Microsoft Word 2000 document directly into SQL Server 2000 or 7.0? Each table in the Word document constitutes one row.
Word has no OLE DB provider, so Data Transformation Services (DTS) can't easily access a Word document. Your best bet is to write a VBScript macro to connect Word to SQL Server. Using the VBScript macro, you can load the data directly into SQL Server without using a SQL Server utility.
Alternatively, you can partially automate the import by copying the Word table into Microsoft Excel, then exporting the data to DTS. Follow these steps:
- In Word, select a table by clicking the Table Select icon in the table's top left corner, then press Ctrl+C to copy the table.
- To convert the table into an Excel row, in Excel, paste the table into a workbook by clicking the top left cell (A1), then pressing Ctrl+V.
- To transpose the newly pasted row to a column, copy the row, tab to a new worksheet, click in the A1 cell, click the drop-down arrow on the paste icon, then select the Transpose option. The transposing action turns each row into a column.
- Select the data row, then paste it to the end of the master sheet. When all tables have the same form and structure, include the row headers only the first time you paste a row into the sheet.
- Repeat Steps 1 through 4 for each Word table.
When you've completed this process, you'll have a worksheet with one title row and all the data rows in a format that DTS can load. If you're planning to export many Word tables and you want to avoid keystrokes, you might consider using macro automation to drive both Word and Excel.
