Challenge:

I recently needed to move a list from SharePoint 2007 to SharePoint 2010.  I wanted the destination list to have the exact content as the source list including structure, list items, and attached files.  Since SharePoint does not have an STSADM.EXE command line tool to accomplish this, it needs to be done manually.  What are our options?

Solution:

After doing some research, I discovered that there are a few ways to accomplish this task.  You can migrate a single list from SharePoint 2007 to SharePoint 2010 using one of four methods:

1. Migrate a single list from SharePoint 2007 to 2010 using a list template
2. Migrate a single list from SharePoint 2007 to 2010 using an Access table
3. Migrate a single list from SharePoint 2007 to 2010 using the detach database method
4. Migrate a single list from SharePoint 2007 to 20110 using PowerShell

I’ll summarize and show how each of these methods works in this article.

Method 1 – Migrate a single list from SharePoint 2007 to 2010 using a list template

The first method to consider is creating a list template from SharePoint 2007 and bringing it into SharePoint 2010. But you will likely be disappointed to learn that SharePoint 2010 will not recognize this template, so you cannot create any list from that template.  A workaround for this problem is posted on Tom’s Random Ranting blog, showing how you can easily modify the manifest file that gest created every time you create a template.

Tom’s basic method is:

• Extract the contents of the STP file (it’s really just a CAB file)
• Edit the manifest.xml file, changing the ProductVersion element from 3 to 4
• Repackage the STP file

It is quick way to bring a list over, but there is one important limitation: if your source list has many items (thousands), you might not be able to copy the entire contents of the list.

Method 2 – Migrate a single list from SharePoint 2007 to 2010 using an Access table

The second way to migrate a list from 2007 to 2010 is to use Access. Basically, you need to export a MOSS 2007 list to an Access table, and then import to SharePoint 2010:

Step 1:  Open source list with Access

Step 2: Select Export a copy of the data to a new database

Step 3: The SharePoint list will now be imported into Access.  You can add or modify columns in this mode to select what you need.

Step 4: From Access, select External Data tab in the Ribbon, and select SharePoint List in the Export section. Enter a SharePoint 2010 site address and select OK.

Your result should appear as follows:

You might be wondering if this method also works with a Document Library since the Document Library doesn’t have the menu Action -> Open for Access!  Yes, of course it does, but for a Document Library, you need to use the similar “Open with Windows Explorer” menu action.

Step 1: Open both Document Libraries with Window Explorer

Step 2: Select all the documents and folders that you need, and select Copy.

Step 3: Paste the data into the destination folder.

And the result is:

Limitation

You will notice that one of the limitations of this migration method is that the Modified Date, Created Date, Modified By, and Created By columns do not retain their values from the source list.  You also need to have Microsoft’s Access 2010 in order to use this method.

Method 3 – Migrate a single list from SharePoint 2007 to 2010 using the detach database method

In this method, we will try to migrate using an STSADM command that is supported SharePoint 2007.  All we have to do is use the Export and Import command to migrate a list.

Step 1:  Migrate the entire SharePoint site that contains the list to be exported from 2007 to 2010.  Please refer to an earlier post in the Cookbook series, How to Migrate a SharePoint 2007 Site to SharePoint 2010 Using Database Attach, for the exact steps on how to do this.  You might have to create a new temporary site in SharePoint 2010 in order to migrate the site, but the site can then be removed after the desired list has been migrated.

Step 2: Once the site has been migrated to SharePoint 2010 server, let’s try to move the sample Tasks list.  From Central Admin -> Backup, select Restore -> Export a site or list:

In the selection drop down box,select the list that you want to move:

At this point, you have exported the desired list to a folder on your SharePoint server, and you are now ready to import that List to the correct destination.

Step 3. Next, import the Tasks list to your final destination site.   SharePoint 2010 does not provide a User Interface in Central Admin for Granular Restore operations; therefore, you have to use PowerShell to accomplish this task:

And below is the result:

Method 4 – Migrate a single list from SharePoint 2007 to 2010 using PowerShell

Lastly, we will try to use PowerShell to Export/Import lists. We know that Microsoft has released a version of Windows PowerShell 1.0 that works with SharePoint 2007,  and which can be downloaded here.  After the installation has completed, we can write a PowerShell script to try to accomplish our goal of exporting a list.

The basic steps are:

• Run a shell script to export a list to a DAT file
• Change this DAT file to CAB, and extract this file so that you can access SystemData.xml in the CAB file, and modify version information there.
• Remake the CAB file and change the extension to .CMP.
• Use PowerShell in SharePoint 2010 to import the list from the .CMP file.

Step 1: Launch PowerShell and load SharePoint assemblies to the program.  Refer to this blog post Nick Grattan in order to prepare your PowerShell for SharePoint 2007: http://nickgrattan.wordpress.com/2007/09/03/preparing-powershell-for-sharepoint-and-moss-2007/:

Let’s write a script to export a SharePoint 2007 List. I will follow an example from Kashish Sukhija’s Blog here: http://blogs.sharepointdevelopers.info/2010/05/sharepoint-2010-deployment-using.html.

[System.Reflection.Assembly]::Load("Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c") 

[System.Reflection.Assembly]::Load("Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c") 

$spsite=[Microsoft.SharePoint.SPSite] ("http://cleanmoss/test")

$spweb=$spsite.OpenWeb()

$openList=$spweb.Lists["Tasks"]

$exportObject = New-Object Microsoft.SharePoint.Deployment.SPExportObject

$exportObject.Type = [Microsoft.SharePoint.Deployment.SPDeploymentObjectType]::List

$exportObject.IncludeDescendants = [Microsoft.SharePoint.Deployment.SPIncludeDescendants]::All

$settings = New-Object Microsoft.SharePoint.Deployment.SPExportSettings

$settings.ExportMethod = [Microsoft.SharePoint.Deployment.SPExportMethodType]::ExportAll

$versions = [Microsoft.SharePoint.Deployment.SPIncludeVersions]::All

$settings.IncludeVersions = $versions

$settings.IncludeSecurity = [Microsoft.SharePoint.Deployment.SPIncludeSecurity]::All

$settings.OverwriteExistingDataFile = 1

$settings.SiteUrl = $spweb.Url

$exportObject.Id = $openList.ID

$settings.FileLocation = "C:TempBackupRestoreTemp"ExportList-"+ $openList.ID.ToString() +".DAT"

$settings.BaseFileName = "

$settings.FileCompression = 1

$settings.ExportObjects.Add($exportObject)

$export = New-Object Microsoft.SharePoint.Deployment.SPExport($settings)

$export.Run()

After you have run this shell script you will have your export list in the c:tempBackupRestoreTemp folder.

Step 2:  Next, all we have to do is change this DAT file to CAB, extract the file so that you can access SystemData.xml in the CAB file, and modify the version information there. Open the SystemData.xml file in a text editor, and change Version=”12.0.0.0″ to Version=”14.0.0.0″, and Build=”12.0.0.6514″ to Version=”14.0.4762.1000″.

Step 3:  You will have to create a new CAB file again after making the changes above.  Here is a reference showing how to make a CAB file using the Makecab.exe command: http://msdn.microsoft.com/en-us/library/dd583149(office.11).aspx.  This is sample content to create the CAB:

.OPTION EXPLICIT     ; Generate errors 

.Set CabinetNameTemplate=ListTasks.cab       

.set DiskDirectoryTemplate=CDROM ; All cabinets go in a single

.Set CompressionType=MSZIP;** All files are compressed in cabinet files

.Set UniqueFiles="OFF"

.Set Cabinet=on

.Set DiskDirectory1=ListTasks.CAB

; Include all files need to be presented in Cab.

manifest.xml

ExportSettings.xml

Requirements.xml

RootObjectMap.xml

SystemData.xml

UserGroup.xml

ViewFormsList.xml

00000000.dat

00000001.dat

00000002.dat

00000003.dat

00000004.dat

00000005.dat

00000006.dat

00000007.dat

00000008.dat

00000009.dat

0000000A.dat

0000000B.dat

0000000C.dat

Step 4: Finally, we can use PowerShell on SharePoint 2010 server and run the Import-spweb to import the list.

Result:

In summary, if you have a need to just import a SharePoint list from an existing SharePoint 2007 farm to a SharePoint 2010 farm, there are several ways to accomplish this task.  Which method you should use will depend on the tools that you have available, and your familiarity with each of them.  Note: There are also third-party tools available to help you with migrating sites and content in SharePoint and, for list migration specifically, you may wish to look at Bamboo’s own List Bulk Import product.

Notes:

• Using the last two methods described, we will be able to retain the original values of Modified Date, Created Date, Created By, and Modified By from the source list. Make sure to specify the parameter -IncludeUserSecurity in your import command.
• Most of the steps we have shown in this article will require you to have Farm administration rights in order to run the necessary import and export commands.
• If your source list contains any custom columns, you might have to install that feature on the destination SharePoint 2010 farm before doing the migration.

See Also:

• SharePoint 2010 Cookbook: How to Migrate a SharePoint 2007 Site to SharePoint 2010 Using Database Attach [Bamboo Nation]
• Using Powershell Script to export & Import SPList [Kashish Sukhija’s Blog]
• Importing SharePoint 2007 list templates (STP) into SharePoint 2010 [Tom’s Random Ranting]
• Preparing PowerShell for SharePoint and MOSS 2007 [Nick Grattan’s SharePoint Blog]
• Packaging and Deploying Web Parts for Microsoft Windows SharePoint Services [MSDN]