HowTo: Get Folders with Special Characters

In this post, we will be generating a report listing all folders which contain special characters. Personally, I do not understand why there are ever special characters in folder names. These characters can cause issues with other applications. Prime examples are the backslash ‘\’ or forward slash ‘/’ which can cause path issues with various applications and when exporting. Also, users are prompted stating there are invalid characters in the name and much approve the name.

All of the ProjectWise related cmdlets are available using the PWPS_DAB module. At the time of this post, I am using version 24.0.1. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.

• Select-PWSQL
• Get-PWFoldersByGUIDs
• Get-MyPWCurrentDatasource
• New-XLSXWorkbook

Run SQL Query to Get Folders

We will run a SQL query to return ALL folders containing special characters.

Special Characters: < > : / \ | ? * “

if($sqlResults = Select-PWSQL "SELECT o_projguid GUID, o_projectno ID, o_projectname NAME 
    FROM dms_proj 
    WHERE o_projectname LIKE '%<%' 
        OR o_projectname LIKE '%>%'
        OR o_projectname LIKE '%:%'
        OR o_projectname LIKE '%/%'
        OR o_projectname LIKE '%\%'
        OR o_projectname LIKE '%|%'
        OR o_projectname LIKE '%?%'
        OR o_projectname LIKE '%*%'
        OR o_projectname LIKE '%""%';"){

    Write-Host "$($sqlResults.Rows.Count) folders returned." -ForegroundColor Green
} else {
    throw "No data returned."
}

Get ProjectWise Folders By GUIDs

In the following we will be getting all affected folders by the returned folder GUIDs. This will include the folder fullpath which can take a little time to obtain.

Write-Host "Getting ProjectWise folders." -ForegroundColor Cyan
$pwFolders = Get-PWFoldersByGUIDs -FolderGUIDs $sqlResults.GUID
$pwFolders.GetFullPath() | Out-Null

---

Create and Populate Datatable

In the following we will be create a datatable to store the folder data. This table will include the ProjectID, name, Fullpath and URN.

$dtFolders = [Data.Datatable]::new()
$dtFolders.Columns.AddRange(@(
    'ProjectID', 'Name', 'FullPath', 'URN'
))

foreach($pwFolder in $pwFolders){ # break
    $dtFolders.Rows.Add($pwFolder.ProjectID, $pwFolder.Name, $pwFolder.FullPath, $pwFolder.ProjectURN) | Out-Null
}

# Use the current datasource name as the name of the new datatable.
$dtFolders.TableName = Get-MyPWCurrentDatasource

---

Export Data to Excel

Finally, we will export the folder data to an Excel spreadsheet.

Write-Host "Exporting folder data '$($dtFolders.TableName)'." -ForegroundColor Cyan
New-XLSXWorkbook -InputTables $dtFolders -OutputFileName $OutputFileName

---

Experiment with it and have fun.

Hopefully, you find this useful. Please let me know if you have any questions or comments.  If you like this post, please click the Like button at the bottom of the page.