Matching value from single line of text column to lookup column

Question

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.

Answer 1

I would definitely go for powershell script.

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

Pseudocode:

$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);
}

$listWithData.Update();
$web.Dispose();

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)

Answer 2

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.