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.

Parents
  • 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.

  • Everything seems to be working fine in terms of the attribute being updated if its blank and the code also generates a csv file which show the users who have been updated. The only problem I have is that I also get the following error in the console:

    Export-Csv : Cannot bind argument to parameter 'InputObject' because it is null.

    At line:12 char:5

    + } | Export-Csv -Path C:\Users\akhtao1\Desktop\test101.csv -NoTypeInformation

    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo : InvalidData: (:) [Export-Csv], ParameterBindingValidationException

    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ExportCsvCommand

     

    Export-Csv : Cannot bind argument to parameter 'InputObject' because it is null.

    At line:12 char:5

    + } | Export-Csv -Path C:\Users\akhtao1\Desktop\test101.csv -NoTypeInformation

    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo : InvalidData: (:) [Export-Csv], ParameterBindingValidationException

    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ExportCsvCommand

     

     

    I dont understand what this code is complaining about as I am getting the results that I want. Please help, thank you.

  • Ah sorry, assuming you are using PowerShell version 3 you could do this:

    Import-Csv -Path MyFile.csv | ForEach-Object {
    if (($User = [adsi]([adsisearcher]"(&(employeenumber=$($_.employeenumber))(!(homephone=*)))").FindOne().Path)) {
    $User.homephone = $_.Homephone
    $user.setinfo()
    New-Object -TypeName PSCustomObject -Property @{
    sn = $User.properties.sn -join ''
    givenname = $User.properties.givenName -join ''
    employeenumber = $User.properties.employeeNumber -join ''
    homephone = $User.properties.homePhone -join ''
    } | Export-Csv -Path Filename.csv -NoTypeInformation -Append
    }
    }
  • Hi Jaap

    You are the man!

    It all works perfect - yes I am using powershell 3. I wont need to go anywhere now for any other powershell query Smile

    thanks for all your help.

  • No problem, glad to be able to help.

  • Hi,

    I have tried this script, but that with an mail address instead of employeenumber. So all I had to do was replace that with "mail" . But I did get the error that the filter was't ok.
    Exception calling "FindOne" with "0" argument(s): "The (&(mail=)(!(homephone=*)) search filter is invalid

    Why?

    Peter

Reply
  • Hi,

    I have tried this script, but that with an mail address instead of employeenumber. So all I had to do was replace that with "mail" . But I did get the error that the filter was't ok.
    Exception calling "FindOne" with "0" argument(s): "The (&(mail=)(!(homephone=*)) search filter is invalid

    Why?

    Peter

Children
No Data