This weekend, I have been scripting powershell to populate several SharePoint relational lists. Data is imported from excel (csv) files in this case.
The whole piece is for an application I am developing. However, I had some difficulties with lookup lists. I refined a function (highlighted in blue) which i am to share here.
So for example, calling code (highlighted in orange) can get the special format of [ID];#[NAME]ready for importing to the field.
$spweb = Get-SPWeb $weburl
try
{
# CSV path/File name
$contents = Import-Csv $csvPath
$lists = $spweb.Lists;
$list = $lists[$listName];
$managerList = $lists["Manager"];
# Delete existing List Data, If not required remove this line
try
{
$list.get_items() | % { $_.Delete() }
}
catch
{
#ignore the list could be empty
}
# Iterate for each list column
foreach ($row in $contents)
{
$item = $list.items.add()
# Check if cell value is not null
if ($row.EmployeeLanId -ne $null)
{$item["EmployeeLanId"] = $row.EmployeeLanId}
$item.update()
if ($row.EmployeeEmail -ne $null)
{$item["EmployeeEmail"] = $row.EmployeeEmail}
$item.update()
if ($row.ManagerLanId -ne $null)
{$item["ManagerLanId"] = GetFormattedLookupValue $spWeb "Manager" $row.ManagerLanId "ManagerLanId" }
$item.update()
}
}
catch
{
write-host “Execution stopped due to: “+$_.Message -foregroundcolor Red -backgroundcolor Yellow
}
finally
{
$spweb.Dispose();
}
try
{
# CSV path/File name
$contents = Import-Csv $csvPath
$lists = $spweb.Lists;
$list = $lists[$listName];
$managerList = $lists["Manager"];
# Delete existing List Data, If not required remove this line
try
{
$list.get_items() | % { $_.Delete() }
}
catch
{
#ignore the list could be empty
}
# Iterate for each list column
foreach ($row in $contents)
{
$item = $list.items.add()
# Check if cell value is not null
if ($row.EmployeeLanId -ne $null)
{$item["EmployeeLanId"] = $row.EmployeeLanId}
$item.update()
if ($row.EmployeeEmail -ne $null)
{$item["EmployeeEmail"] = $row.EmployeeEmail}
$item.update()
if ($row.ManagerLanId -ne $null)
{$item["ManagerLanId"] = GetFormattedLookupValue $spWeb "Manager" $row.ManagerLanId "ManagerLanId" }
$item.update()
}
}
catch
{
write-host “Execution stopped due to: “+$_.Message -foregroundcolor Red -backgroundcolor Yellow
}
finally
{
$spweb.Dispose();
}
<#
GetFormattedLookupValue - return a string in the format id;#name
param [object] $spWeb sharepoint web
param [string] $lookupListName path of the csv file
param [string] $lookupListName the name of the list
param [string] $fieldName url of the site
return [string] id;#name
#>
function GetFormattedLookupValue([object]$spWeb, [string]$lookupListName, [string]$lookupListName, [string]$fieldName)
{
$spweb = Get-SPWeb $weburl
try
{
$lists = $spweb.Lists;
$LookupList = $lists[$lookupListName];
try
{
$LookupItem = $LookupList.Items | Where-Object { $_.Item($fieldName) -eq $valueToLookFor }
$Lookup = ($LookupItem.ID).ToString() + ";#" + ($LookupItem[$fieldName]).ToString()
}
catch [System.Exception]
{
write-host “Problem getting lookup: “+$_.Message -foregroundcolor Red -backgroundcolor Yellow
}
}
catch [System.Exception]
{
write-host “Problem adding lookup: “+$_.Message -foregroundcolor Red -backgroundcolor Yellow
}
finally
{
$spweb.Dispose();
}
return $Lookup
}