SolarWinds Free Tool For Importing CSV Files
Use this lovely utility for bulk importing your company’s joiners into Active Directory. List your users’ properties in Excel, then export to a .csv file; from there use SolarWinds free ‘Import user’ tool to create the accounts in Windows Active Directory.
Review of SolarWinds User Import CSVDE Tool
How to Bulk Import Users From .CSV File
Preparation is the key to a successful import. In particular, plan the attributes that you wish to import and list them in the top row of your spreadsheet / CSV file. Bulk importing users into ad with this free product is much easier than using the command-line CSVDE import.
Mapping Active Directory Attributes
Don’t worry if your list of fields is not a perfect match for the LDAP properties in active directory; one of the cleverest features of SolarWinds’ user import tool is the ability to map your idea of user fields, to those of Active Directory. See the ‘First Row Fields’ mapping below.
Once you have completed the mapping, just a few more clicks in the GUI, a quick check, and you the new users will be born into your Windows Active Directory domain.
Create Mailbox-enabled Users
If you have Exchange Server, then place the radio button next to ‘Create AD account and Exchange mailbox. Once again, SolarWinds' free bulk import tool is much easier than grappling with CSVDE in a DOS box.
In Addition to Your Import Users Utility
Two Bonus Active Directory Tools
SolarWinds provide the CSV import utility as part of a 3-in-one free download. The other two tools remove inactive accounts, one for users the other for computers. Just select the date, and the tool display all the old accounts.
The key is to check, and even export a list of these victims, that way at least you can review who was deleted.
In truth, I don’t have much use for these tools as I prefer to disable inactive accounts, or move them to a special OU. However, I am beginning to think I am a hoarder, and it could be time for a spring clean of very old accounts, not to mention those zillions of test accounts that I won’t ever need again.
How to Install SolarWinds User Import Tool
I found the hardest part of the install was remembering where I downloaded the zip file! Another point, as its name suggests install this CSV Import User utility on a domain controller.
The rest was easy, I extracted the two .msi files called ServiceInstaller and UserImportTool, and then clicked on the licence agreement, and let the install wizard perform the setup.
After the install completes the importer launches you are ready to begin a csv import into active directory. Should you need find the program later look on the start menu, or if all else fails navigate to:
C:\Program Files (x86)\SolarWinds\UserImportTool
Should you need to uninstall, then go to the Control Panel, Programs, scroll down to ‘U’ for User Import Tool. It’s a sign of a good CSV freeware program that it uninstalls easily and completely.
Recommendation: Click on the ‘Get Geeky’ tab and visit the Thwack forum.
LDAP Fields for Active Directory
Here are the commonest LDAP (Lightweight Directory Access Protocol) attributes which are handy when creating new users from CSVDE or this freeware tool:
- samAccountName – Must be unique
- CN – Common Name
- DN (Distinguished Name) The most important LDAP attribute. CN=Guy Thomas OU= Wales,DC=cp,DC=com
It’s worth spending the time to check how the LDAP attributes map to the Active Directory boxes.
One of my favourite research techniques is to add a value in the Active Directory box, then export using CSVDE, finally open the file in Excel and search for the value.
How to Build-up a DN (Distinguished Name)
Here is how to import AD users with a csv. My example is of a distinguished name is:
CN=Guy Thomas,OU=Wales,dc=cp,dc=com.
Not only is DN important as is a mandatory field, but also DN has to be unique. However, if you take it step-by-step it’s not as daunting as it first appears. Extend the sAMAccountName method but include not one, but three elements. Split the DN into CN (Common Name), OU (Organizational Unit) and dc (domain context).
1st Element CN=
Conceptually we need to script: Guy Thomas (or CN=Guy Thomas,)
Excel formula: ="CN="& c2 & " " & d2 &","
You could build the first element by joining all of the givenName with all of the SN. For example, =c2 & " " & d2 result =Guy Thomas. To be precise, you need to add cn= and a comma. For example, CN=Guy Thomas,. So the Excel formula would be ="cn=" & c2 & " " & d2 & ",". Often it’s the punctuation that catches you out, note how I included a space with a null string " ", also remember the comma at the end ",".
2nd Element OU=
If you remember, the second element is the OU. The simplest solution is to ‘hard code’ with, & "OU=Wales,". However if you have a production file where accounts will be in different OUs you could store the OU name in little used LDAP field such as extensionAttribute12. In which case the formula becomes & "OU=" & x2 & ",". Where x2 is the cell holding the OU name. The advantage is where you copy one row, the following rows will have the correct OUs. If you use the hard code method, all users will have the same OU.
3rd Element dc=
The final element has one or more dc=, elements. People often take their eye of the ball when the copy dc=, this partly because amateurs think dc= means domain controller, professionals know it means domain context. Therefore, do pay attention to the detail of your domain name. The key question does your domain have a .com or .net extension? Mistaking YourDom for YourDom.com would cause the import to fail.
As a matter of scripting technique, I store this value in one cell, say z2. then I script the absolute reference by adding dollar signs thus, & $x$2. In that cell z2 would be "dc=cp,dc=com". If you wished to keep is simple you could just add: & "dc=cp,dc=com".
Final formula: ="CN="& c2 & " " & d2 & "," & "OU=" & x2 & "," & $z$2
or another example:
="CN="& c2 & " " & d2 & "," & "OU=Wales,dc=cp,dc=com"
You have now reached a stage where you can either use CSVDE -import, or else use SolarWinds CSV importer to bulk import from csv into AD.
Example Using PowerShell Import-CSV
PowerShell has several built-in cmdlets which cater for CSV files. This free program helps to map CSV fields.
# PowerShell Import-CSV data from a spreadhseet
Clear-Host
$Spread = "D:\PShell\Serv4s.csv"
Import-CSV $Spread
Summary: Evaluation of CSVDE User Import Tool
SolarWinds provide a great tool to bulk import users into Active Directory. All you need for this CSV freeware is a spreadsheet with the fields in the first row. This tools saves time by using CSVDE to create computer or users accounts in AD.
As a bonus, in your download are two more free utilities, these will help delete old accounts or test users.
Additional Free and Trial SolarWinds Network Software
These are programs which I have enjoyed evaluating on my network. Some are completely free, while other downloads are trial versions of the full product. I think SolarWinds have a great strategy, namely, supplying a free gadget, which may be all a small company need, yet providing a big-brother suite of programs for larger organizations.
• Event Log Forwarder for Windows • Free NetFlow Analyzer • Review Kiwi Syslog Server
• SolarWinds Orion Netflow • Network Performance Monitor Video • CSVDE Import User Tool
• Free IP Address Tracker • SolarWinds Exchange Monitor • SolarWinds Kiwi CatTools • Home
