Forum Discussion
Need help importing user data from csv
I need to fill in the Manager Attribute for employee accounts using a CSV export from HR. I have the added bonus of the fact that SamAccountNames do not follow a consistent naming convention. So I need to use the EmployeeID attribute for finding accounts.
Here is an example of the CSV.
"LAST_NAME","FIRST_NAME","EmployeeID","SupervisorID"
"Doe","John","12345","13324"
"Jane","Laura","13456","3455"
"Mclane","John","12351","11331"
In this case, John Doe's Manager has an EmployeeID of 13324.
So I need to search AD for a User object with the EmployeeID attribute of 13324.
Then I need to pull the DistinguishedName attribute from that User Object, for example "CN=LSkywalker,OU=Department,DC=contoso,DC=com"
Then I'd have to copy that data into the Manager attribute of John Doe's account.
I've been trying to outline the script and so far this is what I have.
Variables
$User
$SupervisorID
$EmployeeID
$DistinguishedName
For each $User in CSV find $EmployeeID and $SupervisorID
Search AD for Supervisor Account where EmployeeID attribute = $SupervisorID
Pull $DistinguishedName Attribute value from Supervisor's account
Input $DistinguishedName value into original $EmployeeID's Manager Attribute
Proceed to next line of CSV and repeat
Hopefully I've explained the situation well and someone can help me flesh this out more, any help would be greatly appreciated, thank you.
Hey, Harm.
I'd strongly recommend using the -Filter parameter in the Get-AD* calls, as you're current pulling every single user in Active Directory client-side before filtering anything - twice.
In smaller environments, that won't be an issue, but it'll run into scalability issues rather quickly.
Using your original example, something like this would be more efficient in medium to large directories:
$csv = import-csv c:\scripts\accounts.csv foreach ($line in $csv) { $user = Get-ADUser -Filter "employeeID -eq '$($line.EmployeeID)'"; # Here, we're now leveraging server-side filtering. $manager = Get-ADUser -Filter "employeeID -eq '$($line.SupervisorID)'"; # And again here. if ($user -is [Microsoft.ActiveDirectory.Management.ADUser] -and $manager -is [Microsoft.ActiveDirectory.Management.ADUser]) { # We only want to be in here where we got precisely one user and one manager, otherwise we have no way to arbitrate who gets updated with what. Set-ADUser $user -Manager $manager } }Cheers,
Lain
Baron164 I've just tested this in my test DC and this should work:
$csv = import-csv c:\scripts\accounts.csv foreach ($line in $csv) { $user = Get-ADUser -Filter * -Properties EmployeeID | Where-Object EmployeeID -eq $line.EmployeeID $manager = Get-ADUser -Filter * -Properties EmployeeID | Where-Object EmployeeID -eq $line.SupervisorID Set-ADUser $user -Manager $manager }together with this CSV file
"LAST_NAME","FIRST_NAME","EmployeeID","SupervisorID" "User1","Test","1","3" "User2","Test","2","3"Which results into this (Test User2 as example having Test user 3 as manager now )
Hey, Harm.
I'd strongly recommend using the -Filter parameter in the Get-AD* calls, as you're current pulling every single user in Active Directory client-side before filtering anything - twice.
In smaller environments, that won't be an issue, but it'll run into scalability issues rather quickly.
Using your original example, something like this would be more efficient in medium to large directories:
$csv = import-csv c:\scripts\accounts.csv foreach ($line in $csv) { $user = Get-ADUser -Filter "employeeID -eq '$($line.EmployeeID)'"; # Here, we're now leveraging server-side filtering. $manager = Get-ADUser -Filter "employeeID -eq '$($line.SupervisorID)'"; # And again here. if ($user -is [Microsoft.ActiveDirectory.Management.ADUser] -and $manager -is [Microsoft.ActiveDirectory.Management.ADUser]) { # We only want to be in here where we got precisely one user and one manager, otherwise we have no way to arbitrate who gets updated with what. Set-ADUser $user -Manager $manager } }Cheers,
Lain
- I did think about that, but thought that I couldn't use the Filter command on that 🙂 But this is better for larger environments with more objects of course
Harm_Veenstra Thank you, that is very helpful. It's looks to be working, I see the manager information being filled in. The only issue I see is that the script spits out an error each time it runs the "Set-ADUser" line.
I tried changing the line to "Set-ADUser -Identity $user but that did not make a difference.Noting the error and cross-referencing it with the commandlet help (I don't work much with these commandlets in preference for either ADSI-native or Get-ADObject if I'm doing something ad hoc), you could try the following.
Set-ADUser (ActiveDirectory) | Microsoft Docs
The commandlet help is confusing me a bit because the type is [ADUser] - which is precisely what Harm has provided, yet the help on the -Identity and -Manager parameters speaks to things like objectGUID (my go-to attribute for almost everything), distinguished name, SID or sAMAccountName - clearly none of which are the full [ADUser] type.
I guess I could test to remove the ambiguity but I'm taking the lazy approach in the example below and just using the objectGUID from the $user and $manager objects.
Set-ADUser -Identity ($user.objectGUID) -Manager ($manager.objectGUID)Hopefully something here or from the replies above resolves your issue.
Cheers,
Lain
Edited to include the commandlet link, which I forgot first time around.
