0

What I would like to do is use a Powershell script to create Contacts within a certain folder in Active Directory from a .csv file. This script should allow me to create the first part of an email address based on the information entered in the .csv file.

We are running our exchange server on Windows Server 2003 still and I have Powershell 3.0 on my computer.

This is a script that is close to what I want but not exactly.

$csv = Import-Csv �C:\Contacts.csv� 
foreach($line in $csv) 
{ 
New-MailContact -Name $line.DisplayName -ExternalEmailAddress $line.EmailAddress -OrganizationalUnit �Contacts� -Alias $line.Alias 
}

I am not very experienced with Powershell and if anyone can help me out with this I would be very appreciative.

Edit:

As requested, Here is an example of one line in my .csv plus headers

Name,Email

TRUCK 327,9426645555

Community
  • 1
cyborgcommando0
  • 123
  • 1
  • 7

1 Answers1

2

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:user@domain.com) targetAddress = type:target address of the contact object (for example SMTP:user@domain.com) 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".

  1. Change Name to DisplayName
  2. Copy this DisplayName Column to a new column calledTempName`
  3. Change Email to prefix
  4. Create a new column called FirstName
  5. Create a new column called LastName
  6. Create a new column called Email

Getting FirstName and LastName column populated:

  1. Highlight all of the data in TempName and choose Data then Text to Columns and split out based on a space the first and last names

  2. Now copy the first and last names into their respective new columns you created in #4-5.

Getting Email column populated:

  1. In the email column go to the empty cell adjacent to the the first row of data. ie, there should be the 9426645555 in the prefix 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.

TheCleaner
  • 32,627
  • 26
  • 132
  • 191
  • Thank 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](http://technet.microsoft.com/en-us/library/bb124519(v=exchg.150).aspx)). Any workaround? – cyborgcommando0 Aug 28 '13 at 21:01
  • Grr...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. – TheCleaner Aug 28 '13 at 21:10
  • Thank 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. – cyborgcommando0 Aug 28 '13 at 21:24
  • Using 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. – TheCleaner Aug 28 '13 at 21:38
  • I 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! – cyborgcommando0 Aug 30 '13 at 14:03