Exploring Import Options 4-5
, importing the Excel worksheets to Access 2007 and then exporting the Access database to a SharePoint 2007 list turned out not to be a viable option, because it involved creating and managing yet another database. After that, you still need to reformat the Excel data to get it into a format suitable for SharePoint. While Access does facilitate connectivity to SharePoint and provide a wealth of reporting features, that convenience didn't merit the additional design and testing efforts. The design decision was: Importing Excel worksheets to Access 2007 and then exporting the Access database to a SharePoint 2007 list adds an unnecessary level of complication and management.
Regarding Option 5—finding a third-party product to import the data—a company named Bamboo Solutions offers a product called List Bulk Import that can migrate data and documents into SharePoint 2007 from legacy databases, spreadsheets, and from older versions of SharePoint. In other words, this product eliminates the need to use methods such as those already described.
The List Bulk Import product proved to operate as advertised, but like SharePoint 2007, it expects data to be arranged into rows rather than columns. In a positive light, however, the List Bulk Import product has a sophisticated interface for mapping values on the spreadsheet to the SharePoint list, and provides options that can check for duplicate values in the SharePoint list, update existing data, and create scheduled import jobs. You can save Import Templates in xml files for reuse or editing. The product also produces a log file that provides an audit trail so you can see whether imports succeed or fail. See the Bamboo Solutions web site for additional information. The final design decision was: Bamboo Solutions' List Bulk Import offers some powerful features that make it a better solution than Options 1-4.
The Bamboo List Bulk Import does not create the SharePoint 2007 list; you need to create the list in advance. This actually proved to be advantageous. Creating the list manually helped ensure that column headings were fully descriptive and unique, and provided an opportunity to determine the format of the data, numeric precision, whether numbers were displayed as percents, and other formatting options. You can use content types in SharePoint 2007, storing them in the gallery for the site collection, which means new columns can be added or existing ones changed, and the changes pushed out to lists using the content type.
Using Excel Macros to Prepare Your Data for Import into SharePoint
|Author's Disclaimer: I worked with Bamboo Solutions to scope out the addition of a feature that allows users to define a range of cells that is transposed during the import process. They added this feature to their product (for a modest fee of course). So look for the cell transposition feature in upcoming versions of the List Bulk Import product.
As discussed above, standard financial spreadsheets typically organize data in a way that is "unfriendly" to SharePoint lists (see Figures 1
for an example). Unless the organization is willing to change its financial spreadsheets (unlikely), you'll need to manipulate the data to prepare it for importing. Fortunately, Excel includes a powerful macro engine that you can use to make such changes easily.
The following list shows some tasks that will be required, regardless of which import option you decide to use, and some examples of macros that show how you can automate such tasks:
- Transpose data from the standard row format to place data in columns. The upcoming version of the List Bulk Import tool will offer this functionality. If you're not using that, you'll need a macro to transpose data.
- After the transposition, you'll probably want to move some column titles to place all headings in the same rows (see Listing 1).
- Financial spreadsheets often contain text in cells that should contain numbers. The classic example is the inclusion of "NA" in cells. SharePoint will not accept text when it expects the cell to contain a number so you'll need to change text to numbers (see Listing 2)
As you can see, there are some tricks involved in pulling data from Excel spreadsheets into SharePoint 2007 lists. This article described several different methods, all of which were examined in the context of real-world requirements. Although we eventually decided to use a third party product, companies whose data is less complex, changes rarely, or who don't require audit logs may opt for one of the simpler solutions described here.
|Author's Note: Thanks to Ralph Hefner who provided the code listings for this article.