Export sharepoint list with lookup values using powershell

Question

I need to export the SharePoint list in CSV format for which I am using following code. It works fine except the "Project Name" Field which gives "microsoft.sharepoint.client.fieldlookupvalue" output as its a lookup column. Please suggest how can I include this lookup column in CSV.

    Add-Type -Path "LocalPath\Microsoft.SharePoint.Client.dll"

$webUrl = "https://abc.sharepoint.com/sites/abc/xyz"

$listName = "Project Phases"

$login = "[email protected]"
$password = Read-Host -Prompt "Enter password" -AsSecureString


$context = New-Object Microsoft.SharePoint.Client.ClientContext $webUrl
$context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials $login, $password

$web = $context.Web
$list = $web.Lists.GetByTitle($listName)
$query = New-Object Microsoft.SharePoint.Client.CamlQuery
$items = $list.GetItems($query)

$context.Load($items)
$context.ExecuteQuery()

$items | Select @{ L = "Project Name"; E = { ($_["Project_x0020_Name"]) } }, @{ L = "Project Phase"; E = { $_["Project_x0020_Phase"] } }, @{ L = "Active"; E = { $_["NewColumn4"] } }, @{ L = "Schedule Start Date"; E = { $_["Schedule_x0020_Start_x0020_Date"] } }, @{ L = "Schedule End Date"; E = { $_["Schedule_x0020_End_x0020_Date"] } }, @{ L = "Phase Health"; E = { $_["Phase_x0020_Health"] } }, @{ L = "Actual End Date"; E = { $_["Actual_x0020_End_x0020_Date"] } }, @{ L = "Milestone"; E = { $_["Milestone"] } } | Export-Csv -Path "C:\export_Phases.csv" -NoTypeInformation

Answer 1

You need to use this to get the lookup value from Project Name field.

([Microsoft.SharePoint.Client.FieldLookupValue]$_["Project_x0020_Name"]).LookupValue)

So the export to csv will be like this

$items | Select @{ L = "Project Name"; E = { (([Microsoft.SharePoint.Client.FieldLookupValue]$_["Project_x0020_Name"]).LookupValue)) } }, @{ L = "Project Phase"; E = { $_["Project_x0020_Phase"] } }, @{ L = "Active"; E = { $_["NewColumn4"] } }, @{ L = "Schedule Start Date"; E = { $_["Schedule_x0020_Start_x0020_Date"] } }, @{ L = "Schedule End Date"; E = { $_["Schedule_x0020_End_x0020_Date"] } }, @{ L = "Phase Health"; E = { $_["Phase_x0020_Health"] } }, @{ L = "Actual End Date"; E = { $_["Actual_x0020_End_x0020_Date"] } }, @{ L = "Milestone"; E = { $_["Milestone"] } } | Export-Csv -Path "C:\export_Phases.csv" -NoTypeInformation