AD update from CSV

Question

I get a CSV dump of all employee records from our payroll system once a week. The CSV has something like 28K records. The key between the two systems is the employeeID.

I import this in to PS and run a query to look up every employee in AD based on their employeeID and pull down the DN for that user. I store this as $Mgr to be used later.

Now we get to the meat of the script. The problem I'm running in to, is that I can't count on any field not having some null values. By NULL, I don't mean $null, or '' or "", but " ".

Anyhow, my way to combat this was to write a line for every value that I'm trying to pull in and set the IF on that.

I'm sure there are some ways to optimize this, but I'm not seeing them clearly now.

CSV Example

employeeID,givenName,initials,middleName,sn,name,title,physicalDeliveryOfficeName,streetAddress,l,st,co,countryCode,c,postalCode,department,manager,description,Company
"11111","John"," "," ","Smith","Smith, John","Mechanic","Aurora, IL","123 Some St","Aurora","IL","United States","840","US","60505","DeptID","111112"," ","Company Name"

Ps1 Example

Import-Module ActiveDirectory
$Users=Import-Csv C:\Job_titles.csv
foreach($u in $Users) {
 try {
$Usr = Get-ADUser -Filter "employeeID -eq '$($u.employeeID)'"
$Mgr = Get-ADUser -Filter "employeeID -eq '$($u.manager)'"
Rename-ADObject –Identity $Usr -NewName $u.name
if ($u.description -eq ' ') {Set-ADUser –Identity $Usr -clear description} else {Set-ADUser –Identity $Usr -replace @{description=$u.description}}
if ($u.givenName -eq ' ') {Set-ADUser –Identity $Usr -clear givenName} else {Set-ADUser –Identity $Usr -replace @{givenName=$u.givenName}}
if ($u.initials -eq ' ') {Set-ADUser –Identity $Usr -clear initials} else {Set-ADUser –Identity $Usr -replace @{initials=$u.initials}}
if ($u.middleName -eq ' ') {Set-ADUser –Identity $Usr -clear middleName} else {Set-ADUser –Identity $Usr -replace @{middleName=$u.middleName}}
if ($u.title -eq ' ') {Set-ADUser –Identity $Usr -clear title} else {Set-ADUser –Identity $Usr -replace @{title=$u.title;extensionAttribute1=$u.title}}
if ($u.sn -eq ' ') {Set-ADUser –Identity $Usr -clear sn} else {Set-ADUser –Identity $Usr -replace @{sn=$u.sn}}
if ($u.physicalDeliveryOfficeName -eq ' ') {Set-ADUser –Identity $Usr -clear physicalDeliveryOfficeName} else {Set-ADUser –Identity $Usr -replace @{physicalDeliveryOfficeName=$u.physicalDeliveryOfficeName}}
if ($u.streetAddress -eq ' ') {Set-ADUser –Identity $Usr -clear streetAddress} else {Set-ADUser –Identity $Usr -replace @{streetAddress=$u.streetAddress}}
if ($u.l -eq ' ') {Set-ADUser –Identity $Usr -clear l} else {Set-ADUser –Identity $Usr -replace @{l=$u.l}}
if ($u.st -eq ' ') {Set-ADUser –Identity $Usr -clear st} else {Set-ADUser –Identity $Usr -replace @{st=$u.st}}
if ($u.co -eq ' ') {Set-ADUser –Identity $Usr -clear co} else {Set-ADUser –Identity $Usr -replace @{co=$u.co}}
if ($u.postalCode -eq ' ') {Set-ADUser –Identity $Usr -clear postalCode} else {Set-ADUser –Identity $Usr -replace @{postalCode=$u.postalCode}}
if ($u.department -eq ' ') {Set-ADUser –Identity $Usr -clear department} else {Set-ADUser –Identity $Usr -replace @{department=$u.department}}
if ($u.company -eq ' ') {Set-ADUser –Identity $Usr -clear company} else {Set-ADUser –Identity $Usr -replace @{company=$u.company}}
if ($u.countryCode -eq ' ') {Set-ADUser –Identity $Usr -clear countryCode} else {Set-ADUser –Identity $Usr -replace @{countryCode=$u.countryCode}}
if ($u.c -eq ' ') {Set-ADUser –Identity $Usr -clear c} else {Set-ADUser –Identity $Usr -replace @{c=$u.c}}
if ($u.manager -eq ' ') {Set-ADUser –Identity $Usr -clear manager} else {Set-ADUser –Identity $Usr -replace @{manager="$Mgr"}}
 } catch {
 "'$($u.employeeID)' - '$($u.name)'" | Out-File 'C:\update_errors.txt' -Append
 }
}

Answer 1

I can't test this because I don't know anything about AD, but hopefully this will be of help.

When you say you want to optimize your code, I'm assuming you mean you want to simplify the code.

You can replace your block of if statements with a loop.

First, make list of the properties that you want to look at:

$propNames = 
 'description','givenName','initials','middleName','title','sn',
 'physicalDeliveryOfficeName','streetAddress','l','st','co',
 'postalCode','department','company','countryCode','c','manager'

You could put that somewhere near the top of the script.

Now replace the if statements with a loop that loops over the property names. It could look something like this:

foreach ($propName in $propNames)
{ 
 $uVal = Select-Object -InputObject $u -ExpandProperty $propName
 if ($uVal -eq " ")
 {
 Set-ADUser –Identity $Usr -clear $propName
 }
 else
 {
 Set-ADUser –Identity $Usr -replace @{$propName = $uVal}
 }
}

• user3931089: Thanks. I'm not too concerned with cleaning up the code at this point. My reason for posting here was to optimize and speed up the process. Right now it's taking a VERY long time to scan through the 30K records in the CSV to update the 3K records in AD.I have posted my latest version of the code in my original comment.