Check for blank values in csv or not?

Welcome Forums General PowerShell Q&A Check for blank values in csv or not?

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

 
Participant
1 month, 2 weeks ago.

  • Author
    Posts
  • #116617

    Participant
    Points: 30
    Rank: Member

    Running the commands below will generate errors when there are blank values in the csv, it does continue through the loop and completes. Since it continues through the loop is there a need to check for blank values? Will this slow the script down?

    I eventually want to build this out to include all the ad attributes, then I can just update the csv file when I need to make bulk changes. This would leave lots of blank values in the csv. I'm just not sure if there are any cons to this?

    Import-Module ActiveDirectory
    $inputFile = Import-CSV c:\it\bulk_edit.csv
    foreach($line in $inputFile)
    {Set-ADUser -Identity $line.samaccountname -add @{pager=$line.pager}}
    
  • #116671

    Participant
    Points: 263
    Helping Hand
    Rank: Contributor
  • #116688

    Participant
    Points: 292
    Helping Hand
    Rank: Contributor

    It is generally faster to avoid errors than to cause and/or suppress them, yes. One possible approach is something like...

    $SkipAttributes = @('SamAccountName')
    foreach ($Line in $InputFile) {
        $Attributes = @{} # blank hashtable to start
        $PropertiesToCheck =  $Line.PSObject.Properties.Name | Where-Object {$_ -notin $SkipAttributes}
        foreach ($Property in $Line.PSObject.Properties.Name) {
            if ($Line.$Property.Trim()) {
                # if the property has anything in it other than blank space, add it in!
                $Attributes.Add($Property, $Line.$Property)
            }
        }
        
        Set-ADUser -Identity $Line.SamAccountName -Add $Attributes
    }
  • #118000

    Participant
    Points: 30
    Rank: Member

    This code works for updating user attributes. I'd like to use the same script to clear or remove attributes but not sure how to do that. The help on Set-AdUser -Instance says it only updates object properties. I tried adding $null to the csv values but that didn't do what I hoped.

     $props = 'GivenName','Surname','DisplayName','OfficePhone','City','State','Country',
             'MobilePhone','EmailAddress','Title','Department','Company','ipPhone'
    Import-CSV c:\it\bulk_edit.csv |
        ForEach-Object{
            if($u = Get-AdUser $_.samaccountname -Properties $props)
            {
                $u.GivenName = $_.firstname
                $u.Surname = $_.lastname
                $u.DisplayName = $_.displayname
                $u.OfficePhone = $_.Mobilenumber
                $u.City = $_.city
                $u.State = $_.state
                $u.Country = $_.Country
                $u.MobilePhone = $_.mobilephone
                $u.EmailAddress = $_.emailaddress
                $u.Title = $_.jobtitle
                $u.Department = $_.department
                $u.Company = $_.company
                $u.ipPhone = $_.ipphone
                Set-AdUser -Instance $u
            }
            else
                {
                Write-Host 'User not found'
                 }
    
        }
    
    
  • #118029

    Participant
    Points: 884
    Helping Hand
    Rank: Major Contributor

    You can use Set-ADObject for this, an example from the documentation is

    C:\PS>Set-ADObject "cdadd380-d3a8-4fd1-9d30-5cf72d94a056" -Remove @{url="www.contoso.com"} -Replace @{description="Antonio Alwan (European Manager)"}
    

    And the documentation is here

  • #118059

    Participant
    Points: 30
    Rank: Member

    Could I check if the csv has a $null value if so run the -clear parameter. if it's anything other than $null set it to that value. Just not sure how to add that into my existing code and how to avoid $null being adding back as a value.

  • #118063

    Participant
    Points: 884
    Helping Hand
    Rank: Major Contributor

    Yes, its possible, but I would like you to take some more time, put some thoughts, I can give you hints.

    • Read the CSV
    • Iterate for each row
    • Check the value for null
    • If null calll -clear
    • Else call Set.

    Let us know if you still find difficulty, we are here to help you.

  • #118071

    Participant
    Points: 324
    Helping Hand
    Rank: Contributor

    Splatting is what you want:

    #Set default switches that will always have a value
    #that you are going to pass wot Set-ADUser
    $setParams = @{
        GivenName    = $_.firstname
        Surname      = $_.lastname
        DisplayName  = $_.displayname
        OfficePhone  = $_.Mobilenumber
        City         = $_.city
        State        = $_.state
        Country      = $_.Country
        EmailAddress = $_.emailaddress
        Title        = $_.jobtitle
        Department   = $_.department
        Company      = $_.company
    }
    
    #if the user has a value in mobile phone, then add the Mobile phone attribute
    if ( $_.mobilephone ) { $setParams.Add('MobilePhone', $_.mobilephone) }
    
    #Custom params are a little more difficult, but you are still just working
    #with hashtables.  This would hold all attributes that don't have switches
    #in Set-ADUser
    $customParams = @{}
    #IP Phone is not a supported switch, if you want to set it,
    #it would need to be done with a custom hashtable with -Add or -Replace
    if ( $_.ipphone ) { $customParams.Add('ipPhone', $_.ipphone) }
    #See if we added custom params, if we did then add the Replace switch
    #with those attributes
    if ($customParams) { $setParams.Add('Replace',$customParams) }
    
    Set-AdUser @setParams
    
  • #118123

    Participant
    Points: 292
    Helping Hand
    Rank: Contributor

    Rob, that won't work for any parameters that don't have a defined parameter on the cmdlet. Only a subset of all settable properties have explicit parameters on the cmdlet, the rest being relegated to being passed into the cmdlet as a hashtable to -Add or -Replace.

  • #118264

    Participant
    Points: 30
    Rank: Member

    Yes, its possible, but I would like you to take some more time, put some thoughts, I can give you hints.

    • Read the CSV
    • Iterate for each row
    • Check the value for null
    • If null calll -clear
    • Else call Set.

    Let us know if you still find difficulty, we are here to help you.

    Yea...I'm trying. This is not easy for me but I'm trying. I started from scratch and already ran into errors

    Import-CSV c:\it\bulk_edit.csv | ForEach-Object {
    
    $parma = @{
    
    samaccountname = $_.samaccountname
    pager          = $_.pager
    state          = $_.state
                }
    
            Set-ADUser -instance $parma
    
    }
    
    

    This gives the error

    Set-ADUser : Objects provided to this cmdlet must be search results.
    At line:13 char:9
    + Set-ADUser -instance $parma
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidArgument: (:) [Set-ADUser], ArgumentException
    + FullyQualifiedErrorId : ActiveDirectoryCmdlet:System.ArgumentException,Microsoft.ActiveDirectory.Management.Commands.SetADUser

  • #118296

    Participant
    Points: 884
    Helping Hand
    Rank: Major Contributor

    Below is an untested example.

    $ParamHash = @{}
    
    #For one property
    Import-CSV c:\it\bulk_edit.csv | ForEach-Object -Process {
       if($null -eq $_.State){
            $ParamHash.Clear = @($_.State)
        }
        else{
            $ParamHash.State = $_.State
        }
        $ParamHash.Identity = $_.Identity
    }
    
    Set-AdUser @ParamHash
    
    #Or
    #For multiple properties
    $CSV = Import-Csv c:\CsvPath.csv 
    $Property = $CSV | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name
    $CSV | ForEach-Object -Process {
        $Item = $_
        $Property | ForEach-Object -Process {
            if($Null -eq $_.$Item){
                $ParamHash.Clear = $_.$Item
            }
            else{
                $ParamHash.$Item = $_.$Item
            }
        }
    }
    
    Set-AdUser @ParamHash
    
  • #118519

    Participant
    Points: 30
    Rank: Member

    So what I'm trying to do is check for a value of remove if that exists I want to run set-aduser -clear if not then I'll run set-aduser -instance $u to add the values.

    here is the csv

    samaccountname,state,pager
    testguy,KU,nopager
    testguy2,MO,remove

    If I output $u I can see the testguy2 does have remove set for the pager.

    BUT when I try to check $u for the word remove it never finds it.

    $props = 'GivenName','Surname','DisplayName','OfficePhone','City','State','Country',
             'MobilePhone','EmailAddress','Title','Department','Company','pager'
    
    Import-CSV c:\it\bulk_edit.csv |
        ForEach-Object{
    
           $u = Get-ADUser $_.samaccountname -Properties $props
    
                $u.State = $_.state
                $u.pager = $_.pager           
    
             #  Set-AdUser -Instance $u
    
             if ($u -like '*remove*') 
                {Write-host 'Yes it contains remove'}
    
             else 
    
             {write-host 'nope'}
    
                }
    
    
  • #118521

    Participant
    Points: 884
    Helping Hand
    Rank: Major Contributor

    You cannot do,

    if ($u -like '*remove*') 
    

    You have to select the property from the object $u to check

    if ($u.state -like '*remove*') 
    
  • #118539

    Participant
    Points: 324
    Helping Hand
    Rank: Contributor

    Rob, that won't work for any parameters that don't have a defined parameter on the cmdlet. Only a subset of all settable properties have explicit parameters on the cmdlet, the rest being relegated to being passed into the cmdlet as a hashtable to -Add or -Replace.

    @Joel,

    That is what this code was doing:

    #Custom params are a little more difficult, but you are still just working
    #with hashtables.  This would hold all attributes that don't have switches
    #in Set-ADUser
    $customParams = @{}
    #IP Phone is not a supported switch, if you want to set it,
    #it would need to be done with a custom hashtable with -Add or -Replace
    if ( $_.ipphone ) { $customParams.Add('ipPhone', $_.ipphone) }
    #See if we added custom params, if we did then add the Replace switch
    #with those attributes
    if ($customParams) { $setParams.Add('Replace',$customParams) }
    
  • #118662

    Participant
    Points: 30
    Rank: Member

    Arg....thought I had this figured out. It worked when I had one if statement but when I added a second it pukes this out

    cmdlet ForEach-Object at command pipeline position 2
    Supply values for the following parameters:
    Process[0]:

    $props = 'GivenName','Surname','DisplayName','OfficePhone','City','State','Country',
             'MobilePhone','EmailAddress','Title','Department','Company','pager'
    
    Import-CSV c:\it\bulk_edit.csv |
        ForEach-Object
        {
    
           $u = Get-ADUser $_.samaccountname -Properties $props
    
                $u.State = $_.state
                $u.pager = $_.pager  
                         
             if ($u.state -contains 'remove') 
    
                {Set-ADUser $_.samaccountname -clear state} else {Set-ADUser -identity $_.samaccountname -replace @{state=$u.state}}
    
             if ($u.pager -contains 'remove') 
    
                {Set-ADUser $_.samaccountname -clear pager} else {Set-ADUser -identity $_.samaccountname -replace @{pager=$u.pager}}
    
      
         }
    
  • #118668

    Participant
    Points: 884
    Helping Hand
    Rank: Major Contributor

    its not because of second if statement, you have to put the end flower/curly bracket after ForEach-Object cmdlet.

    ForEach-Object { … }
    #or
    ForEach-Object -Process { … }
    
    # {} script block is value for -Process parameter which is mandatory and should be on the same line.
    

    I would strongly recommend you start reading the help documentation Get-Help ForEach-Object -Full. You have to invest some time in learning the basics, else yourself will spent a lot of time in understanding similar things foreach requirements.

  • #118669

    Participant
    Points: 30
    Rank: Member

    kvprasoon....it is in the curly bracket.

  • #118680

    Participant
    Points: 30
    Rank: Member

    Nevermind...I see I had it in the wrong place.

  • #119611

    Participant
    Points: 30
    Rank: Member

    Ok.. This code does everything I need it to do. The only problem is what I originally asked, this will spit out errors when the csv does not contain a value for any of these props. It continues and completes just fine.

    From my research, I would need to check every value for $null remove it or add it to a new hashtable then I'd still need to run another check for the word remove and possibly add it to a separate hashtable. It just seems like a lot of overhead just to eliminate errors. So I'm torn if I should just suppress the errors or call it good.

    I did play around with checking the value like below but when you look at the hashtable it doesn't seem to have done anything. The blank value is still in the table.

    if ($user.pager) {$hash.add("pager", $($user.pager))}

    Name Value
    —- —–
    state AU
    pager remove
    samaccountname testguy
    state Ma
    pager pager1234
    samaccountname testguy2
    state mo
    pager
    samaccountname erererer

    
      $props = 'description','office','OfficePhone','mail','WWWHomePage','StreetAddress','POBox',
    'l','st','PostalCode','Country','ProfilePath','ScriptPath','HomeDirectory','HomePhone','Pager','Mobile','ipphone','otherFacsimileTelephoneNumber','otherHomePhone','otheripphone',
    'otherMobile','otherpager','title','company','department','proxyaddresses'
    Import-CSV c:\it\bulk_edit.csv |
        ForEach-Object {
           $u = Get-ADUser $_.samaccountname -Properties $props
            #the $_. can match what is in the csv file
                $u.description = $_.description
                $u.office = $_.office
                $u.OfficePhone = $_.officephone
                $u.mail = $_.officephone
                $U.WWWHomePage = $_.webpage
                $U.StreetAddress = $_.street
                $u.POBox = $_.pobox
                $u.l = $_.city
                $u.st = $_.stateprovince
                $u.PostCode = $_.zip
                $u.Country = $_.county
                $u.ProfilePath = $_.profilepath
                $u.ScriptPath = $_.logonscript
                $u.HomeDirectory = $_.homedirectory #needs to be drive letter/folder
                $u.HomePhone = $_.homephone
                $u.Pager = $_.pager
                $u.Mobile = $_.mobile
                $u.ipphone = $_.ipphone
                $u.otherFacsimileTelephoneNumber = $_.otherfaxnumber
                $u.otherHomePhone = $_.otherhomephone
                $u.otheripphone = $_.otheripphone
                $u.otherMobile = $_.othermobile
                $u.otherpager = $_.otherpager
                $u.title = $_.jobtitle
                $u.company = $_.company
                $u.department = $_.department
                $u.proxyaddresses = $_.proxyaddresses
    
             if ($u.description -contains 'remove')
                {Set-ADUser $_.samaccountname -clear @{description=$u.description}} else {Set-ADUser -identity $_.samaccountname -replace @{description=$u.description}}   
             if ($u.office -contains 'remove')
                {Set-ADUser $_.samaccountname -clear @{office=$u.office}} else {Set-ADUser -identity $_.samaccountname -replace @{office=$u.office}}       
             if ($u.state -contains 'remove')    
     
         }
    
    

You must be logged in to reply to this topic.