0

We have about 5,000 Word documents in which the first page is a table created in Word with project information (client, owner, construction date, budget, location, size etc.)

Is there a way to import that table into SharePoint and obtain a reasonable facsimile of the table?

Is it then possible to have the data in the 5,000 tables indexed/linked for searching and reporting?

1

Microsoft InfoPath has an Import from Word feature (File > New > Convert Existing Form).... However, I suspect this won't give you what you want.

The proper solution here would be to write a small C# console app to iterate over each document and use OpenXML to extract the data from the table and then write this into a more suitable structure (e.g. SQL database or SharePoint list(s)).

This article will help you get on the right track: https://stackoverflow.com/questions/11240933/extract-table-from-docx

To write the data into a SQL database will be easy (you probably already have code for this) and is the preferred approach if you want to report on it with SQL Reporting Services (SSRS).

To write the data into SharePoint list(s) then you'll ideally use the client API for this so that you don't need to run the code on a SharePoint server - and instead connect to it remotely.

Please let me know if this helps or if you have any other questions here.

1

Looping through the Word docs programmatically, as spmaster_jack suggested is probably your best bet. Depending on your level of comfort with programming languages and IDEs, you might be better off creating an Excel macro to loop through the Word documents, copying their table info to the current spreadsheet.

This macro code may help point you in the right direction (taken from this question)

This macro should be inserted into Excel (not Word) and put into a standard macro module rather than into the worksheet or workbook event code modules. To do this, go to the VBA (keyboard Alt-TMV), insert a macro module (Alt-IM), and paste the code into the code pane. Run the macro from the Excel interface as you would any other (Alt-TMM).

Sub ImportWordTable()
Dim wdDoc As Object
Dim wdFileName As Variant
Dim TableNo As Integer 'table number in Word
Dim iRow As Long 'row index in Excel
Dim iCol As Integer 'column index in Excel

wdFileName = Application.GetOpenFilename("Word files (*.doc),*.doc", , _
"Browse for file containing table to be imported")

If wdFileName = False Then Exit Sub '(user cancelled import file browser)

Set wdDoc = GetObject(wdFileName) 'open Word file

With wdDoc
TableNo = wdDoc.tables.Count
If TableNo = 0 Then
MsgBox "This document contains no tables", _
vbExclamation, "Import Word Table"
vElseIf TableNo > 1 Then
TableNo = InputBox("This Word document contains " & TableNo & " tables." & vbCrLf & _
"Enter table number of table to import", "Import Word Table", "1")
End If
With .tables(TableNo)
'copy cell contents from Word table cells to Excel cells
For iRow = 1 To .Rows.Count
For iCol = 1 To .Columns.Count
Cells(iRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
Next iCol
Next iRow
End With
End With

Set wdDoc = Nothing

End Sub

If you were starting anew with these Word documents, your best bet would be to use a Word feature called Quick Parts to link the information in those tables to columns of a SharePoint document library where the documents would be stored. Then simply filling out those tables in the Word documents would populate the column information in SharePoint (and contrariwise, updating the columns in SharePoint would update the information in the tables).

Also, be prepared to encounter the SharePoint 5000-item list view threshold. Apply column indexing to any columns on your list/library that you plan on sorting, filtering, or grouping by. Indexing any columns after a list has exceeded the threshold is painful.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.