Solved

how to import the User details in Ative Driectory from Excel

Posted on 2011-09-20
5
291 Views
Last Modified: 2012-05-12
Dear Experts
we are using AD 2003, in AD only user account has been created. they are 150 users, now i want to update users details in AD at one single step with Excel Sheet.
1.  i have exported all  user account in the excel sheet.
2. then i have updated all the users accounts details in excel. now i want to import the excel sheet to AD 2003, so that all the users should be updated in signal step


0
Comment
Question by:mshakeb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 10

Accepted Solution

by:
gaurav05 earned 500 total points
ID: 36571759
Hi,

Instructions to Create a Simple CSVDE Import Spreadsheet
1) Copy my example below and paste into an Excel spreadsheet at precisely cell A1.

objectClass,sAMAccountName,dn
user,Petergr," CN=Peter Graham,OU=Newport,DC=cp,dc=com"
user,Janiebo," CN=Janie Bourne,OU=Newport,DC=cp,dc=com"
user,Edgardu," CN=Edgar Dunn,OU=Newport,DC=cp,dc=com"
user,Belindaha," CN=Belinda Hart,OU=Newport,DC=cp,dc=com"
user,Mayja," CN=May Jamieson,OU=Newport,DC=cp,dc=com"
user,Leroyot," CN=Leroy Ota,OU=Newport,DC=cp,dc=com"

2) In Excel, select the Data Menu and then Text to Columns.  Naturally, choose the comma delimiter. Save the file as .csv for example, Newort.csv

3) Make sure that the 3 LDAP fields are in the first row.  (ObjectClass, sAMAccountName, and DN.)

4) Once you have opened the file in Excel, it is easier to manipulate the values.  For example, you may wish to find and replace dc=cp, dc=com with the name of your domain as we discussed earlier.

 5) When you have finished preparing the spreadsheet to your liking, then Save As and make sure you select Save as type CSV (Comma delimiter).  Since the next step is the command prompt, save the file into an easily accessible folder. E.g. C:\csv.

The Actual CSVDE -i Import Command
After all the hard work in preparing the spreadsheet, we are now ready for the import.  Open the CMD prompt, navigate to the folder where you saved your .csv file.

Type this command:  CSVDE -i -f Newport.csv


let us know is there any issue.
0
 
LVL 24

Expert Comment

by:Sandeshdubey
ID: 36571921
Windows server provide you with 2 utility tools to modify existing AD accounts - CSVDE or LDIFDE
http://www.computerperformance.co.uk/Logon/CSVDE_LDIFDE.htm

What you do is to export your AD user accounts into a csv/spreadsheet, modify it and import it back to AD. Easy.

CSVDE
http://technet2.microsoft.com/WindowsServer/en/Library/1050686f-3464-41af-b7e4-016ab0c4db261033.mspx

LDIFDE
http://support.microsoft.com/kb/237677
0
 
LVL 10

Expert Comment

by:gaurav05
ID: 36571933
Hi,

ldifde -f Exportuser.ldf -s <Server1> -d "dc=Export,dc=com" -p subtree
-r "(&(objectCategory=person)(objectClass=User)(givenname=*))"
-l "cn,givenName,objectclass,samAccountName"

also check this.
http://support.microsoft.com/kb/276382
0
 
LVL 7

Expert Comment

by:ComputerBeast
ID: 36572438
Hi,

Refer to the article:

http://blog.scottlowe.org/2006/06/20/mass-changes-in-active-directory/

Hope it works.

Thank you
Anil
0
 
LVL 1

Author Closing Comment

by:mshakeb
ID: 36934988
problem sloved
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I am going to show you how to simulate a multi-site Lab environment on a single Hyper-V host. I use this method successfully in my own lab to simulate three fully routed global AD Sites on a Windows 10 Hyper-V host.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question