Export a sharepoint list to excel with PowerShell

Peter Kiers 76 Reputation points
2021-07-04T20:59:05.233+00:00

I have code in PowerShell that export a Sharepoint list to excel. Only I have a few Sharepoint Lists that has "/" sign in it. My code can not store a file that has a / in it. Is it possible to get a Sharepoint List with a / in it and change it to the minus "-" sign to save the file. Or just remove the / in the filename.

function ExportList($listName)
{
try
{
$listItems=(Get-PnPListItem -List $listName -Fields $Global:selectProperties).FieldValues
$outputFilePath="c:\Temp\" + $listName + ".xlsx"
$hashTable=@()
foreach($listItem in $listItems)
{
$obj=New-Object PSObject
$listItem.GetEnumerator() | Where-Object { $.Key -in $Global:selectProperties } |
ForEach-Object {
if( $
.Key -eq 'Datum' )
{
$obj | Add-Member Noteproperty $.Key $.Value.ToLocalTime().ToString("dd-MM-yyyy")
}
else
{
$obj | Add-Member Noteproperty $.Key $.Value
}
}
$hashTable+=$obj;
$obj=$null;
}

    $hashtable | Export-XLSX $outputFilePath -Table -Autofit -Force
 } 
 catch [Exception] 
 { 
    $ErrorMessage = $_.Exception.Message        
    Write-Host "Error: $ErrorMessage" -ForegroundColor Red         
 } 

}

ExportList("Dit is mijn lijst / map"); <==========

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,685 questions
SharePoint Development
SharePoint Development
SharePoint: A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.Development: The process of researching, productizing, and refining new or existing technologies.
2,971 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,522 questions
0 comments No comments
{count} votes

Accepted answer
  1. Rich Matheisen 46,711 Reputation points
    2021-07-04T21:29:50.997+00:00

    You only need to replace this line:

    $outputFilePath="c:\Temp\" + $listName + ".xlsx"
    

    with this line:

    $outputFilePath="c:\Temp\" + ($listName -replace "/","-") + ".xlsx"
    

    There are more characters that should be avoided in file and directory names than just a forward slash!

    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.