How to write to one entry in a CSV with Powershell in foreach loop

Question

I am trying to write a script that will take an existing CSV file, with an empty column for "department", and for each row, use the Email address that is present in that row to do a reverse lookup of that user's department in Active Directory, and then write that department value to the row in the "Department" column.

Currently, I can read the CSV, get the emails, get the department, but I cannot figure out how to change the value of "Department" for that row to the value stored in the $department variable.

Import-Module ActiveDirectory

$CSVfile = Import-CSV "C:Usersabcdefgempfieldstaffreport.csv" -Header "training", "fname", "lname", "email", "department", "group", "completion", "url", "date1", "date2"


$CSVfile = foreach ($i in $CSVfile) {
    $email = $i.Email
    $department = Get-ADUser -filter "EmailAddress -eq '$email'" -property department | select -expandproperty department
    Write-Host($i.Email + ", " + $department) -NoNewline
    $i.department= $department
}

$CSVfile | Export-CSV "C:Usersabcdefgempoutput.csv"

I believe that line 10, where I write $i.department = $department should change the value. This script uses a foreach loop to cycle through every row in the CSV.

Can someone please tell me what I'm doing wrong. I've exhausted all relevant guides and articles about this, and I can't figure out why this doesn't work.

The write-host output works perfectly, showing the email address, then the department value from AD, separated by a comma. It all lines up correctly with the actual user data and everything.

Thanks

Answer 1

Can you check this?

[array]$CSVfile = Import-CSV "C:Usersabcdefgempfieldstaffreport.csv" -Header "training", "fname", "lname", "email", "department", "group", "completion", "url", "date1", "date2"


for($i=0; $i -ne $CSVfile.count; $i++) {
    $email = $CSVfile[$i].Email
    $department = Get-ADUser -filter "EmailAddress -eq '$email'" -property department | select -expandproperty department
    Write-Host($CSVfile[$i].Email + ", " + $department) -NoNewline
    $CSVfile[$i].department= $department
}

$CSVfile | Export-CSV "C:Usersabcdefgempoutput.csv"