I have 5 files in one directory, is there a way to go through each of the files grab the $_.user value in each and get-adduser’s email address and populate it as an extra column in each spreadsheet? I understand the logic just don’t know how to code it yet.
are your files csv files? and do they have column headers?
Yes sir they are. The users are in a column called users
so basically
Directory/file.csv column1,column2,users,column3
Directory/file1.csv column1,column2,users,column3
Assuming the user column holds the DistinguishedName, GUID, SID, or samaccountname you could do something like this.
$newdata = Import-CSV input.csv |
ForEach-Object {
$email = Get-ADUser -Identity $_.users -Properties mail | Select-Object -ExpandProperty mail
Add-Member -InputObject $_ -NotePropertyName "email" -NotePropertyValue $email
$_
}
$newdata | Export-Csv input.csv -NoTypeInformation
Hey Curtis,
First I would like to thank you for taking your time to assist me with this.
I have not tested it, but does this loop through 1 csv? how do I make it run this code on every single CSV in the directory. so the logic should look something like this:
-Check directory for csv files - grabs file(s) grabs users from that csv finds out emails and add its to that csv in an additional column. goes to the next csv does the same thing.
If I have to run this vs one csv is fine…
I will try this code out now and see if this adds it to the csv as an addional column called email?
This is just against one CSV. to do multiple you would use Get-ChildItem to get all your files, then loop through them.
$files = Get-ChildItem *.csv
ForEach ($file in $files) {
import-csv $file.fullname | ... etc. etc.
}
Hey curtis, so i got really confused and tried to do it from scratch here is what I have:
but this just add to the file, how do I create a new column to add this data into it? once I have this, I will figure out how to loop through files lol trying to take it step by step.
$csv = ‘\gtnyfile1\shared\Old Data\logs\GTHQENGREMOTE1.csv’
$user = Import-Csv $csv | select user
foreach ($i in $user){
$mail = Get-ADUser $i.user -Properties * | select Mail
Add-Content $mail -path $csv
}
Meddy, here is the code with an explanation of how it works. This should give you the general idea of what to do, if it is not exactly what you want.
# First get a list of all of our CSV files in the directory
$files = Get-ChildItem *.csv
$newdata = @()
# For each file begin processing it
ForEach ($file in $files) {
# Import the current file's content
$data = Import-CSV $file.FullName
# For each item record in the input file
ForEach ($item in $data) {
# Use Get-ADUser to get the mail property from Active Directory
$email = Get-ADUser -Identity $item.users -Properties mail | Select-Object -ExpandProperty mail
# Add the email address to the current item
Add-Member -InputObject $item -NotePropertyName "email" -NotePropertyValue $email
# Add the item with the new email address to the $newdata set
$newdata += $item
# Loop and process the next item record until all records have been processed
}
# Export the newdata set to the same file the old data set was read from
$newdata | Export-Csv $file.FullName -NoTypeInformation
# Loop and process the next file until all files have been processed
}
hey Curtis, what this ended up doing was grabbing everything in the CSV and copying it again and again and again…it did end up addting ‘email’ column but no data in it…
so basically if I have
Name pc
tom tom-pc
hank hank-pc ----repeats
tom tom-pc
hank hank-pc
actually it made that the output for all my files ( and I am slapping myself for not backing them up now ) 
Sorry about that, the $newdata variable should have been inside of the loop so that it starts clean on each new file. As it was, it held on to the data from the previous files processed. So technically, your last file processed should contain the records from all of the files.
Side Note: You should always run code from the internet on test data first to make sure it does what you expect. Miss understandings of the desired result and/or type-o, everyone is fallible.
# First get a list of all of our CSV files in the directory
$files = Get-ChildItem *.csv
# For each file begin processing it
ForEach ($file in $files) {
# Import the current file's content
$data = Import-CSV $file.FullName
# Initalize new array to collect newdata set
$newdata = @()
# For each item record in the input file
ForEach ($item in $data) {
# Use Get-ADUser to get the mail property from Active Directory
$email = Get-ADUser -Identity $item.users -Properties mail | Select-Object -ExpandProperty mail
# Add the email address to the current item
Add-Member -InputObject $item -NotePropertyName "email" -NotePropertyValue $email
# Add the item with the new email address to the $newdata set
$newdata += $item
# Loop and process the next item record until all records have been processed
}
# Export the newdata set to the same file the old data set was read from
$newdata | Export-Csv $file.FullName -NoTypeInformation
# Loop and process the next file until all files have been processed
}
agreed, a well lesson learned. so this is what I have so far…but for some reason it only like processes 1 file, not the rest of the files…so it grabs the ENGfile and processes that over and over…but it does finish…
$csv = '\gtnyfile1\shared\Old Data\logs'
$files = Get-ChildItem $csv
foreach ($file in $files){
$user = Import-Csv $files | select user,computer,ip
foreach ($i in $user){
$mail = Get-ADUser $i.user -Properties * | select Mail
$new = New-object PSObject -Property([ordered]@{`
"mail"= $mail
"user" = $i.user
"PC" = $i.computer
"IP" = $i.ip
})
$new |Export-Csv -NoTypeInformation -path C:\users\amoustafa\desktop\output.csv -Append
}
}
$user = Import-Csv $files | …
should be
$user = Import-Csv $file | …
Hey guys, thanks for your help, Powershell kept searching locally for these files…this is the final script.
$csv = '\gtnyfile1\shared\Old Data\logs'
$files = Get-ChildItem $csv
foreach ($file in $files){
$loc = “$csv$file”
$user = Import-Csv $loc | select user,computer,ip
foreach ($i in $user){
$mail = Get-ADUser $i.user -Properties * | Select Mail
$new = New-object PSObject -Property([ordered]@{`
"mail"= $mail
"user" = $i.user
"PC" = $i.computer
"IP" = $i.ip
})
$new |Export-Csv -NoTypeInformation -path C:\users\amoustafa\desktop\output.csv -Append
}
}
which works great, I just have to edit the Mail field in excel because it adds the @{mail=} but I can edit that with excel! for now! until my programming skills are improved! haa.
Thanks Curtis and Tim !
much appreciated
Change
$mail = Get-ADUser $i.user -Properties * | Select Mail
to
$mail = Get-ADUser $i.user -Properties * | Select -ExpandProperty Mail
that will get rid of the @{mail=} situation
You also do not need to pull all properties just to select the mail property. This would be better.
$mail = Get-ADUser $i.user -Properties mail | Select -ExpandProperty Mail