Powershell Update CSV

spiceuser-buscr · 2021-08-30T14:48:53.000Z

I have a spreadsheet with two columns, column A has the samAccountName and column B empty. I want to populate B using column A as a reference for each account. I want to pull attribute Title into column B and update the spreadsheet. I am open to creating a new spreadsheet or even keeping the data in powershell grid formatting, I just need to keep the order of my spreadsheet so I can copy it back into the master list

$USERS = Import-Csv “path\csv.csv”

ForEach ($User in $USERS) {
$.umbTitle = Get-ADUser -Identity $.samAccountname -properties Title
}

This gives me error - Get-ADUser : Cannot validate argument on parameter ‘Identity’. The argument is null. Provide a valid value for the argument, and then try running the command again.

I know my syntax is jacked, but I am new to powershell and have java and python experience.

Thanks,
Jesse

Neally · 2021-08-30T15:05:26.000Z

Welcome

If you post code, please use the ‘Insert Code’ button. Please and thank you!

PLEASE READ BEFORE POSTING! Read if you're new to the PowerShell forum! Programming & Development

Hi, and welcome to the PowerShell forum! Don’t apologize for being a “noob” or “newbie” or “n00b.” There’s just no need – nobody will think you’re stupid, and the forums are all about asking questions. Just ask! Use a descriptive subject. Don't say "Need help" or "PowerShell Help", actually summarize what the problem is. It helps the rest of us keep track of which problem is which. Don’t post massive scripts. We’re all volunteers and we don’t have time to read all that, nor will we copy, past…

192033ab-bb8f-4032-88a5-8e2313af0344-codebutton_small.png

Neally Bot

jrp78 · 2021-08-30T15:08:30.000Z

If the header line of the field in your csv is named samaccountname then this should work.

EDIT: actually you are trying to store the results into a variable. did you want this? If so, you have to append like so.

$umbtitle = @()
$USERS = Import-Csv "path\csv.csv"
ForEach ($User in $USERS) {
  $umbTitle += Get-ADUser -Identity $user.samAccountname -properties Title}

Neally · 2021-08-30T15:12:37.000Z

there are 2 foreach in powershell, there is foreach-object (that is used with $_) and there is foreach.

They are different.

try like so:

$USERS = Import-Csv "path\csv.csv"

$newCSV = 
ForEach ($User in $USERS) {
  [pscustomobject]@{
      Samaccountname = $user.samaccountname
      Title          = (Get-ADUser -Identity $User.samAccountname -properties Title).title
  }
}

$newCSV |
export-csv ".\file.csv" -NoTypeInformation

dannease · 2021-08-30T15:19:57.000Z

The $_ variable is automatically created when you’re doing pipelines, but that’s not the syntax you’re using here. You’re declaring $User, so you would need to use that. Also, the way you’re using Properties in Get-ADUser is just going to ensure that the Title property is included; it’s still going to return an entire user object. So you need to grab just the .Title property for that by wrapping the Get-ADUser call in parens and then appending .Title to it.

That gives you either of these:

ForEach ($User in $USERS) {
  $User.umbTitle = (Get-ADUser -Identity $User.samAccountname -properties Title).Title
}

OR, do it all in a pipeline:

$USERS | ForEach-Object {$_.umbTitle = (Get-ADUser -Identity $_.samAccountname -properties Title).Title}

spiceuser-buscr · 2021-08-30T15:20:34.000Z

Thanks I made some changes based on your recommendations. Now it runs without error but none of the data is being written to the CSV. Title and samAccountName are columns in the CSV

$USERS = Import-Csv "C:\Users\jesse\csv.csv" 

ForEach ($User in $USERS) {
   $User.Title = Get-ADUser -Identity $User.samAccountname -properties Title
}

So it is able to read the data, but it is not writing anything back to the CSV.

spiceuser-buscr · 2021-08-30T15:25:57.000Z

This is really helpful, but when I run that code the csv is not getting updated. It does run without error though and I appreciate the distinction you made between the implicit $_. object and the way I specified the name for that object in my original code.

jrp78 · 2021-08-30T15:29:18.000Z

That’s not how it works, you have to create a new csv.

$umbtitle = @()
$USERS = Import-Csv "path\csv.csv"
ForEach ($User in $USERS) {
  $umbTitle += Get-ADUser -Identity $user.samAccountname -properties Title |  select samaccountname, title}

$umbtitle | export-csv "path\newfile.csv" -NoTypeInformation

Neally · 2021-08-30T15:32:05.000Z

jrp78:

That’s not how it works, you have to create a new csv.

$umbtitle = @()
$USERS = Import-Csv "path\csv.csv"
ForEach ($User in $USERS) {
  $umbTitle += Get-ADUser -Identity $user.samAccountname -properties Title |  select samaccountname, title}

$umbtitle | export-csv "path\newfile.csv" -NoTypeInformation

Using the += is discouraged, as it deleted and re-creates the array for every iteration.
You can just assign it to a variable (as I did in my sample here ), but yeah basically same idea.

spiceuser-buscr · 2021-08-30T15:38:46.000Z

This solved it for me! Thanks everyone for your help in explaining this. I have learned a lot today!