Introduction to CSVDE Import – Advanced features
In order to master CSVDE import, there is a natural progression:
- Start with CSVDE in EXPORT mode, just to learn the LDAP properties.
- Try a simple CSVDE import example with just 3 user properties.
- Import more advanced user properties, by adding more LDAP columns in the spreadsheet. This page is dedicated to these advanced LDAP features which you need for a bulk import.
Topics for CSVDE Import – Advanced Features
- Understanding LDAP Properties.
- Pure Excel Spreadsheet Techniques
- The CSVDE -i -k bulk import command
- Troubleshooting CSVDE Import
- Summary of CSVDE Import
- Active Directory Users and Computers – Properties sheets
- ADSI Edit – Domain container, user’s attributes (see diagram)
- CSVDE export mode – Open your spreadsheet and study row 1
Above all, you need a plan, a vision of the user you wish to script. In particular, study the LDAP field names that correspond to the user property sheets, for example, the LDAP givenName is the equivalent of First Name on the users General Properties sheet. By experimenting with Active Directory properties, ADSI attributes and CSVDE export, you will gather ideas for creating the perfect user!
Assumptions – You have mastered the basic LDAP fields, dn, sAMAccountName, and objectClass. You have successfully imported test users with CSVDE -i -f Newport.csv. If not, then have a refresher with my Simple Import example.
My challenge is for you to write down all the LDAP attributes that you wish to fill with data. Better still, copy the name from an export spreadsheet and then paste into the first row of the import spreadsheet. Here are just a few ideas to get you thinking: department, displayName, description, phone, email, physicalDeliveryOfficeName.
Make life easy for yourself. Take advantage of standard Excel functions and derive one field from another.
sAMAccountName – Build from givenName and SN
Consider the problem of the logon name, otherwise known as sAMAccountName. This is the name that users type in the Ctrl Alt Delete logon box, it can take a maximum of 11 characters. As a pencil and paper exercise, you could calculate this field from existing names. A common formula is to take the first 4 letters of the firstname and add them to the first 3 letters of the surname (or similar combinations). For Peter Graham, you could derive a logon name of PeteGra.
In Excel this Peter Graham example would translate to =left(c2,4) & left(d2,3). Once perfected, you can copy down this formula so that all new users have a calculated sAMAccountName. Note how the & (ampersand) joins the two elements. A plus sign would not work, what you need is concatenation or in plain English, a join with an ampersand.
Left(text,number) is a built-in Excel function. The first element refers to the text, I use a cell reference, and the second number is the number of characters that you want, three or four in my example.
DN (Distinguished Name)- Probably the most difficult LDAP field to create
Example of DN CN=Guy Thomas,OU=Newport,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 ",".