I have a library containing a large amount of items.

The library contains a column of type 'Single line of text' with a code in it. The code was taken from an external system and entered manually.

There is a now a library on the SharePoint site itself containing all the codes. I need to change the library column containing the codes to type 'Lookup' referencing the new SharePoint library containing the codes.

I know this cannot be done directly.

I need a way to take the value from old code column and use it to set the new lookup column referencing the code in an external library.

I really have no idea about how to go about this.

Has anyone encountered a similar problem and hence be able to offer me some advice about how to go about this? The library have over 15,000 items, so doing this manually would be out of the question.

2 Answers 2


I would definitely go for powershell script.

First, create new lookup column in your list with data - say codeLookup.


$listWithCodes = $web.Lists["listname"];
$listWithData = $web.Lists["listname2"];

$listWithData.Items | foreach-object {
  # do caml query or something similar to get lookup 
  # to list with codes
  $codeItem = GetLookUp($_.["Code"]);
  # set lookup value
  $_.["codeLookup"] = New-Object Microsoft.Sharepoint.SPFieldLookupValue($codeItem.ID,$codeItem.Title);


Example of caml query in PS: $queryString = "1"

$query = new-object Microsoft.SharePoint.SPQuery
$query.ViewAttributes = "Scope='Recursive'"
$query.Query = $queryString
$result = $list.GetItems($query)
  • Hi there, thank you for the pseudocode. I can't say I've used powershell much before, but would of guessed that it would be the best way to go about this. I shall try my best to implement this with my limited experience in PS & SP. Jan 14, 2013 at 19:42
  • 1
    Just replace listname and listname2 with names of your lists and replace method GetLookup with calling caml to get item with specified code. I'll modify my answer after I get back to computer to help you.
    – luccio
    Jan 14, 2013 at 21:04
  • Hmmm, unfortunately what I failed to bring up was the fact that the $listWithCodes list is an external list using a BCS connection. It seems to be having trouble accessing the list through powershell saying it attempted to peform an unauthorized operation. Jan 15, 2013 at 3:39
  • Then do export of that list to xml and in powershell you can process xml file. # get xml contents; $xml = [xml](get-content $xmlPath); # get root node; $root = $xml.Offices;
    – luccio
    Jan 15, 2013 at 8:44
  • Again, thank you for your suggestion, but external lists don't seem to behave in the same as normal lists when it comes to exporting to XML. Everything I have read relating to this seems to fail and only work for normal lists.. Jan 15, 2013 at 20:25

How about creating a new lookup column in the first library and then batch updating all the items in the library by using the values stored in the old text column. And once every item is updated, delete the old text column.

  • Yes, that's pretty much what I'm trying to achieve. However my experience with SharePoint is somewhat limited. I'm trying to get more detailed advice about how to carry this out. I assume you would use powershell for batch updating? Jan 14, 2013 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.