CODEWITHSUNDEEP
powershellactive-directory
MarcGel
MarcGel

Reputation: 301

CSV export is missing data in one of the columns

I have a csv file that this script works fine on. 

$CCure = Import-csv C:\Scripts\file1.csv

ForEach ($user in $CCure) {

    [string]$1Name = $User.FirstName
    [string]$2Name = $User.LastName
    [string]$GivenName = $1Name.Split(" ")[0]
    [string]$SN = $2Name.Split(",")[0]
    [string]$ID = $User.ObjectID
    [string]$EmpID = $User.Int5 |   
    Select @{Name="First Name";Expression={$GivenName}}, @{Name="Last Name";Expression={$SN}}, @{Name="CCure ObjectID";Expression={$ID}}, @{Name="CCure Int5 Row";Expression={$EmpID}} | 
    Export-csv C:\Scripts\CCure\CCure-Names-Test.csv -Append -notypeinformation
    }

However, when I try a similar script going out to AD and want to use RegEx, things don't work. It seems to hang or when it does run, the SurName is missing. Why doesn't the second one work? I must be missing something simple?

$Users = Get-ADUser -LDAPFilter "(&(&(objectCategory=person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2))" -properties samaccountname,givenname,sn
ForEach ($User in $Users) {

    [string]$1Name = $User.GivenName
    [string]$2Name = $User.SN
    [string]$GivenName = $1Name.Split(" ")[0]
    [string]$SN = $2Name.Split(",")[0] |
    Select @{Name="First Name";Expression={$GivenName}}, @{Name="Last Name";Expression={$SN}},@{Name="SID";Expression={$User.samaccountname}}| Export-Csv C:\scripts\ccure\AD-Active-Names3.csv -NoTypeInformation -append

}

Upvotes: 0

Views: 1785

Answers (1)

Matt
Matt

Reputation: 46690

In your second example $sn would be null. This is because of the trailing pipe character that you have. This issue is also present in your first code block for $EmpID. The last command Export-CSV returns nothing to the output stream so the variable in both cases would be $null.

You are taking the result of $2Name.Split(",")[0] and sending that into the pipeline which is then left unused for Select-Object.

So the simple answer is remove the pipeline character and those two lines will now work separately.

[string]$SN = $2Name.Split(",")[0] 
Select @{Name="First Name";Expression={$GivenName}}, @{Name="Last Name";Expression={$SN}},@{Name="SID";Expression={$User.samaccountname}}| Export-Csv C:\scripts\ccure\AD-Active-Names3.csv -NoTypeInformation -append

Also consider making your own object if you are changing property names and values that much. This should accomplish the same thing, be easier to read and take advantage of the pipeline. 

$Users = Get-ADUser -LDAPFilter "(&(&(objectCategory=person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2))"
$Users | ForEach-Object {
    $props = @{
        "First Name" = ($_.GivenName).Split(" ")[0]
        "Last Name" = ($_.SurName).Split(",")[0]
        "SID" = $_.samaccountname
    }

    New-Object -TypeName psobject -Property $props    
} | Select-Object "First Name","Last Name",SID |  Export-Csv C:\scripts\ccure\AD-Active-Names3.csv -NoTypeInformation

You will note that I used $_.SurName as the property names used since PowerShell AD Objects do not directly match their LDAP attribute names. If you tried to access $_.SN you would get a null value. GivenName,Surname and SID are part of the value set returned by default so you don't need to request them.

Looking at about_ActiveDirectory_ObjectModel you will see under AdUser:

Surname - A property of type System.String, derived from the directory attribute: sn

You will still get errors with this if Surname or GivenName are not populated. Accounting for that would be a simple if statement if that is going to be an issue.

Upvotes: 2

Related Questions