Author Posts

October 26, 2015 at 12:32 pm

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.

October 26, 2015 at 5:53 pm

are your files csv files? and do they have column headers?

October 27, 2015 at 5:14 am

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

October 27, 2015 at 7:44 am

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

October 27, 2015 at 8:02 am

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?

October 27, 2015 at 8:10 am

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.
}

October 27, 2015 at 10:39 am

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

}

October 27, 2015 at 11:52 am

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
}

October 27, 2015 at 12:37 pm

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 ) 🙂

October 27, 2015 at 12:51 pm

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
}

October 28, 2015 at 6:25 am

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

}

}

October 28, 2015 at 6:33 am

$user = Import-Csv $files | ...
should be
$user = Import-Csv $file | ...

October 28, 2015 at 6:58 am

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

October 28, 2015 at 7:10 am

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