How to import data into the homePhone attribute within AD with a CSV file without overwriting the existing value.

Hoping you guys can help. I have been given a list of over 2000 users as a CSV file with 2 columns, "employeeNumber" and the corresponding "homePhone". Both only have numerical values. The management want me to update the users homePhone attribute within AD from the list provided. The catch is not to update the field if it already has a value. So, in essence I only want to update the AD accounts that have a blank attribute. I am hoping to use the import function within Powershell. Thank you.

  • Hello, yes this is possible. What you want to do is use an LDAP query to look for a user account matching the employeenumber that has an empty homephone attribute. This can be done using the following LDAP query:

    (&(employeenumber=$_.employeenumber)(!(homephone=*))

    Then if a match is found you can update the homephone property based on the csv file. The full code looks like this:

    Import-Csv -Path MyFile.csv | ForEach-Object {
    if (($User = [adsi]([adsisearcher]"(&(employeenumber=$_.employeenumber)(!(homephone=*))").FindOne().Path)) {
    $User.homephone = $_.Homephone
    $user.setinfo()
    }
    }

    Do note that there will be no prompts and no confirmations, so be careful before running this.

  • Hi Jaap

    Thank you for the quick reply.

    I have entered the query in powershell as it is except for the path to my csv file. I get the following error:

     

    Exception calling "FindOne" with "0" argument(s): "The
    (&([email protected]{employeeNumber=12345678;
    homePhone=38888}.employeenumber)(!(homephone=*)) search filter is invalid."
    At line:2 char:6
    + if (($User =
    [adsi]([adsisearcher]"(&(employeenumber=$_.employeenumber)(!(homeph ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentException

    I am not sure what I am doing wrong. I am using powershell version 3, if that makes a difference.

  • Right, I forgot to wrap that into a $() block. Here is the updated code:

    Import-Csv -Path MyFile.csv | ForEach-Object {
    if (($User = [adsi]([adsisearcher]"(&(employeenumber=$($_.employeenumber))(!(homephone=*))").FindOne().Path)) {
    $User.homephone = $_.Homephone
    $user.setinfo()
    }
    }
  • Hi Jaap

    Thank you for the reply.

    I am now getting a slightly different error. Please see below:

    Exception calling "FindOne" with "0" argument(s): "The (&(employeenumber=23897689)(!(homephone=*)) search filter is invalid."
    At line:2 char:60
    +     if (($User = [adsi]([adsisearcher]"(&(employeenumber=$($_.employeenumber))(! ...
    +                                                                                                ~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : ArgumentException

     

  • Yes, I think we can officially rename today to Typo Friday Wink. I also forgot to put in one of the brackets in the code I posted. So here is the correct search filter:

    Import-Csv -Path MyFile.csv | ForEach-Object {
    if (($User = [adsi]([adsisearcher]"(&(employeenumber=$($_.employeenumber))(!(homephone=*)))").FindOne().Path)) {
    $User.homephone = $_.Homephone
    $user.setinfo()
    }
    }