Re: Import Data into AD from Excel

RSiddaway — Thu, 22 Mar 2012 20:28:00 GMT

I've provided an example for the Microsoft & Quest cmdlets, scripting and the AD provider as you didn't say what you were using

A CSV file was created

Import-Csv -Path "C:\Scripts\AD Examples\Users\bulkchangetest.csv" | Format-Table –AutoSize

userid JobTitle Cost Center HireDate
------ -------- ----------- --------
usera JobTitleA 1001001 1 January 2011
userb JobTitleB 1001002 1 February 2011
userc JobTitleC 1001003 1 March 2011
userd JobTitleD 1001004 1 April 2011

The task is to use the userids to add the data into extensionAttribute3, extensionAttribute4, and extensionAttribute5

Using the Microsoft cmdlets – read the csv file and for each object (row) use Set-ADuser to set the appropriate values.

The results can be displayed using Get-ADuser – note we need the Properties parameter

If required we can clear those attributes using the –Clear parameter on Set-ADUser

Import-Csv -Path "C:\Scripts\AD Examples\Users\bulkchangetest.csv" |            
foreach {            
  Set-ADUser -Identity $($_.userid) -Replace @{extensionAttribute3 = $($_.JobTitle); extensionAttribute4 = $($_."Cost Center"); `

   extensionAttribute5  = $($_.HireDate)}            
}            
            
Import-Csv -Path "C:\Scripts\AD Examples\Users\bulkchangetest.csv" |            
foreach {            
 Get-ADUser -Identity $($_.userid) -Properties * |

select samaccountname, extensionAttribute3, extensionAttribute4, extensionAttribute5            
}            
            
Import-Csv -Path "C:\Scripts\AD Examples\Users\bulkchangetest.csv" |            
foreach {            
 Set-ADUser -Identity $($_.userid) -Clear extensionAttribute3, extensionAttribute4, extensionAttribute5            
}

The Quest cmdlets are similar

Import-Csv -Path "C:\Scripts\AD Examples\Users\bulkchangetest.csv" |            
foreach {            
  Set-QADUser -Identity $($_.userid) -ObjectAttributes @{extensionAttribute3 = $($_.JobTitle); `

 extensionAttribute4 = $($_."Cost Center"); extensionAttribute5  = $($_.HireDate)}            
}            
            
Import-Csv -Path "C:\Scripts\AD Examples\Users\bulkchangetest.csv" |            
foreach {            
 Get-QADUser -Identity $($_.userid) -IncludeAllProperties |

select samaccountname, extensionAttribute3, extensionAttribute4, extensionAttribute5            
}            
            
Import-Csv -Path "C:\Scripts\AD Examples\Users\bulkchangetest.csv" |            
foreach {            
 Set-QADUser -Identity $($_.userid) -ObjectAttributes @{extensionAttribute3 = ""; extensionAttribute4 = ""; extensionAttribute5  = ""}            
}

Use -ObjectAttributes instead of –Replace. We don’t have a –Clear parameter so need to set the values to empty

Scripting involves a bit more work

Import-Csv -Path "C:\Scripts\AD Examples\Users\bulkchangetest.csv" |            
foreach {            
 $data = $_            
 $root = [ADSI]""            
 $search = [adsisearcher]$root            
 $search.Filter = "(&(objectclass=user)(objectcategory=user)(samaccountname=$($data.userid)))"             
 $search.SizeLimit = 3000            
 $search.FindOne() | foreach {            
             
 $user = $_.GetDirectoryEntry()            
 $user.extensionAttribute3 = $($data.JobTitle)            
 $user.extensionAttribute4 = $($data."Cost Center")            
 $user.extensionAttribute5 = $($data.HireDate)            
 $user.SetInfo()            
}            
              
}            
            
Import-Csv -Path "C:\Scripts\AD Examples\Users\bulkchangetest.csv" |            
foreach {            
 $data = $_            
 $root = [ADSI]""            
 $search = [adsisearcher]$root            
 $search.Filter = "(&(objectclass=user)(objectcategory=user)(samaccountname=$($data.userid)))"             
 $search.SizeLimit = 3000            
 $search.FindOne() | foreach {            
  $user = $_.GetDirectoryEntry()            
  $user | select  @{N="samaccountname"; E={$_.samaccountname}},            
  @{N="extensionAttribute3"; E={$_.extensionAttribute3}},            
  @{N="extensionAttribute4"; E={$_.extensionAttribute4}},            
  @{N="extensionAttribute5"; E={$_.extensionAttribute5}}            
 }            
}            
            
            
Import-Csv -Path "C:\Scripts\AD Examples\Users\bulkchangetest.csv" |            
foreach {            
 $data = $_            
 $root = [ADSI]""            
 $search = [adsisearcher]$root            
 $search.Filter = "(&(objectclass=user)(objectcategory=user)(samaccountname=$($data.userid)))"             
 $search.SizeLimit = 3000            
 $search.FindOne() | foreach {            
             
 $user = $_.GetDirectoryEntry()            
 $user.extensionAttribute3 = " "            
 $user.extensionAttribute4 = " "            
 $user.extensionAttribute5 = " "            
 $user.SetInfo()            
}            
}

Use [adsisearcher] to find the acccount – get a directory entry and set the values. The display is similar except we select the attributes. Clearing the attributes involves setting it to a blank string

Finally the provider

Import-Csv -Path "C:\Scripts\AD Examples\Users\bulkchangetest.csv" |            
foreach {            
 $data = $_            
 Get-ChildItem -Filter "(&(objectclass=user)(objectcategory=user)(samaccountname=$($data.userid)))"  `
 -Path Ad:\"DC=Manticore,DC=org" -Recurse |             
 foreach {             
 Set-ItemProperty -Path Ad:\"$($_.DistinguishedName)" -Name extensionAttribute3 -Value $($data.JobTitle)            
 Set-ItemProperty -Path Ad:\"$($_.DistinguishedName)" -Name extensionAttribute4 -Value $($data."Cost Center")            
 Set-ItemProperty -Path Ad:\"$($_.DistinguishedName)" -Name extensionAttribute5 -Value $($data.HireDate)            
}             
}            
            
            
Import-Csv -Path "C:\Scripts\AD Examples\Users\bulkchangetest.csv" |            
foreach {            
 $data = $_            
 Get-ChildItem -Filter "(&(objectclass=user)(objectcategory=user)(samaccountname=$($data.userid)))"  `
 -Path Ad:\"DC=Manticore,DC=org" -Recurse |             
 foreach {             
  New-Object -TypeName PSObject -Property @{            
  samaccountname = Get-ItemProperty -Path Ad:\"$($_.DistinguishedName)" -Name samaccountname |

  select -ExpandProperty samaccountname            
  extensionAttribute3 = Get-ItemProperty -Path Ad:\"$($_.DistinguishedName)" -Name extensionAttribute3 |

  select -ExpandProperty extensionAttribute3            
  extensionAttribute4 = Get-ItemProperty -Path Ad:\"$($_.DistinguishedName)" -Name extensionAttribute4 |

    select -ExpandProperty extensionAttribute4            
  extensionAttribute5 = Get-ItemProperty -Path Ad:\"$($_.DistinguishedName)" -Name extensionAttribute5 |

  select -ExpandProperty extensionAttribute5            
 }            
}             
}              
            
Import-Csv -Path "C:\Scripts\AD Examples\Users\bulkchangetest.csv" |            
foreach {            
 $data = $_            
 Get-ChildItem -Filter "(&(objectclass=user)(objectcategory=user)(samaccountname=$($data.userid)))"  `
 -Path Ad:\"DC=Manticore,DC=org" -Recurse |             
 foreach {             
 Set-ItemProperty -Path Ad:\"$($_.DistinguishedName)" -Name extensionAttribute3 -Value " "            
 Set-ItemProperty -Path Ad:\"$($_.DistinguishedName)" -Name extensionAttribute4 -Value " "            
 Set-ItemProperty -Path Ad:\"$($_.DistinguishedName)" -Name extensionAttribute5 -Value " "            
}             
}

We can search for the user with an LDAP filter as we did with the script – then use Set-ItemProperty to set the values. Displaying the values is the same except we use Get-Itemproperty and the final clear is the same as the original set except we set the values to a blank string

Re: Import Data into AD from Excel

jmhollid — Wed, 21 Mar 2012 20:49:19 GMT

Import them into extensionAttribute3, extensionAttribute4, extensionAttribute5

Re: Import Data into AD from Excel

jmhollid — Wed, 21 Mar 2012 20:48:30 GMT

JobTitle, Cost Center, and HireDate

Re: Import Data into AD from Excel

RSiddaway — Wed, 21 Mar 2012 20:45:07 GMT

Can you give the three columns you want to import.

When you say it has the userID do you mean the samaccountname (login id) and what do you call that column in the spreadsheet?

Import Data into AD from Excel

jmhollid — Wed, 21 Mar 2012 20:30:51 GMT

I have an excel spreadsheet with data that I need to import into Active Directory. It contains the user ID. I was wondering if anyone had a powershell script to the information for each user ID and import it into AD. I only need 3 columns from the spreadsheet.

Any help would be greatly appreciated.

PowerShell for Active Directory

Re: Import Data into AD from Excel

Re: Import Data into AD from Excel

Re: Import Data into AD from Excel

Re: Import Data into AD from Excel

Import Data into AD from Excel