CODEWITHSUNDEEP
powershellactive-directoryexport-to-csv
Ravi Singh
Ravi Singh

Reputation: 441

Change email address in CSV

I am writing PowerShell code to export email addresses to a csv file and edit them. I've written the following:

# Script to get all DLs and email addresses in csv file
Get-ADGroup -Filter 'groupcategory -eq "distribution"' -Properties * |
    select Name, mail |
    Export-csv "C:\temp\Distribution-Group-Members.csv"

# Following will import the csv, make new column as proxy and save the file
# as update.csv 
Import-Csv "C:\temp\Distribution-Group-Members.csv" |
    Select-Object "Name", "mail", @{n = "proxy"; e = "mail"} |
    Export-Csv "c:\temp\Distribution-Group-Members-Updated.csv" -NoTypeInfo

# Following script can import the csv and set proxy addresses from proxy
# column 
Import-Csv "c:\temp\Distribution-Group-Members-Updated.csv" |
    Foreach {
        Get-ADGroup $_.Name | Set-ADGroup -Add @{
            proxyaddresses = ($_.proxy -split ";")
        }
   }

Now, I would like to add 2 more features in the script:

So assuming my DL name is "DL Test" email is "[email protected]" => The script should update the email address of DL to "[email protected]" and add "smtp:[email protected]" as proxy mail address

Can someone please advise, how can I achieve that?

Upvotes: 1

Views: 1393

Answers (1)

TessellatingHeckler
TessellatingHeckler

Reputation: 28983

The part where you have written:

select-object "Name", "mail", @{n = "proxy"; e = "mail"}|

The proxy part is called a calculated property. The first two with just names Name and Mail are copied directly from the input objects, but using the @{..} syntax, you can put code to calculate a new value instead.

So you can use this to achieve both your desired changes:

Import-Csv -Path 'C:\temp\Distribution-Group-Members.csv' | 

  Select-Object -Property Name, 
   @{Label='Mail';  Expression={$_.Mail -replace 'abc', 'xyz'}}, 
   @{Label='Proxy'; Expression={"SMTP:$($_.Mail -replace 'abc', 'xyz');smtp:$($_.Mail)"}}|

  Export-csv 'C:\temp\Distribution-Group-Members.csv' -NoTypeInformation

Upvotes: 3

Related Questions