RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Business Intelligence for the Average Joe: Getting Excel Data into SharePoint 2007 Lists : Page 2

Follow this case study to unlock the data your company has in isolated spreadsheets by publishing it in SharePoint lists.

Exploring Import Options 1-3
We ruled out Option 1 (the cut and paste option) immediately, because there were thousands of cells to import for each company. That led to the next design decision: Cutting and pasting to SharePoint 2007 lists is too labor intensive and prone to error.

Exploring Option 2, SharePoint 2007 can create a list based on the contents of a spreadsheet (by accessing the Create command and then choosing Import Spreadsheet from the Custom Lists section), but that has several limitations. First, it's a one-time import so there's no easy way to add additional rows at a later time, or update existing content when it changes in the spreadsheets (which are updated monthly). Second, SharePoint 2007 wants the source data to be organized in rows, but the typical financial spreadsheet is organized in columns. Figure 1 shows a typical spreadsheet, while Figure 2 shows the results of trying to import that spreadsheet into SharePoint 2007. That failure led to another design decision: The Import Spreadsheet function in SharePoint 2007 doesn't work with standard accounting and financial spreadsheet layouts, and is a one-time process, so it doesn't support future updates to the spreadsheets.

Figure 1. Typical Spreadsheet Data: The typical financial spreadsheet organizes data in columns.
Figure 2. Imported Spreadsheet: SharePoint's Import Spreadsheet function doesn't work well with column-centric spreadsheets.
Fortunately, there's a workaround to the import problem. If you simply copy the data in one spreadsheet onto one worksheet, and then use the Paste Special option to paste it to another worksheet, you can use the Transpose option, which switches column data to row data, as shown in Figure 3. You'll still need to apply a few changes to make the data SharePoint friendly. You can see one such change in Figure 3, which shows the headings in row A moved to row B. If you don't make this change, SharePoint assumes that the only headings are in row A and everything else is data. Figure 4 shows the new SharePoint list created by importing the massaged data. The resulting design decision was: Transposed and reformatted data from standard financially oriented spreadsheets (with dates as column headings) can be imported effectively into a SharePoint 2007 list.

Figure 3. Transposed Data: Using Excel's Paste Special option with the Transpose option results in row-centric data.
Figure 4. Reformatted Data: Moving all header text to one row (row 2 in this example) improves the import into SharePoint 2007.
Figure 5. Improved Import: Importing transposed and "massaged" Excel data gives a satisfactory result.
In Figure 5, each row now corresponds to a date, and each column heading corresponds to a financial value, such as Actual Revenue or Budget/Plan Revenue. However, because the original spreadsheet used standard financial formatting, the list headings aren't fully representative of the data they contain; instead, the headers have numbers added to give them unique values (Actual2, Budget/Plan3, Forecast4, Actual5, etc.). Additionally, what were the originally the section headers in the nicely formatted original spreadsheet have been imported as separate columns, containing no data.

Figure 6. Imported Column Types: After importing a spreadsheet, the List Settings show the column types based on SharePoint's interpretation of the data.
A quick look at the List Settings for the list (see Figure 6) shows that SharePoint 2007 made some incorrect assumptions about the imported data. For example, the import assigns both the dates and the Revenue Forecast information in the spreadsheet a column type of "Single line of text." So after some experimentation, it became clear that the data would require quite a bit of formatting before it could be presented properly in SharePoint 2007 using the Import Spreadsheet method. For example, the blank cells in the spreadsheet should contain "0" to force SharePoint to treat those cells as numeric rather than text data, and the column headings should be made unique and more descriptive prior to import. The resulting design decision was: Significant formatting changes are required after the transposition of data to ensure that SharePoint creates a list with properly labeled and formatted columns.

The next issue with this process (using the Import Spreadsheet feature in SharePoint 2007) comes when new data is added to the source spreadsheet. There is no connection from the initial source spreadsheet to the new SharePoint list. A new list could be created every time the source data changes, but then the SharePoint administrator is faced with administrative headaches: are the old lists deleted or kept for reference, and how will users find the latest set of data? If the spreadsheet only changes once a year, this could be a viable option, but the client's spreadsheets tended to change once a month, meaning that new data would periodically need to be manually cut and pasted from the new spreadsheet to the existing SharePoint list. Once again the issues of labor and possible human error come into play, which simplifies this next design decision: The Import Spreadsheet method is not a good option when the source spreadsheet changes frequently and you need to import the new or modified data to the same SharePoint list.

Figure 7. Transposed and Formatted Data in Table Format: Results of selecting the Format as Table button in Excel 2007 (after installing an add-in from Microsoft).
Option 3, converting the data in the spreadsheet to a table and then exporting to SharePoint 2007, addresses one of the weaknesses in Option 2. By converting the range to a table and then exporting to SharePoint 2007, you can establish a connection between the spreadsheet and the SharePoint list. However, as you'll see below, you need a special Excel 2007 download from Microsoft to make this work. In addition, you must save the file in Excel 2003 format to have synchronization functionality.

Microsoft publishes complete instructions on the process of converting a range to a table, but the process essentially involves selecting the range, and then clicking on the Format as Table button on the Home tab of the Ribbon. Figure 7 shows the sample set of data transposed, the headings from row 1 moved to row 2, and then converted to a table.

To now publish this data to SharePoint 2007 and enable it to synchronize when changes are made, you need to download an Excel 2007 add-in from Microsoft, which you'll find in the article "Excel 2007 Add-in: Synchronizing Tables with SharePoint Lists."

Author's Note: This article provides some insight into why Microsoft "deprecated" this functionality: Microsoft recommends that users instead use Access 2007 to write data to SharePoint lists and for using lists offline.

After you install and configure the download add-in in Excel Options, you'll find a new button ("Publish and allow Sync") on the Design tab on the Office Ribbon that lets you publish and sync with SharePoint 2007. Now you can publish a read-write list to SharePoint 2007, but only if the workbook is saved in Excel 2003 compatibility mode. If you save the document in Excel 2007 format, the publish is a one-time, non-synchronizable event. Helpful messages in Excel will alert you to the loss of functionality if you try to save in the "wrong" format.

Figure 8. Imported List: Here's how the newly formatted table looks when imported to SharePoint.
Now you can access the Design tab in Excel 2007 from your newly formatted table, and then click the Publish and allow Sync button. You'll need to enter the SharePoint 2007 site name and the list name to begin the publishing process. Figure 8 shows the results of exporting this table to SharePoint 2007.

For usability's sake, modify the resulting columns in SharePoint to have appropriate and complete names. For example, you could rename column 1 to Date and the Actual column to Revenue Actual.

Figure 9. List with Renamed and Deleted Columns: After renaming and deleting unneeded columns, here's the resulting list in SharePoint.
Finally, you can delete the empty Revenue column. Figure 9 shows the results of these changes in the SharePoint 2007 list.

To synchronize these changes back to Excel (saving in Excel 2003 format, don't forget!), return to the spreadsheet, right click on a cell in the table, and select "Table, Synchronize with SharePoint." The table in Excel will adjust correspondingly. You can also make changes in the Excel spreadsheet. One nice feature is that if unsynchronized changes exist in either the list or the spreadsheet, the next user who opens the spreadsheet will be given the option to "load the pending changes." That became the next design decision: By installing the "Synchronizing Tables with SharePoint Lists" Excel add-in, and then saving the spreadsheets in Excel 2003 compatibility mode, you can establish a two-way synchronization with a SharePoint 2007 list.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date