loop through files grab $_.user and populate email address from query to each cs

This topic contains 13 replies, has 3 voices, and was last updated by Profile photo of Curtis Smith Curtis Smith 1 year, 4 months ago.

  • Author
    Posts
  • #31332
    Profile photo of Meddy Med
    Meddy Med
    Participant

    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.

  • #31340
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

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

  • #31354
    Profile photo of Meddy Med
    Meddy Med
    Participant

    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

  • #31364
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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
    
  • #31367
    Profile photo of Meddy Med
    Meddy Med
    Participant

    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?

  • #31370
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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.
    }
    
  • #31375
    Profile photo of Meddy Med
    Meddy Med
    Participant

    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

    }

  • #31379
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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
    }
    
  • #31381
    Profile photo of Meddy Med
    Meddy Med
    Participant

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

  • #31382
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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
    }
    
  • #31424
    Profile photo of Meddy Med
    Meddy Med
    Participant

    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

    }

    }

  • #31428
    Profile photo of Tim Curwick
    Tim Curwick
    Participant

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

  • #31436
    Profile photo of Meddy Med
    Meddy Med
    Participant

    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

  • #31437
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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

You must be logged in to reply to this topic.