PowerShell Script for Importing Data from CSV into Active Directory

We are looking for a script that will take data from a .csv file (username, Telephone Number, Mobile Number, IP Phone) and import it into a users AD profile accordingly.

Appreciate any assistants.
Rammy CharlesSales EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rajitha ChimmaniCommented:
Your input file should have the first column as username,telephone,mobile,ipphone.

This command will update the phone numbers for each user mentioned under username column. As you have not specified the AD properties I assumed the following and gave the command. You can update the properties accordingly as per your requirement.

foreach($user in import-csv filepath){Set-User $user.username -Phone $user.telephone -MobilePhone $user.mobile -OtherTelephone $user.IPphone}

Open in new window

0
Rammy CharlesSales EngineerAuthor Commented:
I am receiving the below error:

Set-User : The term 'Set-User' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was
included, verify that the path is correct and try again.
At line:1 char:61
+ foreach($user in import-csv C:\Temp\ADCiscoUCSTestLoad.csv){Set-User $user.usern ...
+                                                             ~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Set-User:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException
0
Rammy CharlesSales EngineerAuthor Commented:
Thanks. I tried that. This is what I have:

Import-Module ActiveDirectory
foreach($user in import-csv C:\Temp\ADCiscoUCSTestLoad.csv){Set-User $user.username -Phone $user.telephone -MobilePhone $user.mobile -OtherTelephone $user.IPphone}
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Rajitha ChimmaniCommented:
Try below command. The command that I gave earlier works on Exchange only.

Import-Module ActiveDirectory
foreach($user in import-csv filepath){
Set-ADUser $user.username -OfficePhone $user.telephone -MobilePhone $user.mobile
$IPPhone = (GET-ADUSER $user.username -PROPERTIES ipphone).ipphone
Set-ADUser $user.username -Add @{ipPhone=$ipphone}
}

Open in new window

0
Rammy CharlesSales EngineerAuthor Commented:
Still no go (see below).

Set-ADUser : Cannot validate argument on parameter 'Identity'. The argument is null. Provide a valid value for the argument, and then try running the command
again.
At line:3 char:12
+ Set-ADUser $user.username -OfficePhone $user.telephone -MobilePhone $user.mobile
+            ~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Set-ADUser], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.ActiveDirectory.Management.Commands.SetADUser
 
Get-ADUser : Cannot validate argument on parameter 'Identity'. The argument is null or an element of the argument collection contains a null value.
At line:4 char:24
+ $IPPhone = (GET-ADUSER $user.username -PROPERTIES ipphone).ipphone
+                        ~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Get-ADUser], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.ActiveDirectory.Management.Commands.GetADUser
 
Set-ADUser : Cannot validate argument on parameter 'Add'. The argument is null or an element of the argument collection contains a null value.
At line:5 char:32
+ Set-ADUser $user.username -Add @{ipPhone=$ipphone}
+                                ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Set-ADUser], ParameterBindingValidationException
0
Rammy CharlesSales EngineerAuthor Commented:
Below is the name of the columns we have in the csv file.

User      Telephone number      Mobile number      IP Phone
0
Rajitha ChimmaniCommented:
Remove space between the above headings as below and run the following script

User      Telephonenumber      Mobilenumber      IPPhone

Import-Module ActiveDirectory
foreach($user in import-csv filepath){
Set-ADUser $user.user -OfficePhone $user.telephonenumber -MobilePhone $user.mobilenumber
$Phone = GET-ADUSER $user.user -PROPERTIES ipphone
$Phone.ipphone = $user.ipphone
set-aduser -Instance $phone}
}

Open in new window

0
Rammy CharlesSales EngineerAuthor Commented:
Works great. Thanks.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
How should that work? Import-CSV expects comma as delimiter by default. Neither tab nor space are valid unless explicitely stated.

Setting the user can be simplified:
Import-Module ActiveDirectory
foreach ($user in import-csv filepath -Delimiter "`t"){
  Set-ADUser $user.user -Replace @{
    OfficePhone = $user.telephonenumber
    MobilePhone = $user.mobilenumber
    IPPhone = $user.ipphone
  }
}

Open in new window

0
Rammy CharlesSales EngineerAuthor Commented:
One more question, the script appears to fail if any of the cells are not populated. Is there any way to execute the script even if one or more of the fields is not present in the csv (i.e. ip phone for one or more users)?
0
Rammy CharlesSales EngineerAuthor Commented:
QLemo I received the below error with your script.

Set-ADUser : Cannot validate argument on parameter 'Replace'. The argument is null or an element of the argument collection contains a null value.
At line:3 char:34
+   Set-ADUser $user.user -Replace @{
+                                  ~~
    + CategoryInfo          : InvalidData: (:) [Set-ADUser], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.ActiveDirectory.Management.Commands.SetADUser
 
Set-ADUser : Cannot validate argument on parameter 'Replace'. The argument is null or an element of the argument collection contains a null value.
At line:3 char:34
+   Set-ADUser $user.user -Replace @{
+                                  ~~
    + CategoryInfo          : InvalidData: (:) [Set-ADUser], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.ActiveDirectory.Management.Commands.SetADUser
 
Set-ADUser : Cannot validate argument on parameter 'Replace'. The argument is null or an element of the argument collection contains a null value.
At line:3 char:34
+   Set-ADUser $user.user -Replace @{
+                                  ~~
    + CategoryInfo          : InvalidData: (:) [Set-ADUser], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.ActiveDirectory.Management.Commands.SetADUser
 
Set-ADUser : Cannot validate argument on parameter 'Replace'. The argument is null or an element of the argument collection contains a null value.
At line:3 char:34
+   Set-ADUser $user.user -Replace @{
+                                  ~~
    + CategoryInfo          : InvalidData: (:) [Set-ADUser], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.ActiveDirectory.Management.Commands.SetADUser
 
Set-ADUser : Cannot validate argument on parameter 'Replace'. The argument is null or an element of the argument collection contains a null value.
At line:3 char:34
+   Set-ADUser $user.user -Replace @{
+                                  ~~
    + CategoryInfo          : InvalidData: (:) [Set-ADUser], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.ActiveDirectory.Management.Commands.SetADUser
0
Rajitha ChimmaniCommented:
You may try this.

Import-Module ActiveDirectory
foreach($user in import-csv filepath){
Set-ADUser $user.user -OfficePhone $user.telephonenumber -MobilePhone $user.mobilenumber
if($user.ipphone -ne ""){$Phone = GET-ADUSER $user.user -PROPERTIES ipphone
$Phone.ipphone = $user.ipphone
set-aduser -Instance $phone}
}

Open in new window



@Qlemo - It worked because the default delimiter in csv is a comma.
0
Rammy CharlesSales EngineerAuthor Commented:
Thanks. Received the below error.

Set-ADUser : replace
At line:3 char:1
+ Set-ADUser $user.user -OfficePhone $user.telephonenumber -MobilePhone $user.mobi ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (BurgessV:ADUser) [Set-ADUser], ADInvalidOperationException
    + FullyQualifiedErrorId : ActiveDirectoryServer:0,Microsoft.ActiveDirectory.Management.Commands.SetADUser
0
Rajitha ChimmaniCommented:
Please provide the complete error. the text that you posted does not include the reason for error
0
Rammy CharlesSales EngineerAuthor Commented:
That is the full error message. It looks like it is failing because that user does not have all cells populated.
0
Rajitha ChimmaniCommented:
Ideally, the script should continue to execute even if the cell value is empty just that the ip phone for that user will not be updated. Please try this

Import-Module ActiveDirectory
foreach($user in import-csv filepath){
Set-ADUser $user.user -OfficePhone $user.telephonenumber -MobilePhone $user.mobilenumber
if($user.ipphone -eq ""){$Phone = GET-ADUSER $user.user -PROPERTIES ipphone
set-aduser -Instance $phone}
if($user.ipphone -ne ""){$Phone = GET-ADUSER $user.user -PROPERTIES ipphone
$Phone.ipphone = $user.ipphone
set-aduser -Instance $phone}
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Yes, empty properties are an issue. IMHO it is best to work with dynamic replace hashtables, only filled with data if something has been found in the CSV:
Import-Module ActiveDirectory
Import-Csv 'C:\Temp\EE\Users.CSV') | % {
  $replace = @{}
  if ($_.TelephoneNumber) { $replace.Add('telephoneNumber', $_.TelephoneNumber) }  <# -OfficePhone #>
  if ($_.MobileNumber   ) { $replace.Add('mobile'         , $_.MobileNumber   ) }  <# -MobilePhone #>
  if ($_.IPPhone        ) { $replace.Add('ipphone'        , $_.IPPhone        ) }
  Set-ADUser -identity $_.User -Replace @replace
}

Open in new window

This assumes, despite what was has been posted tells different, that the data is comma-separated.
0
Martin LissOlder than dirtCommented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Narvaezj's comment #a40489233

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Objection. Correct answers are
  http:#a40489886
  http:#a40490194
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.