In this post I’m going to show you a workaround to an issue that causes data loss when exporting lists with lookup columns to another site.
We have 2 lists in a SharePoint 2013 site, one with a lookup column to the other, and we need to export them to another site without loosing any data.
In the List Settings page of our lists we can use the “Save as Template” functionality to export them.
The list templates are successfully created so we can load them to the destination site collection and recreate the lists from them in the new site.
Note: the destination site language must be the same as the source one
However looking closer to List B we note that all the data in the lookup column has been lost…
This happens because the lookup column points to List A GUID, not its path or name…
The GUID of List A in the destination site is different from that of the same list in the source site, so the lookup column is now pointing to a list that does not exist in the new environment.
Unfortunately we can’t change the lookup column definition in the destination site and get our data back because the “Get information from: ” section is locked…
So, how to solve this problem?
We can change the list template manifest file and set the correct list GUID or path for the lookup column.
Let’s see how to do that in detail…
1. Change the list template file extension
Download the template file of our list with the lookup column (in our example the file is ListBTemplate.stp) and change its file extension from “stp” to “cab”.
2. Extract the files contained in the list template cab package
You can use WinRAR or WinZip to extract the files contained in the cab file
3. Change the list template manifest file
Open the manifest.xml file contained in the list template extracted files (in our example it is the only file) and replace the lookup column list GUID (in our example the GUID of list A in site A, f8088b28-1434-4b51-96d3-a36e945e5146) with the new list GUID or relative path (in our example the GUID or relative path of list A in site B, 265bffc8-dcc9-48e3-99e4-d3ad1d5e2260 or Lists\List A).
4. Recreate the list template package
Now that we have changed the content of our manifest file we need to recreate the cab package. To do that we ca use the makecab command in the command prompt using the following syntax:
makecab "<source file>" "<destination file>"
In our example the specific command is as follows:
makecab "C:\List Templates\ListBTemplate\manifest.xml" "C:\List Templates\ListBTemplate\ListBTemplate.cab"
Once created the cab file we can change its file extension to “stp”, reload it to the destination site collection and recreate our List B.
This is how our new list with the lookup column should look like:
You can see that our lookup data is there again without any loss.