2
\$\begingroup\$

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
  }
}
\$\endgroup\$
2
\$\begingroup\$

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}
    }
}
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – user3931089 Sep 16 '14 at 20:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.