Import bulk contacts from CSV into Exchange 2007 using PS1

Hello,

below there is a script, but it doesn't work if you add or change the header and I want to use this header:
company,displayName,sn,givenName,initials,mailNickName,mail,department,physicalDeliveryOfficeName,title,telephoneNumber,Mobile,facsimileTelephoneNumber,pager,streetAddress,postalCode,l,co,c,proxyAddresses,targetAddress

in stead of:
Last,First,Email,Company,Department,Title,Location,Tel,Fax,Mobile,POBox,Zip,StreetAdd,City,Country

Is there a way to modify the script? And also if there is a blank space(no information) in the csv file data ,how to deal with this?

kind regards,

Jef
Header: 
-------
Last,First,Email,Company,Department,Title,Location,Tel,Fax,Mobile,POBox,Zip,StreetAdd,City,Country
 
code:
-----
$OU = 'Contacts'
# Call CSV File & Create Mailboxes
Write-host "Creating Contacts..."
import-csv 'contacts.csv' |
 foreach{
  $entry = $_
  $DN = $_.First+" " +$_.Last;
  $Alias = $_.First+"_" +$_.Last
  New-MailContact $DN -DisplayName $DN -FirstName $_.First -LastName $_.Last -organizationalunit $OU -Alias $Alias -ExternalEmailAddress $_.email
  Set-Contact $DN -Company $_.Company -Title $_.Title -Department $_.Department -Fax $_.Fax -MobilePhone $_.Mobile -Office $_.Location -Phone $_.Tel -PostalCode $_.Zip -PostOfficeBox $_.POBox -City $_.City -StreetAddress $_.StreetAdd -CountryorRegion $_.Country
  Write-Host 'OU   =' $OU
  Write-host 'First Name  =' $_.First
  Write-Host 'Last Name  =' $_.Last
  write-host 'Display Name  =' $DN
  Write-Host 'Alias   =' $Alias
  Write-Host 'Email Address  =' $_.Email
  Write-Host 'Company  =' $_.Company
  Write-Host 'Office  =' $_.Location
  Write-Host 'Title  =' $_.Title
  Write-Host 'Department =' $_.Department
  Write-Host 'Telephone =' $_.Tel
  Write-Host 'Fax  =' $_.Fax
  Write-Host 'Mobile No.  =' $_.Mobile
  
  }

Open in new window

jefmelkenbeekAsked:
Who is Participating?
 
Chris DentConnect With a Mentor PowerShell DeveloperCommented:

Hey Jef,

I'm sorry for the late reply to this one.

This incorporates the search above. I've included the current replacements in that as well to tidy up a bit.

Chris
$OU = 'GAB_Contacts'
 
# RegEx setup - build two alternation regex patterns with the unwanted chars:
$fChars1 = ':', '?', '/', '\', '*', '<', '>'
$fChars2 = '|', '"', ' ', '.'
$pat1 = [string]::join('|', ($fChars1 | % {[regex]::escape($_)}))
$pat2 = [string]::join('|', ($fChars2 | % {[regex]::escape($_)}))
 
# Call CSV File & Create Mailboxes
 
Write-host "Creating Contacts..."
import-csv 'test.txt' |
  foreach{
 
    $First =($_.First).Trim;
    $Last = ($_.Last).Trim;
 
    # replace the unwanted char with a ' ' or nothing
    $First = $First -replace $pat1, ' ' -replace $pat2
    $Last = $Last -replace $pat1, ' ' -replace $pat2
 
    $DN = "$First $Last";
 
    If ($First -eq "" -or $Last -eq "") { None }
 
    $Alias1 = ($a+"." +$b).replace(")", "")
    $Alias = $Alias1.replace("(", "")
    If ($Last -eq "") { None } 
  
    New-MailContact $DN -DisplayName $DN -FirstName $First -LastName $Last -organizationalunit $OU -Alias ($Alias -replace ('_|\s')) -ExternalEmailAddress $_.email
  
    #will fill automatically the attributes in when it finds the information in AD. /Uncomment the line below if you need auto fill in. 
    Set-Contact $DN -Company $_.Company -Title $_.Title -Department $_.Department -Fax $_.Fax -MobilePhone $_.Mobile -Office $_.Location -Phone $_.Tel -PostalCode $_.Zip -PostOfficeBox $_.POBox -City $_.City -StreetAddress $_.StreetAdd #-CountryorRegion $_.Country
  }

Open in new window

0
 
jefmelkenbeekAuthor Commented:
This is a another script found on web and better then first one.
How I can modify this script to use my header?

$OU = 'Contacts'
 
# Call CSV File & Create Mailboxes
 
Write-host "Creating Contacts..."
 
import-csv 'contacts.csv' |
 foreach{
 
$entry = $_
$DN = $_.First+" " +$_.Last;
$a = $_.First.Replace(" ", "")
$b = $_.Last.Replace(" ", "")
$Alias = $a+"_" +$b
  
  New-MailContact $DN -DisplayName $DN -FirstName $_.First -LastName $_.Last -organizationalunit $OU -Alias $Alias -ExternalEmailAddress $_.email
  Set-Contact $DN -Company $_.Company -Title $_.Title -Department $_.Department -Fax $_.Fax -MobilePhone $_.Mobile -Office $_.Location -Phone $_.Tel -PostalCode $_.Zip -PostOfficeBox $_.POBox -City $_.City -StreetAddress $_.StreetAdd #-CountryorRegion $_.Country
 
  }

Open in new window

0
 
Chris DentPowerShell DeveloperCommented:

Hey,

It's just the references to the each of the fields.

e.g.

$_.First

That presents the First column from the CSV. In your case it would change to:

$_.givenName

You won't be able to load everything on your CSV in that one command. Half the fields cannot be added using New-MailContact, that means you would need a two stage process, one to add the contact, a second to populate the remaining fields.

Chris
0
Improved Protection from Phishing Attacks

WatchGuard DNSWatch reduces malware infections by detecting and blocking malicious DNS requests, improving your ability to protect employees from phishing attacks. Learn more about our newest service included in Total Security Suite today!

 
jefmelkenbeekAuthor Commented:
Hello Chris,

thanks for your response:).I changed this references, but it doesn't work.error: the value of  argument "name" is invalid.
Which attributes cannot be imported in the first import? Can you help me this code please?
regards,

 
0
 
Chris DentPowerShell DeveloperCommented:

You can see all the options with:

Get-Help New-MailContact -Full | More

Which is at least worth knowing about :)

Still, I managed to miss that it used the Set-Contact cmdlet which does have a great deal more available.

Import 'Contacts.csv' | `
  ForEach{
    New-MailContact $_.displayName `
      -DisplayName $_.displayName `
      -FirstName $_.givenName `
      -LastName $_.sn `
      -organizationalunit $OU `
      -Alias $_.mailNickName `
      -ExternalEmailAddress $_.targetAddress

    Set-Contact $_.displayName `
      -Company $_.company `
      -Title $_.title `
      -Department $_.department `
      -Fax $_.facsimileTelephoneNumber `
      -MobilePhone $_.mobile `
      -Office $_.physicalDeliveryOfficeName `
      -Phone $_.telephoneNumber `
      -PostalCode $_.postalCode `
      -City $_.l `
      -StreetAddress $_.streetAddress `
      -CountryorRegion $_.co
 }
0
 
jefmelkenbeekAuthor Commented:
the script is working now thanks :), but it stops every time when there are several spaces between 2 comma's
exp: test,    ,test(-->5 comma's) or test, ,test(-->1 comma).
How can i remove this unnecessary spaces?
0
 
Chris DentPowerShell DeveloperCommented:

Hmm that does make it rather more complicated. There's no way to clean that up prior to import? :)

Chris
0
 
jefmelkenbeekAuthor Commented:
Is there a way to export all users from outlook global address book to csv file and import it with your script?
0
 
Chris DentPowerShell DeveloperCommented:

The global address list is built from AD, so rather than exporting from Outlook we'd be exporting from AD.

But that would make it a little silly importing it again, or are you importing into a different domain? If you are, you certainly can bulk export and re-import.

Chris
0
 
jefmelkenbeekAuthor Commented:
importing in a different domain I mean.
0
 
Chris DentPowerShell DeveloperCommented:

Ahhh, fair enough :)

This is an exporter for the fields above.

It's a bit messy, there may well be a better way to do this, tricky to change perspective after latching onto a viable method though ;) Anyway, it dynamically writes and executes the commands it needs which makes me happy.

Chris
$Fields = "company,displayName,sn,givenName,initials,mailNickName,mail,department," + `
  "physicalDeliveryOfficeName,title,telephoneNumber,Mobile,facsimileTelephoneNumber," + `
  "pager,streetAddress,postalCode,l,co,c,proxyAddresses,targetAddress"
$Fields = $Fields.Split(",")
 
$DomainRoot = New-Object System.DirectoryServices.DirectoryEntry
$Filter = "(&(objectClass=user)(objectCategory=person)(mail=*))"
 
$Searcher = New-Object System.DirectoryServices.DirectorySearcher($DomainRoot, $Filter)
$Fields | %{ $intPropertyNo = $Searcher.PropertiesToLoad.Add($_) }
 
$Results = @()
$Searcher.FindAll() | %{ 
  $Command = ""; $Fields | %{ $Command += [String]"@{n=`"$_`";e={`$_." + $_.ToLower() + "}}, ```n" }
  $Command = $Command.SubString(0, ($Command.Length - 4))
  $Command = "`$Results += `$_.Properties | Select-Object " + $Command
  Invoke-Expression $Command
}
 
$Results | Export-CSV -Path out.csv

Open in new window

0
 
jefmelkenbeekAuthor Commented:
Thanks a lot :), but it exports not all gab users from outlook, only 30% of it.
How can change the code below to replace first and last name filed with something else if it is empty?
exp:  ,Kevin,street,.. --> it has to be --> something,kevin,street,....
         

code: (replace only empty space for Alias)

$entry = $_
$DN = $_.First+" " +$_.Last;
$a = $_.First.Replace(" ", "")
$b = $_.Last.Replace(" ", "")
$Alias = $a+"_" +$b
0
 
Chris DentPowerShell DeveloperCommented:

> Thanks a lot :), but it exports not all gab users from outlook, only 30% of it.

Really? I can think of a few reasons that might be...

Is it a Forest? Or a single domain?

Are any of the missing entries anything other than Users in AD?

For the check you could do:

If (($_.First).Trim -eq "" -or ($_.Last).Trim -eq "") { # Fill with something else }

(# is the comment character)

Chris
0
 
jefmelkenbeekAuthor Commented:
it is a forest and the missing ones are "contacts".
0
 
Chris DentPowerShell DeveloperCommented:

Ah ha :-D

Then we just need to modify the filter :)

> $Filter = "(&(objectClass=user)(objectCategory=person)(mail=*))"

At the moment it only returns users, so we'd do this to add in contacts:

$Filter = "(&(|(&(objectClass=user)(objectCategory=person))(objectClass=contact))(mail=*))"

Which tells it to return Users (of the Person sort), or (|) Contacts but only if they have an e-mail address in the "mail" field.

Chris
0
 
Chris DentPowerShell DeveloperCommented:

Oops.. the Forest bit..

$DomainRoot = New-Object System.DirectoryServices.DirectoryEntry

We might want to change this a bit, we can use this:

$Forest = [system.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest()
$ForestRoot = [ADSI]"GC://$($Forest.FindGlobalCatalog().Name)"

Then we'd use the filter above, and this to search:

$Searcher = New-Object System.DirectoryServices.DirectorySearcher($ForestRoot, $Filter)

Chris
0
 
jefmelkenbeekAuthor Commented:
Hi Chris :D,
sorry for late response. I will test it this monday.
Have a nice weekend.
0
 
jefmelkenbeekAuthor Commented:
Hello Chris,
I 've tested it and it doesn't still export everything :(. In the export file, i found out that my name was not exported. Also is it possible to avoid system mailbox exportation?

Do you know also how it is possible to remove all non-ASCII characters from last and first name please?
I have this from you "If (($_.First).Trim -eq "" -or ($_.Last).Trim -eq "") { # Fill with something else }" and it is working very well, but is it possible to add other filters to remove non ASCII characters?
regards,


0
 
jefmelkenbeekAuthor Commented:
Hi Chris,
Can you just verify this script please, i'm making a mess. This script works fine(thanks to you :-) ).
I have a lot of problems to remove non-AScii characters and white spaces in "First" and "Last" -names. the script below doesn't replace withespaces with "none" comment.
If this script works , it will be great.


$OU = 'GAB_Contacts'
 
# Call CSV File & Create Mailboxes
 
Write-host "Creating Contacts..."
import-csv 'test.txt' |
 
 foreach{
 
$entry = $_
$DN = $_.First+" " +$_.Last;
 
 
$a = $_.First.Replace(" ", "")
$a = $_.First.Replace(".", "")
$b = $_.Last.Replace(" ", "")
 
#$b = $_.Last.Replace("(", "")   
If (($_.First).Trim -eq "" -or ($_.Last).Trim -eq "") { None }
$Alias1 = ($a+"." +$b).replace(")", "")
$Alias = $Alias1.replace("(", "")
If (($b).Trim -eq "") { None } 
  
  New-MailContact $DN -DisplayName $DN -FirstName $_.First -LastName $_.Last -organizationalunit $OU -Alias ($Alias -replace ('_|\s')) -ExternalEmailAddress $_.email
  
  #will fill automatically the attributes in when it finds the information in AD. /Uncomment the line below if you need auto fill in. 
  Set-Contact $DN -Company $_.Company -Title $_.Title -Department $_.Department -Fax $_.Fax -MobilePhone $_.Mobile -Office $_.Location -Phone $_.Tel -PostalCode $_.Zip -PostOfficeBox $_.POBox -City $_.City -StreetAddress $_.StreetAdd #-CountryorRegion $_.Country
 
}

Open in new window

0
 
jefmelkenbeekAuthor Commented:
Mabye we can use this in the script to remove non-ascii characters.


# build two alternation regex patterns with the unwanted chars:
$fChars1 = ':', '?', '/', '\', '*', '<', '>'
$fChars2 = '|', '"'
$pat1 = [string]::join('|', ($fChars1 | % {[regex]::escape($_)}))
$pat2 = [string]::join('|', ($fChars2 | % {[regex]::escape($_)}))
 
$name = '"Is there A Key?/Are you there?"'
# replace the unwanted char wth a ' ' or nothing if path is not valid
if (!(test-path $name -isValid)) {
$name = $name -replace $pat1, ' ' -replace $pat2
}
$name 

Open in new window

0
 
jefmelkenbeekAuthor Commented:
Hi Chris,

I will test it right away :).
Thanks
0
 
jefmelkenbeekAuthor Commented:
when I run the script, i got this error:
A parameter cannot be found that matches parameter name 'SystemStringTrim(ParamsChar[]trimChars),SystemStringTrim() SystemStringTrim(ParamsChar[]trimChars),SystemStringTrim()'.

0
 
Chris DentPowerShell DeveloperCommented:

Oops, we just need some parentheses :)

    $First =($_.First).Trim();
    $Last = ($_.Last).Trim();

That should work.

Chris
0
 
jefmelkenbeekAuthor Commented:
Sorry for bother you again, but it gives now this error :(.
The term 'None' is not recognized as a cmdlet, function, operable program, or script file. Verify the term and try again.
This error hapens for lastnames and firstnames. the script does not replace empty fileds with "none".
regards
0
 
Chris DentPowerShell DeveloperCommented:

Yeah I was wondering about that bit...

Are you just after it echoing "none" back to the command line? If so, we just need none in quotes:

If ($First -eq "" -or $Last -eq "") { "None" }

Otherwise it tries to treat it as a cmdlet which won't work out so well.

Chris
0
 
Chris DentPowerShell DeveloperCommented:

Oh sorry, you mean you want it to set the value to None?

If that's the case we would use:

If ($First -eq "") { $First = "None" }
If ($Last -eq "") { $Last = "None" }

That will need this line moving down as well:

$DN = "$First $Last";

That way we make all changes to the $First and $Last values before we generate or do anything based on those.

There is a bit of an issue with duplicate naming if we do that. Did you want to auto-generate a name if those attributes aren't specified?

Chris
0
 
jefmelkenbeekAuthor Commented:
The contacts are now crated with all same aliases.
0
 
jefmelkenbeekAuthor Commented:
if it is possible it would be great to auto-generate :)
0
 
Chris DentPowerShell DeveloperCommented:

Something like this...

The generated names are a long way from exciting. It just sticks a number on the end of a fixed string.

Chris
$OU = 'GAB_Contacts'
 
# RegEx setup - build two alternation regex patterns with the unwanted chars:
$fChars1 = ':', '?', '/', '\', '*', '<', '>'
$fChars2 = '|', '"', ' ', '.'
$pat1 = [string]::join('|', ($fChars1 | % {[regex]::escape($_)}))
$pat2 = [string]::join('|', ($fChars2 | % {[regex]::escape($_)}))
 
# Call CSV File & Create Mailboxes
 
$i = 0;
Write-host "Creating Contacts..."
import-csv 'test.txt' |
  foreach{
    $First =($_.First).Trim()
    $Last = ($_.Last).Trim()
 
    # replace the unwanted char with a ' ' or nothing
    $First = $First -replace $pat1, ' ' -replace $pat2
    $Last = $Last -replace $pat1, ' ' -replace $pat2
 
    if ($First -eq "" -or $Last -eq "") {
      $i++
      if ($First -eq "") { $First = "NoFirst$i" }
      if ($Last -eq "") { $Last = "NoLast$i" }
    }
 
    # Generate a DN and Alias
    $DN = "$First $Last"
    $Alias = "$First$Last"
  
    New-MailContact $DN -DisplayName $DN -FirstName $First -LastName $Last -organizationalunit $OU -Alias ($Alias -replace ('_|\s')) -ExternalEmailAddress $_.email
  
    #will fill automatically the attributes in when it finds the information in AD. /Uncomment the line below if you need auto fill in. 
    Set-Contact $DN -Company $_.Company -Title $_.Title -Department $_.Department -Fax $_.Fax -MobilePhone $_.Mobile -Office $_.Location -Phone $_.Tel -PostalCode $_.Zip -PostOfficeBox $_.POBox -City $_.City -StreetAddress $_.StreetAdd #-CountryorRegion $_.Country
  }

Open in new window

0
 
jefmelkenbeekAuthor Commented:
Hello Chris,
these are my fields " Last,First,Email,Company,Department,Title,Location,Tel,Fax,Mobile,POBox,Zip,StreetAdd,City,Country" in csv file, but now it does not read "Last" -name.
and the Alias are all the same
0
 
jefmelkenbeekAuthor Commented:
sorry Chris please ignore last comment.
I test it your last script, but it does now give another error: Cannot bind parameter 'Alias' to the target. Exception setting "Alias": ""(Ghunter)-hoste" is not valid for Alias.
0
 
Chris DentPowerShell DeveloperCommented:

Ahh koay, we can kill off the () in there? Perhaps if we add them to the bit you found:

$fChars1 = ':', '?', '/', '\', '*', '<', '>'
$fChars2 = '|', '"', ' ', '.', '(', ')'

Chris
0
 
jefmelkenbeekAuthor Commented:
I am still testing and it look realy great :D at this moment.
0
 
Chris DentPowerShell DeveloperCommented:

Well, fingers crossed :)

Chris
0
 
jefmelkenbeekAuthor Commented:
it is still importing :).
do you know how I can disable recipient update during import please? Now it is creating email addresse from file, but it also add the domain standard address.
0
 
Chris DentPowerShell DeveloperCommented:

You'd have to create an Email Address Policy to apply to contacts above the default address policy. You won't be able to stop it updating with the default if nothing matches above.

Chris
0
 
jefmelkenbeekAuthor Commented:
I got during last importation this error: You cannot call a method on a null-valued expression.
0
 
Chris DentPowerShell DeveloperCommented:

Which line did it flag? It would indicate that whatever was passed in was null, perhaps on read from the sheet (say a blank line at the end of the file).

Chris
0
 
jefmelkenbeekAuthor Commented:
Maybe you right.on the last line it gives this error.
0
 
Chris DentPowerShell DeveloperCommented:

If you get a chance to test it again it would be interesting to see if that's all it is. Quite a common one for scripts like this, it makes a lot of assumptions about the imported data :)

Chris
0
 
jefmelkenbeekAuthor Commented:
hellio Chris,
sorry I didn't understand the last one :(. what exactly do I have to do to test?
0
 
Chris DentPowerShell DeveloperCommented:

Either an import without a trailing blank line, or one with to reproduce the problem :)

It's not necessary if you're happy with what's imported though, it's just to see if we are right about the cause of the error.

Chris
0
 
jefmelkenbeekAuthor Commented:
sorry for late response.I'm still importing. it takes time :).
0
 
Chris DentPowerShell DeveloperCommented:

No problem, these things are rarely fast :)

Chris
0
 
jefmelkenbeekAuthor Commented:
I'd tested it and no problems. I've imported only 20 objects.
Now i will test with 500 objects
0
 
Chris DentPowerShell DeveloperCommented:

Great, good luck :)

Chris
0
 
jefmelkenbeekAuthor Commented:
It works fine now :D.i will test it with more objects during the weekend. till now you helped me a lot. Thank u very much.
Do you know how I can remove a certain emailaddress in powshell and disable recipient update in powershell?
0
 
Chris DentPowerShell DeveloperCommented:

Recipient Update doesn't exist in Exchange 2007, so there's nothing to disable. That makes it a little tricky, not sure you can stop the e-mail address policy applying.

We can remove e-mail addresses though, that's definitely possible :)

Chris
0
 
jefmelkenbeekAuthor Commented:
Hi Chris,
the import is working fine, but i saw a small problem. During the creation of contacts, i've got this error each time: the operation cannot be started, because the object was not found.
This error refers to "set-contact" for Tel nr, streetaddress,.. . The first time it doesn't want to set this info. the 2nd time it does when i run the script again.
0
 
jefmelkenbeekAuthor Commented:
I've increased the points for you :) for all my questions.
I need another big favour :(. Is it possible to built in a windows form with textbox, "Run" button and "stop" button, where all "contact creation" text files are listed(the files are on same folder as script). the script has to run all these files and after each file completion it has to give status "Ok" or "Not ok".
0
 
Chris DentPowerShell DeveloperCommented:

> During the creation of contacts, i've got this error each time: the operation cannot be started,
> because the object was not found.

Hmm we'd probably have to put that down to replication intervals. We might be able to improve matters if we set a specific domain controller for each command. Both have the same option available:

-DomainController <FQDN>

Worth a try :)

On the form, yes, it's possible I've just never tried it. I'll take a look.

Chris

0
 
jefmelkenbeekAuthor Commented:
It is still same problem, but no worries :).If it is possible for windows form it would be great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.