Bulk Import to AD from Excel/CSV File

[ellemeeks (Scriptkitty)]

Hi

I’m trying to bulk import users to AD from an Excel or CSV file. It will be to modify existing users for username, phone number, and job title. I was hoping to find something in powershell minus the AD module cause for whatever reason I can’t get it to work. Anybody have a working script I could reference from?

[jitensh]

first export existing stuff

Import-Module ActiveDirectory
get-aduser -filter * -searchbase "OU=OU,DC=DC,DC=com" -properties TelephoneNumber,MobilePhone,Title,OfficePhone,mobile |select -exp samaccountname,TelephoneNumber,MobilePhone,OfficePhone,mobile,Title |export-csv c:\users.csv -nti

modify the csv make changes

$USERS = Import-CSV c:\temp\users.csv

$USERS|Foreach{
Set-ADUSer -Identity $_.samaccountname -TelephoneNumber $_.TelephoneNumber -MobilePhone $_.MobilePhone -OfficePhone $_.officephone -mobile $_.Mobile -title $_.Title -Replace @{samaccountname $_.samAccoutname}}

this is my bulk ad creator script if any

https://community.spiceworks.com/scripts/show/3682-bulk-create-active-directory-users-powershell-with-logs-less-rows-in-csv

[Neally]

There are a bunch of scripts out there for that, give the search a try. If you decide to try something for yourself, feel free to post your code here ( of so please use the insert code button) and were happy to review and help you out of you’re having issues or errors

[psophos (M Boyle)]

It’s definitely worth figuring out whatever problem you are having with the AD module first and fixing that.

There are other ways of interacting with AD but the PS module is by far the easiest and more people on here have experience with it.

What version of windows and powershell are on your system and what OS version is the DC on?

[ellemeeks (Scriptkitty)]

Hi thanks for your response, I get this when I try to run the modify csv:

The hash literal was incomplete.
At line:5 char:1

Missing closing ‘}’ in statement block.
At line:5 char:1

Thanks Neally-I submitted this request after a good couple of hours of searching for what I needed.

This is what I had written up before and it was being annoying with the AD module thing:

PS C:\Users\emeeks.HAWAIICOUNTY> Import-Module ActiveDirectory
$inputFile = Import-CSV c:\test.csv
$log = “.\Error-Log.txt”
$date = Get-Date
Write-Host “Active Directory – Telephone Number Change Script”
Start-Sleep 2
Write-Host “The change process is now commencing, please wait…”
Start-Sleep 2
Function ChangeTelephoneNumber
{
“Change Process Started On: ” + $date + “) :” | Out-File $log -Append
“————————————————-” | Out-File $log -Append
foreach($line in $inputFile)
{
$sam = $line.SamAccountName
$officephone = $line.OfficePhone
Set-ADUser -Identity $sam -OfficePhone $officephone
Write-Host “Completed Telephone Number Change For: $sam”
“Completed Telephone Number Change For: $sam” | Out-File $log -Append
}
Start-Sleep 2
Write-Host “The process is now complete, please review the log file for any errors.”
}
ChangeTelephoneNumber
Active Directory – Telephone Number Change Script

I don’t know JACK about PS its all very new to me. With that being said-thanks again

[Neally]

How about we break it down, a lot and you try it like so:

Start-Transcript -Path ".\Error-Log.txt"
Import-Module ActiveDirectory
$inputFile = Import-CSV c:\test.csv

foreach($line in $inputFile){
    try{
        Set-ADUser -Identity $line.SamAccountName -OfficePhone $line.OfficePhone -ErrorAction Stop -Verbose
    }catch{
        Write-Warning "Error setting phone number for $($line.samaccountname)"
    }
}

Write-Output "The process is now complete, please review the log file for any errors."
Stop-Transcript

[ellemeeks (Scriptkitty)]

I agree, you’re right I should probably figure it out for the long it will make things easier I just don’t know how to do that so that’s where you guys come in, yay! I tried figuring it out but I’m honestly really lost lol. The version for the DC is 2012r2 and the PS version is v.1.0.

[ellemeeks (Scriptkitty)]

Hi Nealy,

I attempted to use that and I got this return:

Start-Transcript : This host does not support transcription.
At line:1 char:17
+ Start-Transcript <<<<  -Path ".\Error-Log.txt"
    + CategoryInfo          : NotImplemented: (:) [Start-Transcript], PSNotSupportedException
    + FullyQualifiedErrorId : NotSupported,Microsoft.PowerShell.Commands.StartTranscriptCommand
 
Import-Csv : Cannot process argument because the value of argument "name" is invalid. Change the value of the "name" argument and run the operation again.
At line:3 char:24
+ $inputFile = Import-CSV <<<<  c:\test.csv
    + CategoryInfo          : InvalidArgument: (:) [Import-Csv], PSArgumentException
    + FullyQualifiedErrorId : Argument,Microsoft.PowerShell.Commands.ImportCsvCommand
 
WARNING: Error setting phone number for 
The process is now complete, please review the log file for any errors.
Stop-Transcript : This host does not support transcription.
At line:14 char:16
+ Stop-Transcript <<<< 
    + CategoryInfo          : NotImplemented: (:) [Stop-Transcript], PSNotSupportedException
    + FullyQualifiedErrorId : NotSupported,Microsoft.PowerShell.Commands.StopTranscriptCommand

I imagine I have a lot of fluff in my original that I submitted >_<

[Neally]

@ellemeeks

Start-Transcript : This host does not support transcription.

What OS / PowerShell version are you using?

$psversiontable.psversion

@ellemeeks

Import-Csv : Cannot process argument because the value of argument “name” is invalid. Change the value of the “name” argument and run the operation again.
$inputFile = Import-CSV <<<< c:\test.csv

Well I guess the path for your import file is not “c:\test.csv”, you have to change that to the actual location of the csv file.

So:

1. Remove the transcript stuff in the first and last line
2. Update the path for the CSV file
3. try to run it again

Import-Module ActiveDirectory
#############################UPDATE THIS#####
foreach($line in (Import-CSV **c:\test.csv**)){
    Set-ADUser -Identity $line.SamAccountName -OfficePhone $line.OfficePhone -Verbose
}

[ellemeeks (Scriptkitty)]

Actually it is in there heh, I have it in C saved as test.csv.

is this what you meant by the version?

[Neally]

Ok, can you try again like so?

Import-Module ActiveDirectory
#############################UPDATE THIS#####
foreach($line in (Import-CSV c:\test.csv)){
    Set-ADUser -Identity $line.SamAccountName -OfficePhone $line.OfficePhone -Verbose
}

Make triple sure the path is right

[ellemeeks (Scriptkitty)]

Wohoo! Success! Wow all of that stuff I had written out and it only really came down to a couple of lines, lol. Thanks so much!

[Neally]

@ellemeeks

Wohoo! Success! Wow all of that stuff I had written out and it only really came down to a couple of lines, lol. Thanks so much!

Well it has no ‘logging’ now. since you know now that that basically works, now you can add more logging and such to it and go from there.

[ellemeeks (Scriptkitty)]

This is so much better, I don’t need the logs really now that I think about it-I’m just happy it works

thanks again

[jitensh]

Glad it solved i never this post was active unless neally spiced me. )