Link to home
Create AccountLog in
Avatar of Rammy Charles
Rammy CharlesFlag for United States of America

asked on

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.
Avatar of Rajitha Chimmani
Rajitha Chimmani
Flag of United States of America image

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

Avatar of Rammy Charles

ASKER

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
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}
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

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
Below is the name of the columns we have in the csv file.

User      Telephone number      Mobile number      IP Phone
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

Works great. Thanks.
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

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)?
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
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.
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
Please provide the complete error. the text that you posted does not include the reason for error
That is the full error message. It looks like it is failing because that user does not have all cells populated.
ASKER CERTIFIED SOLUTION
Avatar of Rajitha Chimmani
Rajitha Chimmani
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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.
Objection. Correct answers are
  http:#a40489886
  http:#a40490194