Search code examples
powershellsharepointtrim

Removing text before specific character in PowerShell script

I am exporting the items within a SharePoint list to an array then putting them into an email within a table. The SharePoint name field is giving additional characters (user ID I think) before the display name which I cannot trim/remove before its output to the array then added to email.

I have tried using a few variations of the below but I do not think it works for an already created array:

(Get-Content $outputfile) |
    Select-String -Pattern '*#' -NotMatch |
    Out-File $outputfile

as well as:

(Get-Content $outputfile) |
    Where-Object { $_.Trim() -ne "" } |
    Set-Content $outputfile

The script looks like the below:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

$web = Get-SPWeb -Identity "https://sharepointsite.com"
$list = $web.Lists["testlist"]

#Array to Hold Result - PSObjects
$ListItemCollection = @()

$list.Items | foreach {
    $ExportItem = New-Object PSObject 
    $ExportItem | Add-Member -MemberType NoteProperty -Name "Name" -Value $_["Name"]
    $ExportItem | Add-Member -MemberType NoteProperty -Name "Reason" -Value $_["Reason"]
    $ExportItem | Add-Member -MemberType NoteProperty -Name "Example" -Value $_["Example"]
    $ExportItem | Add-Member -MemberType NoteProperty -Name "Created" -Value $_["Created"]

    $ListItemCollection += $ExportItem
}

$web.Dispose()

$emailbody = $(cat C:\temp\emailbody.txt) + $ListItemCollection

#Email formatting
$style = "<style>BODY{font-family: Arial; font-size: 10pt;}"
$style = $style + "TABLE{border: 1px solid black; border-collapse: collapse;}"
$style = $style + "TH{border: 1px solid black; background: #dddddd; padding: 5px; }"
$style = $style + "TD{border: 1px solid black; padding: 5px; }"
$style = $style + "</style>"

$SMTPServer = "mail.com"
$EmailFrom = "[email protected]" 
$EmailTo = "[email protected]"
$EmailSubject = "Test Email"

$Message = New-Object System.Net.Mail.MailMessage $EmailFrom, $EmailTo
$Message.Subject = $EmailSubject
$Message.IsBodyHTML = $true
$message.Body = $emailbody + ($ListItemCollection | ConvertTo-Html -Head $style | Out-String)
$SMTP = New-Object Net.Mail.SmtpClient($SMTPServer)
$SMTP.Send($Message)

Example table output shows:

Name          Reason              Example         Created
64;#Test User   testing reason    testing example   17/04/2019 4:28:33 a.m.
105;#John Smith test for reason   more testing for example more testing for example more testing for example    17/04/2019 4:29:24 a.m.
Solution

  • If the question is just about removing the unwanted characters prefixing the user names, you can simply strip these off using 

    $ExportItem | Add-Member -MemberType NoteProperty -Name "Name" -Value ($_["Name"] -replace '^\d+;#')

    By the way, if you're on PowerShell 3.0 or better, there is an easier way to construct the objects

    $ListItemCollection = $list.Items | ForEach-Object {
    [PSCustomObject]@{
        'Name'    = $_["Name"] -replace '^\d+;#'
        'Reason'  = $_["Reason"]
        'Example' = $_["Example"]
        'Created' = $_["Created"]
    }

    Or even (untested)

    $ListItemCollection = $list.Items | Select-Object @{Name = 'Name'; Expression = {$_["Name"] -replace '^\d+;#'}},
                                                  Reason, Example, Created

    Hope this helps