Import bulk contacts from CSV into Exchange 2007 using PS1

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
 
  }

Select allOpen in new window

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

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,

 

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
 }

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?

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

Chris

Is there a way to export all users from outlook global address book to csv file and import it with your script?

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

importing in a different domain I mean.

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

Select allOpen in new window

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

> 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

it is a forest and the missing ones are "contacts".

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

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

Hi Chris :D,
sorry for late response. I will test it this monday.
Have a nice weekend.

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,

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
 
}

Select allOpen in new window

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 

Select allOpen in new window

Hi Chris,

I will test it right away :).
Thanks

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()'.

Oops, we just need some parentheses :)

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

That should work.

Chris

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

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

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

The contacts are now crated with all same aliases.

if it is possible it would be great to auto-generate :)

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
  }

Select allOpen in new window

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

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.

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

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

Chris

I am still testing and it look realy great :D at this moment.

Well, fingers crossed :)

Chris

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.

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

I got during last importation this error: You cannot call a method on a null-valued expression.

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

Maybe you right.on the last line it gives this error.

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

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

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

sorry for late response.I'm still importing. it takes time :).

No problem, these things are rarely fast :)

Chris

I'd tested it and no problems. I've imported only 20 objects.
Now i will test with 500 objects

Great, good luck :)

Chris

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?

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

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.

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".

> 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

It is still same problem, but no worries :).If it is possible for windows form it would be great