Whether you’re going from Open Directory to Active Directory or from Active Directory to Open Directory, chances are you’ll encounter csvde along the way. Csvde is installed on Windows Server and allows you to interface with Active Directory using csv files. cvsde can import files using the -i switch, followed by the -f switch to indicate the file that you are importing, followed by the path of the file. So if you save a file called toimport.csv to the root of your c drive temporarily you would use the following command to import the objects in the rows of the file:
csvde -i -f c:toimport.csv
Now, what’s that file need. At a minimum the file needs to indicate the objectClass for each user, the users sAMAccountName and the dn. So this file
can be used to import a user called johndoe. But how to build a csv file like this from Open Directory? There are a number of ways, but here’s one way I’ve found works pretty well for me. First, let’s use dscl to dump a list of the long and short user names:
dscl /LDAPv3/127.0.0.1 -list /Users cn > import.txt
Now from Excel, click on File, Import and then select to import from a Text file, clicking Import. Then, browse to and double-click on your file, which if you used the above command would be called import.txt. Then, when it asks you for the Original data type, choose Fixed width. This will dump two columns. One with the short name, another with the name.
Now, download and open this spreadsheet
I made for ya’ll. Paste the shortname column into the sAMAccountName column. Then paste the column with the full name into the D column, where John & Jane Doe are now. Then copy the user (objectClass) entry in column A to the number of rows you actually have (they will all be users) and then copy the CN= in column C to all of the rows you need. Then the , from column E and finally the OU/Search Base information for your Active Directory will need to replace that of mine. So if your Active Directory domain is called contoso.com (don’t laugh, I’ve seen it in production) and the ou you are going to use is Users then replace this text with OU=Users,DC=contoso,DC=com. Once you have all of the information filled in per row, notice that row G will automatically update. If you look at the formula, I’m just merging the contents of rows C-F. Copy the contents of rows 2 and 3 into the cells for column F until the end of your users.
Now you can take the information from column B and paste it into the toimport.csv and then take the information for row G and paste it into column C of the toimport.csv file (using Paste Special and pasting only the Value, NOT the formula). The objectClass will need to be filled in as user for each user as well (easily enough, this is user). Passwords aren’t to be imported, so using the 3 attributes from toimport.csv along with the command initially referenced earlier in this article give it a shot.
There are a number of other attributes that you will likely want to pull in and maybe augment as well. However, it’s late and I’ll have to talk about those later. In the meantime, do 1-2 users at a time until you feel confident to let csvde rip on all 10,000. I also strongly recommend bringing the initial import into a unique OU so that you can remove them all easily if things go wrong.
krypted August 19th, 2009
Posted In: Mac OS X Server, Windows Server
Active Directory, csv, csvde, Excel, import, LDAP, ldif, merge fields, Open Directory