truncate a text object's output

I’ve got a function that extracts data from AD which contains information in the “info” field (Telephones>Notes in the ADUC GUI). I keep training records in a standardized date format on the first row of each record: 2014 Training Completed: xx/xx/xxxx I also have other minor data in that field. The following script works fine for extracting and formatting the data for Excel. However in Excel, I need to run a =LEFT(A1,35) to only include that relevant first line of text. Which also works, but involves more work after the export from POSH has happened.

[blockquote]2014 Training Completed: xx/xx/xxxx
Some other text, some more text[/blockquote]
results in
[blockquote]2014 Training Completed: xx/xx/xxxx[/blockquote]

My question: is there a way to take the $ data and truncate it by the left most 35 characters as part of the function? Output the same data, just have POSH (v4) handle the truncation as part of the function, saving the extra step from having to be done in Excel?

function GET-REPORTS {
    [CMDLETBINDING(SupportsPaging = $true,
                SupportsShouldProcess = $true)]
    BEGIN {
    $DATE = Get-Date -format 'yyMMdd_hhmm'
    $GETREP = Get-ADUser -filter * -Properties * | where {$ -like '*Training*' -and $_.enabled -eq $TRUE} | select Name,Info,PasswordLastSet,LastLogonDate
    } #end BEGIN
    $GETREP | Export-Csv "$CSVPATH\$DATE-TRAINING_REPORT.csv" -NoTypeInformation
    } #end PROCESS

    END {
    } #end END


First thing, since you’re only selecting four properties, you may want to change -Properties * to -Properties Info,PasswordLastSet,LastLogonDate. Think about it, you’re returning all the user’s properties, and then only using three, that aren’t included by default.

The more you can do in PowerShell the better, because really, who wants to be an Excel superstar? You need to employ the use of calculated properties. Search around for some examples, as well, take a look at my example below. Good luck; this should get you the proper results.

PS C:\> $x = '2014 Training Completed: 12/05/2014 Hello Freakshow!'
PS C:\> $x.Substring(0,35)
2014 Training Completed: 12/05/2014

PS C:\> Get-ADUser username -Properties Info,PasswordLastSet | Select-Object Name,@{N='Info';E={($_.Info).Substring(0,35)}},PasswordLastSet

Name           				Info                                    PasswordLastSet
----                    	----                                    --------------- #These are supposed to be dashes
Lastname, Firstname			2014 Training Completed: 12/05/2014     9/16/2014 9:49:47 AM

Brilliant! That’s a technique I’ll add to my workflow. That gave the desired results without having to modify data in Excel. I agree with your comment about only grabbing properties needed and understand the reason why, I just threw this script together this morning and will refine with your suggestions.


P.s. Can’t I be both?

function My-Function {
    [CMDLETBINDING(SupportsPaging = $true,
                SupportsShouldProcess = $true)]
    BEGIN {
    $startypes = 'Powershell','Excel'
    } #end BEGIN
    foreach ($star in $startypes)
        {Write-Output "You are a $star Superstar!"}
    } #end PROCESS

    END {
    } #end END

} #end My-Function

[blockquote]You are a Powershell Superstar!
You are a Excel Superstar![/blockquote]


Yes, you can be both once you have learned everything about PowerShell and Excel, and speak at conferences.

Please check if below works for you. The function will ask your domain controller to filter for the training users and returns only the relevant properties. A calculated property in the Select-Object line will take care of getting the 35 characters from the Info attribute.

function Get-TrainingReport {

        [String] $OutputPath
    Begin {

        $Date = Get-Date -format 'yyMMdd_hhmm'
        $CsvPath = Join-Path -Path $OutputPath -ChildPath "$Date-TRAINING_REPORT.csv"
    Process {

        $Users = Get-ADUser -Filter { info -like '*Training*' -and enabled -eq $True } -Properties Name, Info, PasswordLastSet, LastLogonDate | 
            Select-Object -Property Name, @{Name='Info';Expression={$_.Info.Substring(0,35)}}, PasswordLastSet, LastLogonDate |
                Export-Csv -Path $CsvPath -NoTypeInformation

Get-TrainingReport -OutputPath C:\MyReportFolder