Check for blank values in csv or not?

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

Viewing 18 reply threads
  • Author
    Posts
    • #116617
      Participant
      Topics: 17
      Replies: 22
      Points: 210
      Rank: Participant

      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
      Topics: 1
      Replies: 1627
      Points: 3,051
      Helping Hand
      Rank: Community Hero
    • #116688
      Participant
      Topics: 2
      Replies: 510
      Points: 1,301
      Helping Hand
      Rank: Community Hero

      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
      Topics: 17
      Replies: 22
      Points: 210
      Rank: Participant

      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
      Senior Moderator
      Topics: 8
      Replies: 1136
      Points: 3,902
      Helping Hand
      Rank: Community Hero

      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
      Topics: 17
      Replies: 22
      Points: 210
      Rank: Participant

      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
      Senior Moderator
      Topics: 8
      Replies: 1136
      Points: 3,902
      Helping Hand
      Rank: Community Hero

      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
      Topics: 8
      Replies: 1271
      Points: 1,020
      Helping Hand
      Rank: Community Hero

      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
      Topics: 2
      Replies: 510
      Points: 1,301
      Helping Hand
      Rank: Community Hero

      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
      Topics: 17
      Replies: 22
      Points: 210
      Rank: Participant

      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
      Senior Moderator
      Topics: 8
      Replies: 1136
      Points: 3,902
      Helping Hand
      Rank: Community Hero

      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
      Topics: 17
      Replies: 22
      Points: 210
      Rank: Participant

      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
      Senior Moderator
      Topics: 8
      Replies: 1136
      Points: 3,902
      Helping Hand
      Rank: Community Hero

      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
      Topics: 8
      Replies: 1271
      Points: 1,020
      Helping Hand
      Rank: Community Hero

      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
      Topics: 17
      Replies: 22
      Points: 210
      Rank: Participant

      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
      Senior Moderator
      Topics: 8
      Replies: 1136
      Points: 3,902
      Helping Hand
      Rank: Community Hero

      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
      Topics: 17
      Replies: 22
      Points: 210
      Rank: Participant

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

    • #118680
      Participant
      Topics: 17
      Replies: 22
      Points: 210
      Rank: Participant

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

    • #119611
      Participant
      Topics: 17
      Replies: 22
      Points: 210
      Rank: Participant

      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')    
       
           }
      
      
Viewing 18 reply threads
  • The topic ‘Check for blank values in csv or not?’ is closed to new replies.