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:

 

AD Attribute	Value
DN	CN=First name Initia. Last name,OU=Test,OU=Contacts,DC=domain,DC=com
objectClass	contact
ou
distinguishedName	CN=First name Initia. Last name,OU=Test,OU=Contacts,DC=domain,DC=com
instanceType	4
whenCreated	20160110232419.0Z
whenChanged	20160110232548.0Z
uSNCreated	53027203
uSNChanged	53027234
name	First name Initia. Last name
objectGUID	X'01709c730e93d24db092bac6744cda3c'
objectCategory	CN=Person,CN=Schema,CN=Configuration,DC=domain,DC=com
cn	First name Initia. Last name
sn	Last name
c	BM
l	City Address
st	State/provice Address
title	Job Title
description	Description
postalCode	Zip/Postal Code Address
postOfficeBox	P.O. Box Address
physicalDeliveryOfficeName	Office
telephoneNumber	Telephone Number General
facsimileTelephoneNumber	Fax
givenName	First name
initials	Initia
displayName	Display name
info	Notes
co	Bermuda
department	Department
company	Company
streetAddress	Street
wWWHomePage	Web Page
countryCode	60
ipPhone	IP phone
mail	E-mail General
homePhone	Home
mobile	Mobile
pager	Pager

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