Import Multiple values in Exchange Command shell
- I am trying to run this command :
import-csv DL_AuthorizedSenders.csv | foreach {Set-DistributionGroup -Identity $_.'DLName’ -AcceptMessagesOnlyFrom $_.AuthorizedSender}
Excel file contains two columns, one with DL name and other with a userID, there are multiple entries which I want to be authorized senders but when I run this script it picks the last one and shows only one authorized sender, I want to add all in a file as authorized senders. Can anyone guide me how to do it?
Answers
- On Sat, 7-Nov-09 20:19:48 GMT, Manajee wrote:
>I am trying to run this command : import-csv DL_AuthorizedSenders.csv | foreach {Set-DistributionGroup -Identity $_.'DLName’ -AcceptMessagesOnlyFrom $_.AuthorizedSender} Excel file contains two columns, one with DL name and other with a userID, there are multiple entries which I want to be authorized senders but when I run this script it picks the last one and shows only one authorized sender, I want to add all in a file as authorized senders. Can anyone guide me how to do it?
doing for a minute -- you're setting the
value, not adding to it. :-)
Replace your set-distributiongroup with something like this:
Set-DistributionGroup DLNAME -AcceptMessagesOnlyFrom
((get-distributiongroup DLNAME).AcceptMessagesOnlyFrom + $_)
---
Rich Matheisen
MCSE+I, Exchange MVP
--- Rich Matheisen MCSE+I, Exchange MVP- Marked As Answer byXiu Zhang - MSFTMSFT, ModeratorFriday, November 20, 2009 9:27 AM
Rich Matheisen: You're setting the value, not adding to it. :-) Replace your set-distributiongroup with something like this:
Set-DistributionGroup DLNAME -AcceptMessagesOnlyFrom
((get-distributiongroup DLNAME).AcceptMessagesOnlyFrom + $_)Here is a script that is tested on my SBS 2008 box with Exchange 2007. Rich Matheisen is basically right with his algorithm. However, that code block does not work (and does not pretend to either). But I guess the code could have come closer to Rich's and be tighter than below:
Import-Csv C:\Temp\DL_AuthorizedSenders.csv |
Foreach-Object {
# Create new object and assign DG
$dgObj = New-Object System.Object
$dgObj = Get-DistributionGroup $_.DLName
# Retrieve accepted senders
$dgObj | Add-Member -Membertype NoteProperty -Name Accept -Value $dgObj.AcceptMessagesOnlyFrom -Force
# Add authorized sender to the list of authorized senders
$newAuthSender = $dgObj.Accept + $_.AuthorizedSender
Set-DistributionGroup -Identity $_.DLName -AcceptMessagesOnlyFrom $newAuthSender -ErrorAction SilentlyContinue
}
Four users and three distribution groups are created. The .CSV file:
DLName,AuthorizedSender
dist_1,user 1
dist_1,user 2
dist_2,user 2
dist_2,user 3
dist_2,user 4
dist_3,user 3
dist_3,user 4
The ForEach-Object loop iterates through each line. First the contents of AcceptMessagesOnlyFrom are retrieved and stored in the array variable $dgObj.Accept. Then the contents of $_.AuthorizedSender is added. There are some limitations: The script does not check if the users and distribution groups exist. The script can only be run once. It will fail if these users are assigned to the field AcceptMessagesOnlyFrom. If you need to run it once more, you will first have to remove the users. Run Set-DistributionGroup -Identity $_.DLName -AcceptMessagesOnlyFrom $Null once or twice inside the ForEach-Object loop. A future version of this script might handle this limitation.
MCTS: Messaging | MCSE: S+M | Small Business Specialist- Marked As Answer byXiu Zhang - MSFTMSFT, ModeratorFriday, November 20, 2009 9:27 AM
All Replies
- On Sat, 7-Nov-09 20:19:48 GMT, Manajee wrote:
>I am trying to run this command : import-csv DL_AuthorizedSenders.csv | foreach {Set-DistributionGroup -Identity $_.'DLName’ -AcceptMessagesOnlyFrom $_.AuthorizedSender} Excel file contains two columns, one with DL name and other with a userID, there are multiple entries which I want to be authorized senders but when I run this script it picks the last one and shows only one authorized sender, I want to add all in a file as authorized senders. Can anyone guide me how to do it?
doing for a minute -- you're setting the
value, not adding to it. :-)
Replace your set-distributiongroup with something like this:
Set-DistributionGroup DLNAME -AcceptMessagesOnlyFrom
((get-distributiongroup DLNAME).AcceptMessagesOnlyFrom + $_)
---
Rich Matheisen
MCSE+I, Exchange MVP
--- Rich Matheisen MCSE+I, Exchange MVP- Marked As Answer byXiu Zhang - MSFTMSFT, ModeratorFriday, November 20, 2009 9:27 AM
- This article shows the syntax that is needed for entries to a multi-value field, http://support.microsoft.com/default.aspx/kb/945643.
Basically the issue isn't your cmd, it's the format of the csv file.
I would use something like the following:
DLName,AuthorizedSender
MYDL,"user1, user2, user3"
I don't have an environment handy to test this on but that should be close.
Thanks
Will
Will Shepherd - MCSE/MCITP/MCTS (Windows 2008,Exchange 2007,OCS 2007) import-csv DL_AuthorizedSenders.csv | foreach {Set-DistributionGroup -Identity $_.'DLName’ -AcceptMessagesOnlyFrom $_.AuthorizedSender}
import-csv DL_AuthorizedSenders.csv | Foreach-Object {Set-DistributionGroup -Identity $_.'DLName’ -AcceptMessagesOnlyFrom $_.AuthorizedSender}
Try the statement above. I think the error is that you have used foreach instead of Foreach-Object.
MCTS: Messaging | MCSE: S+M | Small Business Specialist- Tried all above suggestions but nothing works, its doing the same thing just picking up the last entry in CSV file and replace all.
Will Shepard: This article shows the syntax that is needed for entries to a multi-value field, http://support.microsoft.com/default.aspx/kb/945643.
Basically the issue isn't your cmd, it's the format of the csv file.
I would use something like the following:
DLName,AuthorizedSender
MYDL,"user1, user2, user3"Yes, that is where the problem is. The values get overwritten. This approach in the KB works from the command line, but not from a .CSV file. All values between the quotation marks are treated as a single object. Though I have not tested it, with PowerShell v2, we could have used the Import-Csv -delimiter ";" command, thereby not running into the issue with the comma:
--------------- CSV FILE START ----------------
DLName,AuthorizedSender
dist_3,"user1@smith.lcl, user2@smith.lcl, user4@smith.lcl"
--------------- CSV FILE STOP ----------------
[PS] C:\>Import-Csv C:\temp\DL_AuthorizedSenders_03.csv | Foreach-Object {Set-DistributionGroup -Identity $_.DLName -AcceptMessagesOnlyFrom $_.AuthorizedSender}Object "user1@smith.lcl, user2@smith.lcl, user4@smith.lcl" could not be found. Please make sure that it was spelled correctly or specify a different object. At line:1 char:66
To handle this, you will need a scipt inside the Foreach-Object loop which one by one adds all the users to a variable. I'll try later today.
MCTS: Messaging | MCSE: S+M | Small Business Specialist- On Sun, 8-Nov-09 07:17:48 GMT, Manajee wrote:
>Tried all above suggestions but nothing works, its doing the same thing just picking up the last entry in CSV file and replace all.
If you tried my suggestion and it didn't work then I think you may
have misstated your problem. My code will add the current user to the
already existing set of values. It doesn't replace them.
---
Rich Matheisen
MCSE+I, Exchange MVP
--- Rich Matheisen MCSE+I, Exchange MVP Rich Matheisen: You're setting the value, not adding to it. :-) Replace your set-distributiongroup with something like this:
Set-DistributionGroup DLNAME -AcceptMessagesOnlyFrom
((get-distributiongroup DLNAME).AcceptMessagesOnlyFrom + $_)Here is a script that is tested on my SBS 2008 box with Exchange 2007. Rich Matheisen is basically right with his algorithm. However, that code block does not work (and does not pretend to either). But I guess the code could have come closer to Rich's and be tighter than below:
Import-Csv C:\Temp\DL_AuthorizedSenders.csv |
Foreach-Object {
# Create new object and assign DG
$dgObj = New-Object System.Object
$dgObj = Get-DistributionGroup $_.DLName
# Retrieve accepted senders
$dgObj | Add-Member -Membertype NoteProperty -Name Accept -Value $dgObj.AcceptMessagesOnlyFrom -Force
# Add authorized sender to the list of authorized senders
$newAuthSender = $dgObj.Accept + $_.AuthorizedSender
Set-DistributionGroup -Identity $_.DLName -AcceptMessagesOnlyFrom $newAuthSender -ErrorAction SilentlyContinue
}
Four users and three distribution groups are created. The .CSV file:
DLName,AuthorizedSender
dist_1,user 1
dist_1,user 2
dist_2,user 2
dist_2,user 3
dist_2,user 4
dist_3,user 3
dist_3,user 4
The ForEach-Object loop iterates through each line. First the contents of AcceptMessagesOnlyFrom are retrieved and stored in the array variable $dgObj.Accept. Then the contents of $_.AuthorizedSender is added. There are some limitations: The script does not check if the users and distribution groups exist. The script can only be run once. It will fail if these users are assigned to the field AcceptMessagesOnlyFrom. If you need to run it once more, you will first have to remove the users. Run Set-DistributionGroup -Identity $_.DLName -AcceptMessagesOnlyFrom $Null once or twice inside the ForEach-Object loop. A future version of this script might handle this limitation.
MCTS: Messaging | MCSE: S+M | Small Business Specialist- Marked As Answer byXiu Zhang - MSFTMSFT, ModeratorFriday, November 20, 2009 9:27 AM
