Using Powershell to Create Exchange Contacts from a .CSV file
EDIT: grr...I forgot Exchange 2003...the below only works on Exchange 2007 and above.
For Exchange 2003 you need to use csvde: http://support.microsoft.com/kb/327620
An example of how to format the csv file and the csvde command can be found here: http://forums.msexchange.org/m_1800509533/printable.htm
QUOTE:
objectClass,DN,displayName,proxyAddresses,targetAddress,mail,mailNickname,givenName,sn,showInAddressBook
objectClass = Class of the object which you are creating (In your case "contact") DN = Distinguish Name of the object displayName = Display Name of the object proxyAddresses = type:proxy address of the contact object (for example SMTP:[email protected]) targetAddress = type:target address of the contact object (for example SMTP:[email protected]) mail = Mail (which you can see in general tab of contact object) mailNickname = alias name of the contact object givenName = First Name sn = Last Name showInAddressBook = for enabling contact in GAL (Even if we did not specify this value the contact will display in the default GAL)
If you want i'll send you a sample CSV file.
You may have to learn Excel tips and tricks to create the CSV file from the input details.
Once you create the CSV file you can use the following command to import the contact objects.
CSVDE -i -f For Ex : CSVDE -i -f c:\test.csv
-i = for specifying import mode
-f = For specifying the input file
Another alternative is a 3rd party utility like AD Bulk Contacts: http://www.dovestones.com/active-directory-contact-import/
OK, here's what I would do (if this were Exchange 2007+ lol)
Setup of csv file
First, open your CSV file in Excel. You need to make your CSV file better for input into PS "script".
- Change
Name
toDisplayName
- Copy this
DisplayName Column to a new column called
TempName` - Change
Email
toprefix
- Create a new column called
FirstName
- Create a new column called
LastName
- Create a new column called
Email
Getting FirstName
and LastName
column populated:
Highlight all of the data in
TempName
and chooseData
thenText to Columns
and split out based on a space the first and last namesNow copy the first and last names into their respective new columns you created in #4-5.
Getting Email
column populated:
- In the email column go to the empty cell adjacent to the the first row of data. ie, there should be the
9426645555
in theprefix
column on the same row to the left somewhere, let's say that data is in B3 for example. Here you'll create a formula to concatenate the email prefix with the domain. Something like=CONCATENATE(A2,"@domain.com")
then copy and paste that down the column to get all the email addresses correct.
Now at this point you should have rows that have a DisplayName
, FirstName
, LastName
, and Email
that has valid info for a single user. Don't worry about the extraneous columns...we won't use them in the input.
Save the file now as a CSV file and then verify the CSV file looks accurate for input
On to the Powershell script:
$csv = Import-Csv C:\Contacts.csv
foreach($line in $csv)
{
New-MailContact -Name $line.DisplayName -FirstName $line.FirstName -LastName $line.LastName -ExternalEmailAddress $line.Email -OrganizationalUnit Contacts -Alias $line.Alias
}
That should at least point you in the direction to get you there. If you get stuck, let me know.
Related videos on Youtube
Paolo Amato
Updated on September 18, 2022Comments
-
Paolo Amato over 1 year
I'm writing a script in bash and i would like to know if there is an alternative way to write these sed commands (without using sed):
sed '1,11d;$d' "${SOTTOCARTELLA}"/file sed '1,11!d' "${SOTTOCARTELLA}"/file sed '1,11d' -i "${SOTTOCARTELLA}"/file1
-
kralyk over 10 yearsExchange 2007??
-
cyborgcommando0 over 10 yearsExchange 2003 :( For a few more months at least.
-
kralyk over 10 yearsWhat are you wanting the script to do that it doesn't currently do? At first glance it looks ok...but not sure what you are wanting it to do that it isn't.
-
cyborgcommando0 over 10 yearsThe data I have for the Display name should be broken up into First and Last instead of the Display name and the data I have for emails will only include the email address before the domain @thisisthedomain.com but will be the same domain email address for each one.
-
kralyk over 10 yearsPost in your OP an example line from your csv.
-
Cyrus over 5 yearsWelcome to SO. Stack Overflow is a question and answer page for professional and enthusiastic programmers. Add your own code to your question. You are expected to show at least the amount of research you have put into solving this question yourself.
-
l0b0 over 5 yearsWhat are you trying to achieve that
sed
isn't doing for you? If I read those commands correctly you can easily achieve the same withhead
andtail
, but I don't know why you wouldn't use a working solution. -
Paolo Amato over 5 yearsI know that this works, i am just curios about if there is an equivalent that can produce the same result as these sed commands i posted here. How could it be done with head and tail? thanks!
-
Stephen C over 5 yearsIf you are using
head
andtail
, that's notbash
!
-
-
cyborgcommando0 over 10 yearsThank your for the thorough assistance! Although it appears I have run into a snag. I setup Powershell 2.0 on our Windows Server/Exchange 2003 machine and it seems that the
New-MailContact
cmdlet is only good for Windows Server 2013 and Exchange Online (Source). Any workaround? -
kralyk over 10 yearsGrr...I keep forgetting the Exchange 2003 part. This works on Exch2007 or greater. For 2003 you'll need to use csvde. I'll update my answer.
-
cyborgcommando0 over 10 yearsThank you for all your help. I may just wait until we upgrade to a newer version of exchange lol. If I have some more time I will attempt this though and let you know.
-
kralyk over 10 yearsUsing a 3rd party tool like AD Bulk Contacts will be quick and easy...you'll just need to purchase it, but it's fairly cheap.
-
cyborgcommando0 over 10 yearsI think our short term solution is going to be Ad Bulk Contacts and when we upgrade that server later this year I will use powershell for any other contacts. Thanks TheCleaner!