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:\Users\abcdefg\temp\fieldstaffreport.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:\Users\abcdefg\temp\output.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

Either remove $CSVFile = from in front of foreach(..., or, add a line with the bare statement $i on a line underneath $i.department=.... — Mathias R. Jessen
– Mathias R. Jessen, Commented Jan 8, 2021 at 20:11
To further explain what @MathiasR.Jessen wrote - with statement $i you would output the content of the variable from the foreach block, to be collected in the $CSVFile array automatically. — zett42
– zett42, Commented Jan 8, 2021 at 20:48

Farbkreis · Accepted Answer · 2021-01-08 20:13:59Z

1

Can you check this?

[array]$CSVfile = Import-CSV "C:\Users\abcdefg\temp\fieldstaffreport.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:\Users\abcdefg\temp\output.csv"

answered Jan 8, 2021 at 20:13

Farbkreis

6444 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related