Migrating Data to MOSS 2007

Wes Bryan

Your organization, like many others, has probably started embracing Microsoft® SharePoint® technologies and is looking for ways to migrate data from a variety of sources to the new platform.  Experience has taught you that data migrations can be time consuming and unexpectedly difficult despite planners who describe the task at hand as “straightforward”.  SharePoint data migrations are no exception, and there are a number of issues that are likely to turn this “straight” path into a winding and dangerous road. 

·         Source data types often do not share a corresponding SharePoint List counterpart.

·         Data values, especially Boolean Yes/No fields, rarely map directly to a Choice value.

·         Source data can come from a variety of sources and exist in different formats.

·         Vendor assistance to help you get information out of their products is likely to be scarce or very expensive.

·         Microsoft Office SharePoint Services (MOSS) 2007 is new and there isn’t a wealth of case studies or information available to draw upon.

Yes, a straightforward data migration rarely goes as smoothly as planned.  There are issues surrounding the planning and execution of the effort that need to be addressed in order to minimize system downtime and ensure data preservation.  Due to the focus on these surrounding issues, often the easiest and best way to facilitate the actual data transfer is overlooked.  Let’s review a number of tools and methods out there to help you though – many targeted with support for the upcoming release of MOSS 2007.

Our discussion of migrating data to MOSS 2007 will be limited to data moves originating from non-SharePoint data sources.  Microsoft has done a good job supplying tools to assist those wishing to migrate information from SharePoint Portal Server (SPS) 2003 and Windows SharePoint Services (WSS) 2.0 to the new MOSS 2007 and WSS 3.0.  While these solutions will help those who have already broken ground within their organization with an earlier SharePoint release, there are many that have been waiting for the release of MOSS 2007 before fully committing to SharePoint.  This article is for those using the new release as their first foray into production SharePoint use, and for those who may have adopted the prior release of SharePoint but still have large amounts of legacy data to move to the new SharePoint platform.

There are a large number of information workers that still work and retain critical information in Microsoft Excel based spreadsheets.  MOSS 2007 is designed with some built in functionality to assist in bringing data from these spreadsheets to SharePoint.  In fact, the Import Spreadsheet functionality will work with any spreadsheet application that is compatible with WSS.  It works fine in very simple scenarios where data typing requirements are loose and no data transformations are required.  To import a spreadsheet into a new List, click the Import Spreadsheet link in the Custom Lists group of a site content Create page.  

Figure 1

You will be asked to provide a name for your new List and to browse to the file to import.  The next dialog will be displayed within an instance of the spreadsheet application (Figure 2).  Use this dialog and the spreadsheet instance to indicate the range of cells to import.  You have the option of selecting the individual cell matrix within the spreadsheet at this time or choosing a predefined Named or List Range. 

Figure 2

The import will begin when you click the Import button from the dialog, and once it is complete you will be transitioned to the data view for the new List.   

Figure 3

We selected the Range of Cells option and simply marked the range we wanted to import.  This is a quick method to get large amounts of data into SharePoint, but it does come with some limitations; e.g. the List column names shown in Figure 3.  The columns in the new List are assigned default names and will need to be edited individually through the SharePoint List Settings - Change Column pages.  What about column data types?  You will also have to change these manually.  The import will try to interpret the column data to determine whether it matches a basic data type, but it does not support Boolean or Choice types.  Your results will be much better if you take the time to plan the import and setup your range by defining a List Range or Named Range within Microsoft Excel.  This will not correct the data type problems, but it will allow you to set the column names automatically.  This example uses Microsoft Excel 2003 and it has built-in support for creating MOSS 2007 lists directly from Excel.  After defining a range, choose the List-Publish List option from the Excel context menu to create a new List in SharePoint. 

Figure 4

Demonstrated in Figure 4, you merely need to enter the URL to the SharePoint Site where the new List should be created and provide it a name. 

As previously mentioned, the import spreadsheet functionality is fine for the straightforward scenarios, but the path does start to wind when you need more control over how the data is mapped to columns and/or need to move data from legacy applications in addition to spreadsheets.  If you need the maximum amount of control and flexibility over the import process, then you will find no substitute for authoring your own custom import application using the SharePoint Object Model to create List items.  Who has the time and money to do this?  Not everyone has access to a software developer with the time and skills needed to code a custom application.  Thankfully, there are a few companies that have traveled this road themselves and have learned how to best navigate the rough patches and sharp turns ahead.

Bamboo Solutions is one of those companies and its List Bulk Import product is one such tool that has paved this road.  It streamlines the import of data into SharePoint and allows for better control over how List data is created.  It can import both meta-data and file attachments from spreadsheets or delimited text files.  Data from legacy applications just needs to be exported into any delimited format prior to import.  Release 1.2 of the Bamboo List Bulk Import, due for a mid-November (2006) release, further speeds the import of data in legacy applications by allowing direct imports from ODBC-compliant data sources. 

The Bamboo List Bulk Import sports a wizard style interface to guide you through the import preliminaries (Figure 5).  After selecting the import source and the destination SharePoint List, you have the option of matching fields in the import source to SharePoint List columns.  This capability doesn’t sound all that sexy, but if the order of your columns just doesn’t happen to match the SharePoint List layout (or if you wish to omit certain source fields) then it is a very attractive feature. 

Figure 5

The next step is to map the values in the source data to the values you want represented in your List (Figure 6).  This feature will allow you to map directly to SharePoint Choice or Yes/No data types.  It can also be used to correct inconsistencies in the source data.  Since the Bamboo List Bulk Import appends data to an existing List rather than creating a new one, you can feed large amounts of information in incremental chunks or pull it together from more than one input source.  Each source can have a different field layout, yet be eventually mapped to a single List.

Figure 6

The final step before processing allows you to define the fields in the data that make up a unique record identifier.  This could be an employee ID field in the case of a Contact List import, or it could consist of a multiple field combination such as Last Name + First Name + Job Title. 

Figure 7

Once this unique descriptor is applied, you can instruct the Bamboo Bulk Import to do one of two things when it encounters an existing List Item that has a matching unique identifier.  The first option is to skip the duplicate record, and the second is to have the import update the existing List Item with the meta-data contained in the current import source file.  This powerful capability combined with the product scheduling features, described below, allow the Bamboo List Bulk Import to be configured to run nightly unattended updates.

The Bamboo List Bulk Import also boasts some extended functionality that aids the import process.  One of the most powerful options is that it can be configured to run on a schedule.  This is great for delaying the import processing system load until off-hours or for scheduling a recurring import.  There is also built-in logging that allows you to review the results of each import and identify records that were rejected during the run.  Got documents with your meta-data?  No problem.  Any field containing a UNC file path can be identified as an Attachment type and automatically added to List Items as SharePoint attachments.

The Bamboo List Bulk Import helps you navigate the path to a successful data migration. While it, like all off-the-shelf products, cannot cover every import scenario, it provides an easy solution to the majority of your data migration needs.  The best part is that it does all this for less than a fraction of the costs of a custom import project or its closest third-party competitor.  For more information about the Bamboo List Bulk Import product or to purchase a copy, click here. 

About the author

Wes Bryan is the Engineering Manager for Bamboo Solutions, a provider of SharePoint Web Parts and custom SharePoint development services.  He has been with Bamboo for 6 years and has over a decade of experience developing commercial software products.  Prior to his MS SharePoint involvement, his past role included creating an enterprise collaboration and information management application.  Much of the SharePoint experience he gathers comes from his team and their shared experiences with customers implementing portal solutions and Web Parts.  He can be reached via email.