Using a CSV list to create Active Directory contacts

I’ve recently been asked to convert a set of contacts exported from Outlook as a CSV file into Active Directory contacts. This was the first time I’ve had to do something like this and this exercise made me realize that Outlook contacts actually had much more fields available than Active Directory contacts so if anyone is about to embark on this task, note that there are going to be many fields that you would not be able to bring into Active Directory.

The way I approached this was to begin by determining what fields were available for Active Directory contacts. This can be easily determined by reviewing the tabs and fields of an Active Directory contact in Active Directory Users and Computers:

From here, the next step is to determine what fields in the Contact object maps to the ones that we will use to import via a CSV file. To do this, I executed the following csvde command to export existing contacts from AD to review the fields:

csvde -f exportContacts.csv -d OU=Contacts,DC=domain,DC=com

Opening up this CSV file will list the following available attributes:

• DN
• objectClass
• ou
• distinguishedName
• instanceType
• whenCreated
• whenChanged
• uSNCreated
• uSNChanged
• name
• objectGUID
• objectCategory
• dSCorePropagationData
• cn
• sn
• givenName
• displayName
• memberOf
• proxyAddresses
• targetAddress
• mailNickname
• internetEncoding
• countryCode
• legacyExchangeDN
• textEncodedORAddress
• mail
• msExchHideFromAddressLists
• msExchPoliciesIncluded
• msExchRecipientDisplayType
• msExchVersion
• showInAddressBook
• msExchUMDtmfMap
• altRecipientBL
• initials
• company
• mAPIRecipient
• msExchALObjectVersion
• telephoneNumber
• homeMTA
• extensionData
• homeMDB
• garbageCollPeriod
• mDBUseDefaults
• userAccountControl
• codePage
• pwdLastSet
• primaryGroupID
• objectSid
• accountExpires
• sAMAccountName
• sAMAccountType
• userPrincipalName
• ipPhone
• lastLogonTimestamp
• msExchHomeServerName
• msExchMailboxSecurityDescriptor
• msExchUserAccountControl
• msExchMailboxGuid
• msExchPoliciesExcluded
• msExchMDBRulesQuota
• msExchUserCulture
• msExchRecipientTypeDetails
• altRecipient
• badPwdCount
• badPasswordTime
• lastLogoff
• lastLogon
• logonCount
• lockoutTime
• deliverAndRedirect
• msDS-
• SupportedEncryptionTypes

Opening up a CSV file that was created from exporting Outlook contacts will display the following attributes:

• Title
• First Name
• Middle Name
• Last Name
• Suffix
• Company
• Department
• Job Title
• Business Street
• Business Street 2
• Business Street 3
• Business City
• Business State
• Business Postal Code
• Business Country/Region
• Home Street
• Home Street 2
• Home Street 3
• Home City
• Home State
• Home Postal Code
• Home Country/Region
• Other Street
• Other Street 2
• Other Street 3
• Other City
• Other State
• Other Postal Code
• Other Country/Region
• Assistant’s Phone
• Business Fax
• Business Phone
• Business Phone 2
• Callback
• Car Phone
• Company Main Phone
• Home Fax
• Home Phone
• Home Phone 2
• ISDN
• Mobile Phone
• Other Fax
• Other Phone
• Pager
• Primary Phone
• Radio Phone
• TTY/TDD Phone
• Telex
• Account
• Anniversary
• Assistant’s Name
• Billing Information
• Birthday
• Business Address
• PO Box
• Categories
• Children
• Directory Server
• E-mail Address
• E-mail Type
• E-mail Display Name
• E-mail 2 Address
• E-mail 2 Type
• E-mail 2 Display Name
• E-mail 3 Address
• E-mail 3 Type
• E-mail 3 Display Name
• Gender
• Government ID Number
• Hobby
• Home Address PO Box
• Initials
• Internet
• Free Busy
• Keywords
• Language
• Location
• Manager’s Name
• Mileage
• Notes
• Office Location
• Organizational ID Number
• Other Address PO Box
• Priority
• Private Profession
• Referred By
• Sensitivity
• Spouse
• User 1
• User 2
• User 3
• User 4
• Web Page

**Notice how much more attributes are available for an Outlook contact so if you are to convert these into AD contacts then you will need to determine where to put the additional information.

I’ve created the following table that maps what AD attribute maps to which CSV fields:

With the above information, simply create a spreadsheet with the AD attribute as the column headings, fill in the appropriate values for the contacts, save it as a CSV file then use the csvde command below to import and create the AD contacts:

csvde -I -f c:ContactsImport.csv