Guy’s Scripting Ezine No 20 – CSVDE and ExchangeIt will be 28 years before another Feb 29th falls on a Sunday. I wonder what Windows 2032 will be like on Sunday February 29th 2032? Down to business. My speciality is getting people started. This week my mission is to help you create users with mailboxes. The technique relies on CSVDE importing user accounts from a spreadsheet into Active Directory. Assemble the toolkit.CSVDE is a built-in command found on Windows 2000 and later systems. Some say CSV stands for comma separated VARIABLE, whilst others will tell you that the V is for value, what matters is that CSV is a format that a whole host of programs can translate easily. The letters DE in CSVDE stand for Data Exchange and that sums up our task, to extract data from a spreadsheet and import it into Active Directory. Excel is the best tool to manipulate LDAP fields provide a holding pen for the data. The very first row of the import spreadsheet holds a full list of LDAP properties. Our job is to ‘see the woods for the trees’ and select the correct properties for our exchange mailboxes, whilst weeding out unnecessary properties. The key skill is cross-matching numerous LDAP fields with the user’s property sheet that you see in Active Directory. When it comes to importing data, VBScript is a possible alternative to CSVDE. If you need a quick straightforward import favour CSVDE, if you have a tricky job involving passwords then use VBScript. Incidentally, all bulk import methods rely on spreadsheets (More on VBScripts and mailboxes in a future ezine.) Calculating IP Address ranges is a black art, which many network managers solve by creating custom Excel spreadsheets. IPAT cracks this problem of allocating IP addresses in networks in two ways: For Mr Organized there is a nifty subnet calculator, you enter the network address and the subnet mask, then IPAT works out the usable addresses and their ranges. For Mr Lazy IPAT discovers and then displays the IP addresses of existing computers. Download the Free IP Address Tracker My assumption is that you have an Active Directory domain with Exchange 2000 / 2003 installed. While our goal is to import users, our first aim is to EXPORT a test user which you know has all the Exchange Mailbox tabs. What I have in mind is reverse engineering, we know the answer from the test user, the LDAP fields in the spreadsheet reveal how that user object was put together. I have divided the project into four phases, Export from Active Directory, clean up the LDAP columns in Excel, add more users and finally, import into Active Directory. Phase A – Exporting from Active Directory1) Navigate to Active Directory Users and Computers, create a test OU; trust me – it will be easier to filter the Active Directory objects if you have the exchange user in a test OU. 2) From your test OU, create a test user with a mailbox. It is essential that you have installed exchange 2000 / 2003 or else you will not see the mailbox properties. This test user will act as template that we will copy to create as many other users as you need. 3) Before we open Excel we need a .csv file. So, go to the command prompt and type: CSVDE – f exchange.csv – d "ou=Test,DC=YourDomain,DC=com" Learning Points Note 1: The command is: ou=Test not: DC=Test Note 2: -d is a filter, we do not want to be overloaded with objects that are of no interest to Exchange. Note 3: Most commands are case INsensitive so OU=TEST is fine. Note 4: Export is the default behaviour of CSVDE, so you do not need -e to extract the objects from Active Directory. However, you do need a filename and that is taken care of by the -f exchange.csv. Note the .csv extension. Phase B – Over to ExcelRemember the goal is to prepare a .csv file to import users and provide them with exchange mailboxes. 1) Open your exchange.csv file in excel. Now we are ready to interpret the LDAP properties which you see in the first row of the spreadsheet. 2) One of my aims is to simplify this .csv file. So, Column C = Ou, this will not be needed and can be deleted, as can Row 2. 3) Here are the 5 LDAP properties you need: a) Active Directory. DN, objectClass, cn, userAccountControl and sAMAccountName Note: DN and distinguishedName are really the same property, as are cn and Name. So I would keep DN and cn and delete the columns with distinguishedName and Name. b) 5 Exchange properties. homeMDB, mDBUseDefaults, mailNickname, mail and msExchHomeServerName. c) Active Directory Columns which are interesting but not essential. sn title description physicalDeliveryOfficeName telephoneNumber givenName displayName department company 4) These properties are not needed and may get in the way, so delete these columns. badPwdCount codePage countryCode badPasswordTime lastLogoff lastLogon pwdLastSet primaryGroupID accountExpires logonCount whenCreated whenChanged uSNCreated uSNChanged name objectGUID objectCategory gPLink dSCorePropagationData objectSid sAMAccountType groupType Phase C – Create your new users1) Assuming you have removed the OU row, your test user is now in Row 2. Copy your user in row 2, then paste into 3 / 4 new rows. In the production scenario you could drag and drop to copy this hundreds of times, but for now 3 / 4 new users will be more manageable when practicing. 2) Each user must have a unique DN or distinguishedName. Here is an example of how I edited row 3 to create a Barabara Shepherd user. Row 1 DN (Row 1 LDAP Heading) Row 2 CN=Guy Thomas,OU=Test,DC=cpexch,DC=com Row 3 CN=Barbara Shepherd,OU=Test,DC=cpexch,DC=com Two other LDAP fields that you need to alter are: sAMAccountName and mailNickname I edited both the above fields to say BarbaraSheph. For a large number of users, you could save time by using a formulae, for example in the mailNickname column you could say =p2 or what ever column letter represents sAMAccountName. Note 1: There is a limit of 20 characters for the sAMAccountName Note 2: You may like to check then alter values in what I call the ‘Optional Columns’. Phase D – Import then Verify1) When your spreadsheet is ready, go to the File menu and Save as, CSV (comma delimited). For example exchange2.csv 2) Go to the command prompt, navigate to the folder containing the CSV file. 3) Type CSVDE – i -f exchange2.csv 3a) An improvement would be adding the – k switch, which ignores errors like ‘Object already exists’. Type CSVDE – i -f exchange2.csv -k An important final step is to verify that the import has worked, switch to your OU in Active Directory Users and Computers, and rigorously check that you have the same number of tabs that the original user had. Check that the email address has been created, and check the home server is what you expected. Tip, in Windows 2000, select the View menu Advanced, now you should see an extra exchange tab. This really is a mission impossible. However if you have a test Exchange 2000 or 2003 machine, then you will almost certainly have Active Directory. In truth I have had no negative feedback, so either this is so hard no-one could do it, or else it works as I designed it – providing you have the time to test. Check out my ebook ‘How to use CSVDE commands’ here See Also | 
