powershell write errors to CSV

lee roberts 161 Reputation points
2021-12-06T16:37:15.207+00:00

Good Afternoon,
I have hundreds of distributions list to recreate in exchange online. These DL's thousand's of contacts in them. I have a simple script it import them, but i would like to right any error's that can't be imported to a CSV file so i can easily see. I no i can you the start-transcript to see everything but don't want to have to go through all info as there is so many. Is there a way this can be done? The script I'm using to import is below

Import-CSV "CSV PATH" | Foreach-Object { Add-DistributionGroupMember -Identity "DL NAME" -Member $_.PrimarySMTPAddress }
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,282 questions
0 comments No comments
{count} votes

Accepted answer
  1. Rich Matheisen 44,006 Reputation points
    2021-12-06T20:03:22.047+00:00

    The only kind of error you're likely to encounter is that the e-mail address doesn't exist in any AD object. That might be an empty record in your CSV, a misspelling of the email address (an address that just holds spaces, or a badly formatted address), or a duplicated email address in the CSV.

    You could do a cursory check for empty/all-spaces addresses, and a properly formatted email address:
    it has only one "@" character
    it doesn't begin or end with a "."
    there's a value to the left of the only "@"
    the value to the left of the "@" is composed of only ASCII characters
    the domain name has at least one "."

    You could go further, but I think you're really only interested in whether or not the email address exists in the directory. If that's the case:

    $DlName = "DL NAME"
    Import-CSV "CSV PATH" |
        Foreach-Object { 
            $TryThis = $_
            Try{
                Add-DistributionGroupMember -Identity $DlName -Member $_.PrimarySMTPAddress -ErrorAction STOP |
                    Out-Null
            }
            Catch{
                [PSCustomObject]@{
                    DLName = $DlName
                    PrimarySMTPAddress = $TryThis
                    Problem = ($_ | Out-String)
                }
            }
        } | Export-CSV c:\junk\BadSMTPAddresses.csv -NoTypeInformation
    
    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. DaveK 1,846 Reputation points
    2021-12-06T17:02:13.837+00:00

    You could maybe run the Add-DistributionGroupMember in a Try/Catch block to trap any which produce errors and write the PrimarySMTPAddress with a export-csv -append in the catch block. I'm not overly familiar with what errors cause what kind of termination but under the presumption that a typo in the PrimarySMTPAddress etc would cause a trappable error, it might be a quick way to save a list of failed attempts. There maybe a more graceful way but I would start with something like this before digging further.

    0 comments No comments

  2. MotoX80 30,916 Reputation points
    2021-12-06T18:22:25.16+00:00

    It all depends on what kind of errors you are getting. I suggest that you start by writing a validation script that looks at each entry and splits your master csv off into good and bad csv files. If you can fix a record as you process it, then do that. You can then import the "good csv" and create the DL's. If you can't programatically fix the "bad csv" records, then you will need to fix them manually, or just ignore that data.

    $GoodRecords = @()
    $BadRecords = @()
    Import-CSV "CSV PATH" | Foreach-Object {
        $Bad = $False                                           # assume the data is good
        if ($_.PrimarySMTPAddress -eq "") {
            "Address is bad {0}" -f $_.PrimarySMTPAddress
            $Bad = $true                                        # nope, this is a bad data record
        }
        if ($_.Group -eq "Fince") {                          # Example of data that could be fixed. 
            $_.Group = "Finance"                            # Fix spelling error
            "Spelling fixed for Finance."
        }
    
    
        # More validation tests go here.
    
    
        If ($Bad) { 
            $BadRecords += $_ 
        } else {
            $GoodRecords += $_ 
        }
    }
    $GoodRecords | Export-Csv -NoTypeInformation -Path "C:\Data\Good.csv" 
    $BadRecords  | Export-Csv -NoTypeInformation -Path "C:\Data\Bad.csv" 
    
    0 comments No comments

  3. lee roberts 161 Reputation points
    2021-12-07T18:52:16.987+00:00

    Thank you all for your assistance.

    You have made may life immeasurably easier

    0 comments No comments