Add error column to CSV output

Welcome Forums General PowerShell Q&A Add error column to CSV output

This topic contains 3 replies, has 3 voices, and was last updated by

 
Participant
6 months, 2 weeks ago.

  • Author
    Posts
  • #101028

    Participant
    Points: 0
    Rank: Member

    I am retrieving AD accounts and properties using a custom attribute. This is working fine, however I want to add a column to the csv called Error for accounts that do not exist. I am trying to incorporate this into one csv file. It is a pretty large dataset of roughly 800K users, any recommendations for increasing efficiency would be appreciated. Before adding the if/else script took 12 hours to run. I tried using streamwriter to improve performance but no gain. Script doesn't work in current form. I understand I am trying to add content that doesn't match any properties in the CSV, however I am not sure how to fix it.

    $B = Get-Content C:\UserList.txt
    
    foreach ($U in $B)
    {
        $User = get-aduser -Filter {CA -like $U} -Properties EmailAddress,telephoneNumber,SamAccountName,sn,givenName,Initials,DisplayName,CA -Server domainname | Select-Object -Property EmailAddress,telephoneNumber,SamAccountName,sn,givenName,Initials,DisplayName,CA
         
         If ($User -eq $null)
            {
    
                $User.Error = "$U not found" | Export-Csv C:\Results.txt -NoTypeInformation -Append -Force
    
            }
    
        Else {$User | Export-Csv C:\Results.txt -NoTypeInformation -Append}
        
    
    }
  • #101031

    Participant
    Points: 17
    Rank: Member

    Hello, this is my first post so I hope it is useful. You can try something like the below.

    $B = Get-Content C:\List.txt
    
    $collection = foreach ($U in $B) {
        try {
            $user = Get-ADUser $U -Properties EmailAddress, telephoneNumber, SamAccountName, sn, givenName, Initials, DisplayName
            $properties = @{EmailAddress = $user.emailaddress
                TelephoneNumber = $user.telephonenumber
                SamAccountName = $user.samaccountname
                sn = $user.sn
                GivenName = $user.givenname
                Initials = $user.initials
                DisplayName = $user.displayname
                NotFound = $null
                }
            New-Object -TypeName PSObject -Property $properties
        }
    
        catch {
            $properties = @{ 
                EmailAddress = $null
                TelephoneNumber = $null
                SamAccountName = $null
                sn = $null
                GivenName = $null
                Initials = $null
                DisplayName = $null
                NotFound = $U
                }
            New-Object -TypeName PSObject -Property $properties
    
        }
      
    }         
    
    $collection | Export-Csv C:\output.csv -NoTypeInformation
    
    
    
  • #101034

    Participant
    Points: 17
    Rank: Member

    Hello, this is my first post so I hope it is useful. You can try something like the below:

    $B = Get-Content C:\UserList.txt
    
    $collection = foreach ($U in $B) {
        try {
            $user = Get-ADUser $U -Properties EmailAddress, telephoneNumber, SamAccountName, sn, givenName, Initials, DisplayName
            $properties = @{EmailAddress = $user.emailaddress
                TelephoneNumber = $user.telephonenumber
                SamAccountName = $user.samaccountname
                sn = $user.sn
                GivenName = $user.givenname
                Initials = $user.initials
                DisplayName = $user.displayname
                NotFound = $null
                }
            New-Object -TypeName PSObject -Property $properties
        }
    
        catch {
            $properties = @{ 
                EmailAddress = $null
                TelephoneNumber = $null
                SamAccountName = $null
                sn = $null
                GivenName = $null
                Initials = $null
                DisplayName = $null
                NotFound = $U
                }
            New-Object -TypeName PSObject -Property $properties
    
        }
      
    }         
    
    $collection | Export-Csv C:\UserList.csv -NoTypeInformation
    
    
    
  • #101058

    Participant
    Points: 265
    Helping Hand
    Rank: Contributor
    $User.Error = "$U not found" | Export-Csv C:\Results.txt -NoTypeInformation -Append -Force

    This will be why this doesn't work. You need to split this line in two:

    $User.Error = "$U not found"
    $User | Export-Csv C:\Results.txt -NoTypeInformation -Append -Force

    Now, as to the other quandary... your main issue here is that you're opening and closing this file several times a second. There's absolutely no need for that. Just compile your objects into an array, and have the cmdlet build the file all at once before saving to disk. -Append exists for when you have an existing file that you want to tack on a bunch to, not really meant for building files one piece at a time and saving in between. That much disk I/O will slow everything down, no matter how you do it!

    Also, please consider renaming your variables. This is going to give you some grief later. If you come back to this in a month, and already
    have to scan through half the script to remember what the variable is for, you need a better name. There's nothing wrong with
    verbose names; the Assembly days are dwindling, and the ability to be verbose is quite valuable.

    Additionally, you'll run into trouble trying to put an object with no properties into a CSV file. CSV files need to have all objects with the same set of properties, so you'll have to go through and set them to $null in a custom object, and then tack on your error property. All objects will need an error property for it to be exported properly, as well, so we can add it in the Select-Object clause.

    $UserList = Get-Content -Path 'C:\UserList.txt'
    # Assign a variable to the output of the loop to gather all outputted data into a neat array
    $OutputData = foreach ($User in $UserList) { 
        $PropertyList = 'EmailAddress', 'TelephoneNumber', 'SamAccountName', 'SN', 'GivenName', 'Initials', 'DisplayName', 'CA'
        # Braces for -Filter is common, but will give you a lot of grief in many, 
        # many cases. Just use a plain string; quotes, no braces.
        $UserObject = Get-Aduser -Filter "CA -like $User" -Properties $PropertyList -Server $DomainName | 
            Select-Object -Property $PropertyList, @{
                Name       = "Error"
                Expression = { $null }
            } # Adding the error as a null property. If you don't do this, the export will ignore it unless the VERY FIRST object has this property set.
         
         if ($UserObject) {
            # 'Drop' the object to output to have the loop pick it up and wrap it into an array for $OutputData variable
            # Alternate is to be explicit: Write-Output $UserObject
            $UserObject
         }
         else {
            $NullUser = @{}
            foreach ($Property in $PropertyList) {
                $NullUser[$Property] = $null
            }
            $NullUser['Error'] = "$User not found"
            # Again, output the null user as an object (it's been a simple hashtable up to now)
            [PsCustomObject] $NullUser
         }
    }
    # Export the whole thing at once, only one larger disk I/O event 
    # instead of thousands of tiny ones (less OS file access overhead)
    $OutputData | Export-Csv -Path 'C:\Results.txt' -NoTypeInformation

The topic ‘Add error column to CSV output’ is closed to new replies.