Adding a split string to CSV

I am a bit confused on the most efficient way to achieve this task. In exchange, I’m attempting to generate a csv that holds the data I need. Below is the command I am using:

Get-Mailbox -ResultSize 20 | Select-Object "UserPrincipalName", "Alias" | Export-Csv "C:\Users\xml"

The format for “alias” is always “firstname.lastname”. In my csv file, I’d like additional fields added under “FirstName” and “LastName”, and I was going to populate these fields by splitting the “Alias” string, and last name to the $lastname=@().

Something like this:

$firstname=@()
$lastname@()
foreach($alias in $mailboxes){
$firstname += $alias.split"."[0]
$lastname += $alias.split"."[1]

As I was writing this I realized I could probably just build a PS custom object and feed each value in the value, then export. Any suggestions would be appreciated though.

EDIT: So after trying

$Mailboxes = Get-Mailbox -ResultSize 20 | Select-Object "UserPrincipalName", "Alias"

foreach($alias in $Mailboxes.alias){
    $name = $alias -split "."
    $firstname = $name[0]
    write-host $firstname
}

This doesn’t output anything :frowning:

Try using calculated properties:

Get-Mailbox -ResultSize 20 | Select-Object UserPrincipalName,
                                            @{
                                                name='FirstName'
                                                expr={$_.Alias.split(".")[0]}
                                            },
                                            @{
                                                name='LastName'
                                                expr={$_.Alias.split(".")[1]}
                                            } | Export-Csv "C:\result.csv"
1 Like

I think @LearnLaughOps solution is the best, and it’s a technique you’ll find a lot of use for.
Here’s a long winded way of achieving something similar.

$MailBoxes = Get-Mailbox -ResultSize 20

$Users = Foreach ($Mailbox in $Mailboxes) {
    [PSCustomObject]@{
        UserPrincipalName = $Mailbox.UserPrincipalName
        Alias = $Mailbox.Alias
        FirstName = $Mailbox.Alias.split('.')[0]
        LastName = $Mailbox.Alias.split('.')[1]
    }
}

$Users | Export-Csv c:\Users\report.csv -NoTypeInformation

First thing is to capture all the mailboxes in a variable. I’m foregoing Select-Object here which means in the future if there are more properties I want to include in my export they’ll still be on those objects and I can just add another property to my PSCustomObject.

Then we loop through the mailbox objects and for each one, we output a PSCustomObject with the property definitions we want, including splitting the Alias in to two new properties: FirstName and LastName.

Then all that output is being captured in a variable. This gives me a chance to review it in VS Code and check that the output is what I want.

Lastly, the Users variable is piped to Export-Csv where I’m also using the -NoTypeInformation or -nti (for short) to exclude the first row that’s usually output.

2 Likes

Your answer used the PSCustomObject, which I needed to use to change the header values of “UserPrincipalName” and “Alias”. I’m still learning to use this function so I appreciate your response.

1 Like

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.