Importing csv ForEach-Object where objects have multiple values

I am trying to import a CSV and configure -AcceptMessagesOnlyFrom property. I can run this for example:

<Set-DistributionGroup “group name” –AcceptMessagesOnlyFrom user1@domain.com,user2@domain.com>

But when i import a CSV like this:

<Import-Csv “C:\Import.csv” | ForEach-Object {Set-DistributionGroup -Identity _.PrimarySMTPAddress -AcceptMessagesOnlyFrom .AcceptMessagesOnlyFrom -AcceptMessagesOnlyFromDLMembers $.AcceptMessagesOnlyFromDLMembers}>

Then I get an error "
Set-DistributionGroup : Couldn’t find object “name1@abc.com name2@abc.com”. Please make sure that it was spelled correctly or specify a different object."

And so i think that i should separate those email addresses with a comma and I try that and get a similar error

Set-DistributionGroup : Couldn’t find object “name1@abc.com, name2@abc.com”. (the comma between the names.

And so I try any number of commas, and quotation marks, in the csv file and no worky. I don’t understand how to tell PowerShell, there is more than one email address (value) and that foreach value (email address) create an entry.

What does the AcceptMessagesOnlyFrom column look like? I’m guessing the addresses are separated by spaces from your post.

From looking at the docs, I suspect it’s choking on the spaces, so remove them and replace with commas.

I’d do something like this (not tested) which will also help readability:

Import-Csv “C:\Import.csv” | ForEach-Object {

    $params = @{

        Identity = $_.PrimarySMTPAddress
        AcceptMessagesOnlyFrom = $_.AcceptMessagesOnlyFrom -replace ' ',','
        AcceptMessagesOnlyFromDLMembers = $_.AcceptMessagesOnlyFromDLMembers

    }

    Set-DistributionGroup @params

}

1 Like

What does the -AcceptMessageOnlyFrom parameter does? it is programmed to accept multiple strings. Could it be possible to get an excerpt of the script on how the parameter was written.

Thank you so much for helping me.

I have not tried your method yet. But, just thinking about it, using -replace, should not make a difference. I can edit the csv, and enter whatever i want in the AcceptMessagesOnlyFrom column. At this point I am using a CSV that has three columns, PrimarySMTPAddress AcceptMessagesOnlyFrom, AcceptMessagesOnlyFromDLMembers.

Doing a full help on set-distributiongroup reveals:

-AcceptMessagesOnlyFrom
MultiValuedProperty

" Valid values for this parameter are individual senders in your organization (mailboxes, mail users, and mail contacts). You can use any value that uniquely identifies the sender. For example:

    * Name
    * Display name
    * Alias
    * Distinguished name (DN)
    * Canonical DN
    * Email address
    * GUID

You can enter multiple senders separated by commas."

In the file/csv that I create, I enter the values, manually, separated by a comma. I have tried display name, email address, but not the others, like a GUID. I am setting the DL in 365, and the smtp addresses I am adding to the DL are in 365.

If I do <Import-Csv “C:\Test.csv” | Get-Member>, I can see that the TypeName: System.Management.Automation.PSCustomObject.

If I run this a little differently and try to specify the DL, i get the same error:
<Set-DistributionGroup -Identity DistList@abc.com -AcceptMessagesOnlyFrom (Import-Csv “C:\Test.csv” | select -ExpandProperty AcceptMessagesOnlyFrom)>

Set-DistributionGroup : Couldn’t find object “smpt1@abc.com, smtp2@abc.com”. Please make sure that it was spelled correctly or specify a different object.

It’s like PowerShell is thinking that everything in the -AcceptMessagesOnlyFrom column is one value.

By the way as I read my original post, I see that I made mistakes writing it here in this window: this is the command I ran originally/or now

<Import-Csv “C:\Test.csv” | ForEach-Object {Set-DistributionGroup -Identity _.PrimarySMTPAddress -AcceptMessagesOnlyFrom _.AcceptMessagesOnlyFrom}>

Thank you so much for helping me. I am not sure what you mean “get an excerpt of the script on how the parameter was written.” I did reply matt-bloomfield above.

It looks like you’re not using a custom delimiter for
CSV. Are the columns properly quoted so those values containing commas won’t accidentally be treated as columns themselves? I’m leaning towards your value being treated as a single address instead of a list.

I am very new to posting here and I was typing <> at the beginning and end of the code. I was wondering why the $_. was not appearing in the code. I needed to click the </> button. Sorry about that.

I am importing a test.csv, it has two columns:

  1. PrimarySMTPAddress (Contains Primary SMTP address of the Distribution list I want to change)
  2. AcceptMessagesOnlyFrom (Contains comma separated email address of people, smtp1, smtp2, smtp3, etc…
Import-Csv "C:\Test.csv" | ForEach-Object {Set-DistributionGroup -Identity $_.PrimarySMTPAddress -AcceptMessagesOnlyFrom $_.AcceptMessagesOnlyFrom}

In the columns, i have tried entering the email addresses like this.

  1. smtp1 smtp2 smtp3
  2. smtp1, smtp2, smtp3
  3. LastName, FirstName LastName, FirstName LastName, FirstName
  4. “LastName, FristName”, “LastName, FristName”, “LastName, FristName”,
  5. “FirstName LastName”, “FirstName LastName”, FirstName LastName"

And could probably keep adding to the list, GUID, DN, etc., etc… But every time I put more than one value in that cell, it appears like I am not telling PowerShell there is a list of names there. Powershell thinks that whatever i enter there is one value. So it responds with the error:

  1. Couldn’t find object “smtp1 smtp2 smtp3" or
  2. Couldn’t find object “smtp1, smtp2, smtp3” or
  3. Couldn’t find object “LastName, FirstName LastName, FirstName LastName, FirstName” or

Could it be that the “comma” in the CSV file is not being read correctly by powershell? I mean this seems like the easiest command in the world! I am editing the CSV in Excel, and then saving as a CSV. Could the font be messed up or something?

Commas in the column could be a part of the problem. However, you need to make sure that what’s being passed to the parameter is a collection of objects rather than one long string separated by commas. Format the addresses in the column with a semi-colon and try the code below.

Import-Csv “C:\Import.csv” | ForEach-Object {

    $params = @{

        Identity = $_.PrimarySMTPAddress
        AcceptMessagesOnlyFrom = $_.AcceptMessagesOnlyFrom -split ';'
        AcceptMessagesOnlyFromDLMembers = $_.AcceptMessagesOnlyFromDLMembers

    }

    Set-DistributionGroup @params

}
1 Like

That worked! Now I see why you wrote the original message that included the replace command. And I did remove the space. In the cell I tried:

  1. smtp1; smtp2 - did not work only entered the first value, but errored on the second. (there is a space after the :wink:
  2. smtp1;smtp2 (no space after the ; - this worked!) yay! so then i tried
  3. smtp1,smtp2 and that got an error.

Why is it that I have to define the separator? Are the parameters of the import-csv nullified because I created a variable?

You don’t need a custom delimiter. The issue was/is that this

"first, second, third"

in powershell is a single item, not 3. So given your original data simply change it to this

Import-Csv “C:\Import.csv” | ForEach-Object {

    $params = @{

        Identity = $_.PrimarySMTPAddress
        AcceptMessagesOnlyFrom = $_.AcceptMessagesOnlyFrom -split ','
        AcceptMessagesOnlyFromDLMembers = $_.AcceptMessagesOnlyFromDLMembers

    }

    Set-DistributionGroup @params

}

The act of splitting the string on whatever the separator makes it now an array of items. The only time you would need custom delimiters is if your data is not properly enclosed with quotes. Yours is properly quoted and quite obvious in the error

Set-DistributionGroup : Couldn’t find object “name1@abc.com name2@abc.com”. Please make sure that it was spelled correctly or specify a different object."

If it’s space delimited change it to split on the space.

Thanks everyone for helping me! I Get it. I have never used the -split parameter before! I hope to never forget this. That took me waaaaayyyy too many hours. I am not sure i would ever have solved this without the help you so freely gave to me. Thank you soooooo much…

1 Like