PowerShell foreach loop export to new column in CSV format

Question

I am currently working with a CSV file that has a manager's employee number, but not their SAMAccountName. I want to utilize Get-ADUser to grab the manager's SAMAccountName from their EmployeeNumber attribute and place that inside a new column in the same CSV file.

CSV sample:

"Full Name","Username","Manager","Manager User Sys ID"
"User 1","u1","1, Manager","123456"
"User 2","u2","2, Manager","234567"
"User 3","u3","3, Manager","345678"

I would like:

"Full Name","Username","Manager","Manager User Sys ID","Manager SamAccountName"
"User 1","u1","1, Manager","123456","m1"
"User 2","u2","2, Manager","234567","m2"
"User 3","u3","3, Manager","345678","m3"

I have spent some time putting together the following code. I can get a new column added and can further grab the SAMAccountName, but it only exports a single line in the new CSV file like this:

"SAMAccountName","managerUsername"
"m1","@{SAMAccountName=m1}"

Here is the code:

$managers = Import-Csv -Path .\test.csv
$usermananger = @()
foreach ($manager in $managers)
{
  $getmanagersam = Get-ADUser -Filter "employeeNumber -eq $($manager."Manager User Sys ID")" -Properties SAMAccountName |
    Select-Object SAMAccountName
  $exportstring = $testmanager |
    Select-Object *,@{Name='managerUsername';Expression={"$getmanagersam"}}
  $exportstring | Export-Csv -Path .\simpletest.csv -NoTypeInformation
}

Change select SAMAccountName to select -ExpandProperty SAMAccountName — Mathias R. Jessen
– Mathias R. Jessen, Commented Apr 30, 2015 at 16:55
Mathias, thanks for the reply. That helped a bit - the output is cleaner but basically what's happening is it's throwing a syntax error and it's overwriting the CSV over and over and only keeps one line. Error parsing query 'employeeNumber -eq NO_MANAGER' syntax error. — jdubs
– jdubs, Commented Apr 30, 2015 at 17:38

Ansgar Wiechers · Accepted Answer · 2015-04-30 20:17:39Z

1

As @MathiasR.Jessen mentioned in the comments: you need to expand the SamAccountName property to get just the value. Also, you're overwriting your output CSV with every iteration. Either append to the file, or move the Export-Csv cmdlet outside the loop. The former requires PowerShell v3 or newer, the latter requires that you change the loop to a ForEach-Object loop (or run the foreach loop in a subexpression).

Personally I'd prefer using a pipeline, so I'd pick the latter:

Import-Csv -Path .\test.csv | ForEach-Object {
  $acct = Get-ADUser -Filter "employeeNumber -eq $($_.'Manager User Sys ID')" |
          select -Expand SamAccountName
  $_ | select *,@{Name='managerUsername';Expression={$acct}}
} | Export-Csv -Path .\simpletest.csv -NoTypeInformation

edited Apr 30, 2015 at 20:17

answered Apr 30, 2015 at 17:55

Ansgar Wiechers

201k27 gold badges286 silver badges363 bronze badges

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

2 Comments

jdubs Over a year ago

Ansgar - this worked brilliantly. I appreciate the assist on this. One problem I'm having is that it keeps throwing a syntax error. Any advice on how to get around the syntax error?

Ansgar Wiechers Over a year ago

@jdubs You have one or more blank lines in test.csv. Remove them.

Graham Gold · Accepted Answer · 2015-04-30 18:34:01Z

The short answer is to add the -Append option to your export-csv statement to stop it overwriting each time round the loop.

Alternatively move the export outside the loop as follows:

$managers = Import-Csv -Path .\test.csv

$managers|foreach-object{
  $getmanagersam = Get-ADUser -Filter "employeeNumber -eq $($_.'Manager User Sys ID')" | select -ExpandProperty SAMAccountName
  $_|Select *,@{Name='managerUsername';Expression=$getmanagersam}
} | Export-Csv -Path .\simpletest.csv -NoTypeInformation

Note: looks like @AnsgarWiechers beat me to it :-)

TheMadTechnician · Accepted Answer · 2015-05-01 02:50:32Z

I'm late to the party it looks like, but that rarely stops me... I'm not a huge fan of adding properties via the Select cmdlet, so I'm going to offer an alternative where you pipe the CSV to a ForEach-Object loop (just like the other answers), but inside that you use Add-Member with the -PassThru argument, and then pipe it off to the output file. I will add a new line and indent after the pipes, just to make it easier to read.

Import-Csv -Path ".\test.csv" | 
    ForEach-Object {Add-Member -InputObject $_ -NotePropertyName 'managerUserName' -NotePropertyValue (Get-ADUser -Filter "employeeNumber -eq $($_.'Manager User Sys ID')").samaccountname -PassThru} |
    Export-Csv -Path ".\simpletest.csv" -NoTypeInformation

This should essentially do the exact same thing as the other two answers, it just adds the property differently, because variety is the spice of life! (I saw that on the internet somewhere, so it must be true.)

MadTech - This also worked brilliantly, thank you for adding to the discussion. This method is throwing the same syntax error as Angars version... do you think it has something to do with the CSV header having spacing?
it probably wants single quotes around the search string. Try "employeeNumber -eq '$($_.'Manager User Sys ID')'" and see if that doesn't fix the error.

Collectives™ on Stack Overflow

PowerShell foreach loop export to new column in CSV format

3 Answers 3

2 Comments

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Linked

Related