I have a CSV file with the following header names:

Name, Date, Owner

I want to use PowerShell to

1. Import the File1.csv that contains header values "Name", "Date", "Owner", "Extra"
2. Read the "Owner" column (contains samaccountnames) and use it to query AD to find the "department, division" attributes associated with each user.
3. Create two new columns named "Department" and "Division" then export the CSV into a new CSV (file2.csv)

Here's the code I have so far:

$file1 = Import-csv -path "C:\temp\File1.csv"
ForEach ($record in $file1) {
    $getAttributes = Get-ADUser -Filter $record.Owner | Select-Object division,department
    $record | Add-Member -MemberType NoteProperty -Name "Division" -Value $getAttributes.Division
    $record | Add-Member -MemberType NoteProperty -Name "Department" -Value $getAttributes.Department
    $record | Export-Csv -Path C:\temp\file2.csv -Encoding UTF8 -NoTypeInformation -Append
}

I have tried different variations and nothing has worked for me so far. Any assistance would be appreciated!

CodePudding user response：

The two main issues with your code are:

1. -Filter $record.Owner is not a valid syntax for the ActiveDirectory Filter.
2. Get-ADUser does not return an object with the attributes Department and Division unless you specifically ask to query them (-Properties Department, Division).

Import-csv -Path "C:\temp\File1.csv" | ForEach-Object {
    $usr = Get-ADUser -LDAPFilter "(samaccountname=$($_.Owner))" -Properties Department, Division
    if(-not $usr) {
        # if the user could not be found skip it
        Write-Warning "'$($_.Owner)' could not be found..."
        return
    }
    # recreate this object (`$_`) with 2 new properties, `Division` and `Department`
    $_ | Select-Object *, @{N='Division';E={$usr.Division}}, @{N='Department';E={$usr.Department}}
} | Export-Csv -Path C:\temp\file2.csv -Encoding UTF8 -NoTypeInformation