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!
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…
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 …
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”
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?
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
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:
- You have an extra “e” in the department variable on the new-adobject cmdlet. IE $departement should be $department.
- GivenName is not a valid Parameter for Get-ADObject. You will need to set this value in the OtherAttributes hashtable.
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
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!
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
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 !
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
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
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 ?
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
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 ??
Ok I found the problem.
My Excel is semicolon delimited.
I just added -Delimiter “;” and it works!
