locked
New-ADUser and Importing from CSV with Null Fields RRS feed

  • Question

  • Hi,

    I am new to Powershell so please forgive me if this is a stupid question.  My starting point was a script I found by HicanNL on http://gallery.technet.microsoft.com/scriptcenter/PowerShell-Create-Active-7e6a3978/view/Discussions to import users into AD via a CSV file.  The intent of this script is to help us build test and demonstration environments quickly.  Each environment what fields we need to popoulate could be different so therefore I am trying to create one master spreadsheet with all the possible columns that New-ADUser supports (as found here http://technet.microsoft.com/en-us/library/hh852238).

    The problem I am having is that even though the documentation says nulls are allowed for some of the parameters, if I have a null in my spreadsheet (saved as CSV) it doesn't like it.  I guess it sees the parameter in the import and decides that if I have the parameter, I had better have a value for it. For instance, I might want AccountExpirationDate to be set one time and therefore we will put a date in the field, but next time we might want to keep it blank.  I am trying to avoid juggling dozens of templates and modifying the PS1 file each time.

    So what is my best method to ignore on a column by column (field by field) on a import by import basis values that may be null?  I inserted the relevant portion of my code below.  Be kind, I am not a developer.

    Thanks.

    Alan

    Import-CSV $newpath | ForEach-Object  { 
        
        If(!$exists)
        {
          $i++
     	  
    	  # Needed to import the password to prevent errors since it is plain text in the CSV file.
    	  $AccountPasswordSecure = ConvertTo-SecureString $_.AccountPassword -AsPlainText -force
      	  	
    	  #Converts the false/true 0/1 values from the CSV which are seen as a string to boolean which Powershell requires.	
    	  $_.UserMustChangePasswordAtLogon = [System.Convert]::ToBoolean("True")
    	  $_.UserCannotChangePassword = [System.Convert]::ToBoolean("True")
    	  $_.PasswordNeverExpires = [System.Convert]::ToBoolean("True")
    	  $_.AllowReversiblePasswordEncryption = [System.Convert]::ToBoolean("True")
    	  $_.AccountEnabled = [System.Convert]::ToBoolean("True")
    	  $_.SmartcardLogonRequired = [System.Convert]::ToBoolean("True")
    	  $_.AccountNotDelegated = [System.Convert]::ToBoolean("True")
    	  $_.CompoundIdentitySupported = [System.Convert]::ToBoolean("True")
    	  $_.PasswordNotRequired = [System.Convert]::ToBoolean("True")
    	  $_.AccountEnabled = [System.Convert]::ToBoolean("True")
    	  	    
    	  # OtherAttributes is a Hash Table. This converts the strings in the CSV file to a Hash Table.
    	  $OtherAttribuesHash = ConvertFrom-StringData $_.OtherAttributes
    	  
    	  
    	  # The code below maps the column names in the CSV file to the actual parameters names needed for import. 
    	  # Blank values will not be imported.
    	   New-ADUser `
    			-GivenName $_.FirstName `
    			-Initials $_.Initials `
    			-Surname $_.LastName `
    			-Name $_.FullName `
    			-DisplayName $_.DisplayName `
    			-Description $_.Description `
    			-Office $_.Office `
    			-OfficePhone $_.OfficePhone `
    			-EmailAddress $_.EmailAddress `
    			-HomePage $_.WebPage `
    			-StreetAddress $_.StreetAddress `
    			-POBox $_.POBox `
    			-City $_.City `
    			-State $_.StateProvince `
    			-PostalCode $_.ZIPPostalCode `
    			-Country $_.CountryRegion `
    			-UserPrincipalName $_.UserLoginName `
    			-SamAccountName $_.UserLoginName2000 `
    			-AccountPassword $AccountPasswordSecure `
    			-ChangePasswordAtLogon $_.UserMustChangePasswordAtLogon `
    			-CannotChangePassword $_.UserCannotChangePassword `
    			-PasswordNeverExpires $_.PasswordNeverExpires `
    			-AllowReversiblePasswordEncryption $_.AllowReversiblePasswordEncryption `
    			-Enabled $_.AccountEnabled `
    			-SmartcardLogonRequired $_.SmartcardLogonRequired `
    			-AccountNotDelegated $_.AccountNotDelegated `
    			-KerberosEncryptionType $_.KerberosEncryptionType `
    			-CompoundIdentitySupported $_.CompoundIdentitySupported `
    			-PasswordNotRequired $_.PasswordNotRequired `
    			-AccountExpirationDate $_.AccountExpirationDate `
    			-ProfilePath $_.ProfilePath `
    			-ScriptPath $_.ScriptPath `
    			-HomeDirectory $_.HomeDirectory `
    			-HomeDrive $_.HomeDrive `
    			-HomePhone $_.HomePhone `
    			-MobilePhone $_.MobilePhone `
    			-Fax $_.Fax `
    			-Title $_.JobTitle `
    			-Department $_.Department `
    			-Company $_.Company `
    			-Manager $_.Manager `
    			-Division $_.Division `
    			-EmployeeID $_.EmployeeID `
    			-EmployeeNumber $_.EmployeeNumber `
    			-Organization $_.Organization `
    			-OtherAttributes $OtherAttribuesHash `
    			-OtherName $_.OtherName `
    			-AuthType $_.AuthType `
    			-Certificates $_.Certificates `
    			-Credential $_.Credential `
    			-Instance $_.Instance `
    			-LogonWorkstations $_.LogonWorkstations `
    			-PassThru $_.PassThru `
    			-Path $_.Path `
    			-PrincipalsAllowedToDelegateToAccount $_.PrincipalsAllowedToDelegateToAccount `
    			-Server $_.Server `
    			-ServicePrincipalNames $_.ServicePrincipalNames `
    			-TrustedForDelegation $_.TrustedForDelegation `
    			-Type $_.Type `
    			-Confirm $_.Confirm `
    			-WhatIf $_.WhatIf 
    
          $output | Out-File $log -append
        }
        Else
        {
          "Record skipped as account already exists or other error exists: " + $_.CN | Out-File $log -append
        }
      }


    My Ramblings @ http://alanwhitehouse.wordpress.com and My Tweets @ http://www.twitter.com/alanwhitehouse

    Monday, January 14, 2013 2:09 PM

Answers

  • I think this should be the correct idea:

    $Properties = 'Initials', 'name', 'description', 'state'
    
    Import-CSV $newpath | ForEach-Object {
    
    $list = @{}
    foreach ($property in $properties){
        if($_.$property){
            $list.$Property = $_.$property
        }
    }
    
    New-ADUser @list
    
    }

    • Proposed as answer by Kazun Monday, January 14, 2013 3:15 PM
    • Marked as answer by Alan Whitehouse Tuesday, January 15, 2013 3:35 PM
    Monday, January 14, 2013 2:32 PM
  • First column name is "Last Name",but you can redefine this using correct sequence in variable $properties and eq ad property:

    #"Last Name - SurName","Email - Mail" $properties = "SurName","Mail" Get-Content $newpath | Select-Object -Skip 1 | ConvertFrom-CSV -Header $Properties



    • Edited by Kazun Monday, January 14, 2013 3:25 PM
    • Marked as answer by Alan Whitehouse Tuesday, January 15, 2013 3:35 PM
    Monday, January 14, 2013 3:24 PM

All replies

  • I think this should be the correct idea:

    $Properties = 'Initials', 'name', 'description', 'state'
    
    Import-CSV $newpath | ForEach-Object {
    
    $list = @{}
    foreach ($property in $properties){
        if($_.$property){
            $list.$Property = $_.$property
        }
    }
    
    New-ADUser @list
    
    }

    • Proposed as answer by Kazun Monday, January 14, 2013 3:15 PM
    • Marked as answer by Alan Whitehouse Tuesday, January 15, 2013 3:35 PM
    Monday, January 14, 2013 2:32 PM
  • Thank you for the reply.  I get how the $Property will work, but where in that snippit of code does it tell whether or not something is null and to ignore it this time.   For instance, initials might be null this time, but not next time. Again, not a developer so bear with my stupidity.

    Alan


    My Ramblings @ http://alanwhitehouse.wordpress.com and My Tweets @ http://www.twitter.com/alanwhitehouse

    Monday, January 14, 2013 2:55 PM
  •  if($_.$property){
    this fragment tests if current line from csv has $property property non-null or non-empty
    Monday, January 14, 2013 2:59 PM
  • Thanks.  Final question and I will give this a shot.  In my Excel/CSV file the column headers are user friendly and do not exactly match up with the New-ADUser property name.  For instance the formal property is "Surname" and I have called it "Last Name" in the Excel sheet.  Best way to handle that in this new world?

    My Ramblings @ http://alanwhitehouse.wordpress.com and My Tweets @ http://www.twitter.com/alanwhitehouse

    Monday, January 14, 2013 3:09 PM
  • First column name is "Last Name",but you can redefine this using correct sequence in variable $properties and eq ad property:

    #"Last Name - SurName","Email - Mail" $properties = "SurName","Mail" Get-Content $newpath | Select-Object -Skip 1 | ConvertFrom-CSV -Header $Properties



    • Edited by Kazun Monday, January 14, 2013 3:25 PM
    • Marked as answer by Alan Whitehouse Tuesday, January 15, 2013 3:35 PM
    Monday, January 14, 2013 3:24 PM
  • Thanks to all.  I will give the suggestions a try and post my results.

    My Ramblings @ http://alanwhitehouse.wordpress.com and My Tweets @ http://www.twitter.com/alanwhitehouse

    Monday, January 14, 2013 3:37 PM
  • I seem to have it almost working but the import does not complete.  However each time I run it and no matter what combination of values I put in the system, I get the following:

    ****

    New-ADUser : 'PasswordNeverExpires' for this account is set to true. The account will not be required to change the
    password at next logon.
    At C:\Users\Administrator\desktop\create_ad_users\ImportUsers.ps1:57 char:2
    +     New-ADUser @list
    +     ~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidArgument: (:) [New-ADUser], ArgumentException
        + FullyQualifiedErrorId : ActiveDirectoryCmdlet:System.ArgumentException,Microsoft.ActiveDirectory.Management.Comm
       ands.NewADUser

    ****

    Here is my current full code:

    #Loads the right Powershell Cmdlet for Active Directory
    Import-Module ActiveDirectory
    
    # Determines the current directory the .ps1 is being run from and looks to that directory for the CSV file
    $directory = Split-Path -parent $MyInvocation.MyCommand.Definition
    $importfile = $directory + "\UserList.csv"
    
    # Define variables
    $log      = $directory + "\Import.log"
    
    # This is the the location you want the users to be created in the AD tree
    $location = "OU=Test,OU=Users,DC=test,DC=local"
    
    # Inport Code
    Function importUsers
    {
      
      #Each of these is a possible parameter that can be passed with the New-ADUser command and that are available to populate in the master Excel sheet for importing users.
      $Properties = "GivenName","Initials","Surname","Name","DisplayName","Description","Office","OfficePhone","EmailAddress","HomePage","StreetAddress","POBox","City","State","PostalCode","Country","UserPrincipalName","SamAccountName","AccountPassword","ChangePasswordAtLogon","CannotChangePassword","PasswordNeverExpires","AllowReversiblePasswordEncryption","Enabled","SmartcardLogonRequired","AccountNotDelegated","KerberosEncryptionType","CompoundIdentitySupported","PasswordNotRequired","AccountExpirationDate","ProfilePath","ScriptPath","HomeDirectory","HomeDrive","HomePhone","MobilePhone","Fax","Title","Department","Company","Manager","Division","EmployeeID","EmployeeNumber","Organization","OtherName","AuthType","Certificates","Credential","Instance","LogonWorkstations","PassThru","Path","PrincipalsAllowedToDelegateToAccount","Server","ServicePrincipalNames","TrustedForDelegation","Type","Confirm","WhatIf"
    
      Get-Content $importfile | Select-Object -Skip 1 | ConvertFrom-CSV -Header $properties
      
      Import-CSV $importfile | ForEach-Object  { 
      
    	# This transformation is needed to import the password to prevent errors since it is plain text in the CSV file.
    	$_.AccountPassword = ConvertTo-SecureString $_.AccountPassword -AsPlainText -force
      
    	#These transofrmations convert the false/true 0/1 values from the CSV which are seen as a string to boolean which Powershell requires.	
    	$_.PasswordNeverExpires = [System.Convert]::ToBoolean("True")
    	$_.SmartcardLogonRequired = [System.Convert]::ToBoolean("True")
    	$_.AccountNotDelegated = [System.Convert]::ToBoolean("True")
    	$_.PasswordNotRequired = [System.Convert]::ToBoolean("True")
    	$_.AllowReversiblePasswordEncryption = [System.Convert]::ToBoolean("True")
    	$_.CompoundIdentitySupported = [System.Convert]::ToBoolean("True")
    	$_.Enabled = [System.Convert]::ToBoolean("True")
    	$_.CannotChangePassword = [System.Convert]::ToBoolean("True")
    	$_.ChangePasswordAtLogon = [System.Convert]::ToBoolean("True")
    	$_.TrustedForDelegation = [System.Convert]::ToBoolean("True")
      
      	# OtherAttributes is a Hash Table. This converts the strings in the CSV file to a Hash Table.
    	$_.OtherAttributes = ConvertFrom-StringData $_.OtherAttributes
      
      
    	$list = @{}
    		foreach ($property in $properties){
    			if($_.$property){
    				$list.$property = $_.$property
    			}
    		}
    	
    	New-ADUser @list
    	
    	}
    }
    
    # Run the script
    importUsers
    
    #End of script


    My Ramblings @ http://alanwhitehouse.wordpress.com and My Tweets @ http://www.twitter.com/alanwhitehouse



    Monday, January 14, 2013 8:34 PM
  • You have both PasswordNeverExpires and ChangePasswordAtLogon set to true

       -ChangePasswordAtLogon bool
           Whether a password must be changed during the next logon attempt.
           Values for this parameter: $false or 0, $true or 1
           This cannot be set to $true for an account that also has PasswordNeverExpires set.

    Tuesday, January 15, 2013 7:06 AM
  • I get that.  I am guessing my syntax for converting the strings to booleans is incorrect.  I found that by googling.  Any suggestions?

    My Ramblings @ http://alanwhitehouse.wordpress.com and My Tweets @ http://www.twitter.com/alanwhitehouse

    Tuesday, January 15, 2013 1:40 PM
  • You're converting  all those values to true so change one to false or keep theses values in csv

    and make a check that if both values are set to true then choose only one

    • Edited by Blindrood Tuesday, January 15, 2013 2:08 PM
    Tuesday, January 15, 2013 2:07 PM
  • Forgive me if this is a duplicate post.  I posted but it seems to have disapeared.

    I see the error in my logic as far as the "True" with setting the boolean value.

    I need the value for True/False to come from the CSV file.  And again the different combinations of answers will vary with the individual import. However, in the CSV no matter if I have a 0/1 or a False/True, I am getting an error in Powershell.  It is telling me:

    ****

    New-ADUser : Cannot convert 'System.String' to the type 'System.Nullable`1[System.Boolean]' required by parameter
    'PasswordNeverExpires'.
    At C:\Users\administrator\desktop\create_ad_users\ImportUsers.ps1:68 char:13
    +     New-ADUser @list
    +                ~~~~~
        + CategoryInfo          : InvalidArgument: (:) [New-ADUser], ParameterBindingException
        + FullyQualifiedErrorId : CannotConvertArgument,Microsoft.ActiveDirectory.Management.Commands.NewADUser

    ****

    So I have tried some different logic to tell the system that these properties are boolean and not strings.  I have tried [boolean] $_.PasswordNeverExpires = [System.Convert]::ToBoolean($_.PasswordNeverExprires) as well as some variations and a couple other things I found on Google.  However the result is the same.

    How do I deal with this issue?


    My Ramblings @ http://alanwhitehouse.wordpress.com and My Tweets @ http://www.twitter.com/alanwhitehouse

    Tuesday, January 15, 2013 3:35 PM