Author Posts

June 29, 2016 at 12:37 pm

Hello everyone,

I want to import contacts that I have received a CSV file in our AD.
So I made a script and edit the CSV file that any corersponde.

Here is the script:

Import-Module ActiveDirectory 
$Users = Import-Csv -Path "D:\userslist.csv"  
foreach ($User in $Users)  
{  
    $OU = "OU=TestGPO,DC=coditel,DC=be"  
    $FirstName = $User.givenName 
    $LastName = $User.sn
    $DisplayName = $LastName + " " +$FirstName
    $Company = $User.company
    $Department = $User.department
    $EMail = $User.mail
    $TelephoneNumber = $User.telephoneNumber
    $Mobile = $User.mobile
    New-ADObject -type Contact -Path $OU -Name $DisplayName -DisplayName $DisplayName -GivenName $user.givenName -OtherAttributes @{'sn'=$LastName; 'company'=$company; 'department'=$departement; 'mail'=$EMail; 'telephoneNumber'=$telephoneNumber; 'mobile'=$mobile}
}

Looks like this Excel file:

sn givenName mail Company department telephoneNumber mobile
PANTA Sandra sandra.panta@sfrserviceclient.fr R SERVICE CLIENT SA RD Aix – Groupe 7 +33 4 00 00 00 00 +33 6 00 00 02 00

I started the script from my PC (not the AD because I have not Excel installed on it) and he created me a user / but nothing else!

Should I start the script from the server? Should I install excel on the server? Script wrong?

Thank you!

June 29, 2016 at 12:49 pm

Are you not better off creating this through exchange if it is a contact ? something like the below

you do not need excel to read the csv file

 $Contacts = "D:\userslist.csv"

 
 Import-Csv $contacts | ForEach-Object { New-MailContact -Name $_."Name" -ExternalEmailAddress $_."Email-Address" -FirstName $_."firstname" -LastName $_."lastname" -OrganizationalUnit "OU=Contacts,OU=Users,DC=Domain,DC=Name,DC=com" -Alias $_."Alias" }

csv just needs the appropriate headers in the top row

Name, Email-Address, Firstname etc.........

  • This reply was modified 2 years, 3 months ago by  Mark Prior.
  • This reply was modified 2 years, 3 months ago by  Mark Prior.

June 29, 2016 at 12:57 pm

That change if I put them in the Exchange?
Just execute the script from the PowerShell console of the Exchange then?

I know add fields in the Exchange department, company, phone number, etc.? In your example not so I ask myself ...

The Excel should look like then? :

Lastname Firstname Email-Address Company(??) Department(??) telephoneNumber(??) mobile(??)
PANTA Sandra sandra.panta@sfrserviceclient.fr R SERVICE CLIENT SA RD Aix – Groupe 7 +33 4 00 00 00 00 +33 6 00 00 02 00

Thanks for help in advance!

PS : Alias is use for what ? I dont need a alias .. I dont have that column in my CSV ..

June 29, 2016 at 1:11 pm

from memory i do not think the new-mailcontact supports the telephone etc.., try adding using the set command at the end

 $Contacts = "D:\userslist.csv"

 
 Import-Csv $contacts | ForEach-Object { New-MailContact -Name $_."Name" -ExternalEmailAddress $_."Email-Address" -FirstName $_."firstname" -LastName $_."lastname" -OrganizationalUnit "OU=Contacts,OU=Users,DC=Domain,DC=Name,DC=com" -Alias $_."Alias" | Set-Contact -MobilePhone $_."mobile" -Department $_."Department" -Phone $_."phone" -Company $_."company"}

not tested as i do not have laptop with me today, the

$_."columnNameGoesHere"

June 29, 2016 at 1:20 pm

I'll try that in a moment, but suddenly, you may realize why I wanted to go through the AD rather than by the Exchange?

June 29, 2016 at 1:50 pm

looking at the error you havnt changed OU to suite your environtment,

error looks to start at charector 71 "Externalmailaddress" have you updated CSV to read correctly / have correct column headings?

i.e added alias & renamed values?

if alias is not needed just remove

-Alias $_."Alias"

from the line

June 29, 2016 at 1:54 pm

Hi Florian,
There are a couple of code issues, but what do you mean "he created me a user / but nothing else!". When you use New-ADObject to create a contact, it just creates an AD Contact. If your AD Object is in fact being created, then you look at the Object class Attribute, it should be "Contact". This does not; however, mean you will be able to see it as a contact in an Exchange environment. To do that, you must enable it as a MailContact. The easiest way to do that is to use the Enable-MailContact Exchange cmdlet.

Script issues:
1) You have an extra "e" in the department variable on the new-adobject cmdlet. IE $departement should be $department.
2) GivenName is not a valid Parameter for Get-ADObject. You will need to set this value in the OtherAttributes hashtable.

June 29, 2016 at 3:25 pm

Really sorry, a bit rotten morning I did not concentrate, I did not blunder!
I will withdraw Alias. email Level I put CECEI for the column: Email-Address

June 30, 2016 at 6:49 am

Hello,

When i run the script from the Exchange :

Cannot bind argument to parameter 'Name' because it is an empty string.
+ CategoryInfo : InvalidData: (:) [New-MailContact], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorEmptyStringNotAllowed,New-MailContact

Script :

 $Contacts = "\\coditel-12\Engineering\6-IT\userslist.csv"

 
 Import-Csv $contacts | ForEach-Object { New-MailContact -Name $_."Name" -ExternalEmailAddress $_."Email-Address" -FirstName $_."firstname" -LastName $_."lastname" -OrganizationalUnit "OU=TestGPO,DC=Domain,DC=Coditel,DC=be" | Set-Contact -MobilePhone $_."mobile" -Department $_."Department" -Phone $_."phone" -Company $_."company"}

Excel :

Name lastname firstname Email-Address company Department phone mobile
PANTA Sandra PANTA Sandra sandra.pantaleon@serviceclient.fr SERVICE CLIENT SA RD Aix – Groupe 7 +33 4 00 00 90 00 +33 6 00 00 02 78

Someone can help me ? Thank you!

June 30, 2016 at 6:58 am

Hi Curtis,

I change what you tell me but .. No success ..
How i can execute this script ? Copy/Past on a powershell console doesn't work ?

Edit :
Error when i start on AD :
New-ADObject : Cannot bind parameter 'OtherAttributes' to the target. Exception
setting "OtherAttributes": "Object reference not set to an instance of an obje
ct."
At D:\Extract2.ps1:14 char:103
+ New-ADObject -type Contact -Path $OU -Name $DisplayName -DisplayName $Dis
playName -OtherAttributes <<<< @{'sn'=$LastName; 'givenName'=$FirstName; 'comp any'=$company; 'department'=$department; 'mail'=$EMail; 'telephoneNumber'=$tele phoneNumber; 'mobile'=$mobile} + CategoryInfo : WriteError: (:) [New-ADObject], ParameterBinding Exception + FullyQualifiedErrorId : ParameterBindingFailed,Microsoft.ActiveDirectory .Management.Commands.NewADObject Ty !

June 30, 2016 at 8:48 am

Hi Florian

the script works fine, you may want to check csv file, heres a pic of the script the file and the produced contact

http://s33.photobucket.com/user/markpriorgtst/media/collection.jpg.html

Thanks

June 30, 2016 at 9:04 am

Hi Mark,

Here the script :

 $Contacts = "\\coditel-12\Engineering\6-IT\userslist.csv"

 
 Import-Csv $contacts | ForEach-Object { New-MailContact -Name $_."Name" -ExternalEmailAddress $_."Email-Address" -FirstName $_."firstname" -LastName $_."lastname" -OrganizationalUnit "OU=TestGPO,DC=Domain,DC=Coditel,DC=be" | Set-Contact -MobilePhone $_."mobile" -Department $_."Department" -Phone $_."phone" -Company $_."company"}

Here the file excel :
http://s50.photobucket.com/user/FlorianSchinas/media/Capture_zpst44ghaw1.jpg.html

I dont know where i'm bad ...

Here my test :
http://s50.photobucket.com/user/FlorianSchinas/media/bug_zpsib7atlwo.jpg.html

June 30, 2016 at 10:09 am

Hi Florian

looking at

http://s50.photobucket.com/user/FlorianSchinas/media/bug_zpsib7atlwo.jpg.html

There is no "name" field in $contacts, however it looks like the excel file has it. Perhaps $contacts was an old varible? or maybe pointing to an old file ?

June 30, 2016 at 11:07 am

sorry florian i cant see whats wrong with that, from pics shown this should be working, i can see the name column is there and populated

June 30, 2016 at 11:53 am

The name is a fusion of two column on my excel, but i convert it on csv, so i don't think the problem can from my Excel ?
I really want import this user soon, i don't know why don't work, is really strange !

And why my command don't show the same that you for user ??

July 1, 2016 at 7:21 am

Ok I found the problem.
My Excel is semicolon delimited.
I just added -Delimiter ";" and it works!