Author Posts

April 1, 2016 at 12:59 am

Hi, is it possible to make a reference to custom property already created in order to avoid repetition of formula used to create it? For instance:


Import-Excel -Path $PathToFile |
Select-Object -Property @{n='CustomProperty1';e={...}},
@{n='CustomProperty2';e={ReferenceToCustomProperty1 + ...}}

Typical example would be UserPrincipalName property of AD user. I was given .xlsx file where there is column full name – by using -split method and regex GivenName and SurName can be extracted and then SamAccountName would be GivenName.SurName and UserPrincipalName would be SamAccountName@domainname. I would like to avoid repetition of -split and regex for UserPrincipalName custom property since it can be calculated by simply adding @domainname to the end of SamAccountName.

April 1, 2016 at 6:07 am

i recommend using ForEach-Object in this case

Import-Excel -Path $PathToFile | ForEach-Object {
    $customproperty1 = 'code'
    $customproperty2 = $customproperty1 + 'more code'

    [pscustomobject]@{
        CustomProperty1 = $customproperty1
        CustomProperty2 = $customproperty2
    }
}

April 1, 2016 at 6:15 am

If your csv file contains fullname column with a space, use something like this.
Ex: John Doe

Import-Csv .\test.csv | ForEach-Object {
$fullname = ($_.fullname).split()
$samaccount = $fullname -join '.'
[pscustomobject]@{
    GivenName = $fullname[0]
    SurName = $fullname[1]
    SamAccount = $samaccount
    UPN = "$samaccount@domainname"
    }
} 

April 2, 2016 at 4:49 am

These techniques are well known to me (when ForEach-Object is used). There is nothing wrong with that but I wondered whether using already created custom property to calculate other custom property was doable. My case is good example since New-ADUser cmdlet accepts pipeline input by property name so:


Import-Excel -Path $PathToFile |
Select-Object -Property @{n='SamAccountName';e={...}},
@{n='UserPrincipalName';e={SamAccountName + "@domainname"}},
@{},
@{},
...,
@{} | New-ADUser -Verbose

would be an ideal way of doing this.

Import-Excel function creates objects with columns names as properties (as if I have .csv file) from input .xlsx file. However that file comes from HR department and I have to process practically all values in order to get right format for AD user attributes (I rely heavily on using RegEx with -split and -replace methods so lines are pretty long thus I want to avoid repetitions). Also in my language there are letters 'č', 'ć', 'ž', 'š', 'đ' which can not be used in SamAccountName and UserPrincipalName but they have to be replaced with letters 'c', 'c', 'z', 's', 'dj' respectively. Also if givenname.surname exceeds 20 characters I have to use firstletterofgivenname.surname to get SamAccountName and UserPrincipalName. All in all there are many splits, replaces and RegEx's so any repetition of these long lines is something I would like to skip if possible.

April 2, 2016 at 8:32 am

I think what you're asking is if you can use the value of a calculated property to create another calculated property based on that value in the same Select-Object function. Unfortunately I don't think this is possible because Select-Object would not know of the calculated property as the properties it gets are from the object that you've sent down the pipeline.

There is of course nothing stopping you from doing multiple Select-Object statements sequentially, but really all you'd be gaining is readability in your expressions of calculated properties further down your pipeline.

April 2, 2016 at 8:52 am

Unfortunately among other responsibilities taking care of user accounts, which should have been done by some junior domain admin, is also something I am doing on daily basis. Since we do not have any professional software like Identity Manager this work has been tedious and manual so I have decided to use PS which works well with regular expressions allowing me to correct errors in typing or formatting in input Excel file – in doing so I have perfect input for New-ADUser cmdlet.

Conclusion: It is impossible to make reference to already calculated custom property in Select-Object, custom property can be calculated from properties of object coming into pipeline i.e. $_ or $PSItem.