Solved

Importing Users into AD

Posted on 2011-09-23
19
344 Views
Last Modified: 2013-11-05
I"ve run into an issue trying to import users into Active Directory. I know there are other methods, I just can't figure out why this method isn't working. I have an Excel worksheet that in the first column is the user first name, second column is the last name, third column is the child OU, forth column is the parent OU. On the second Excel worksheet is a concantenate formula which puts everything into the correct format from the information provided from the first worksheet. The third worksheet is a summary that is saved as a formatted text file. I then save that file as a batch file on the root drive and run it from the command prompt. If everything is correct it will  put the users into the assigned OU with a first initial last name account, assign a password and have the user change it at log on.

My problem is the import works, but it does not assign the first initial last name. It assigns only the last name. What I did initially was copy and paste the first names from one Excel sheet into the second one that I use for importing. I copy and paste first names, then second names. The strange part is, if I manually type the first name into the Excel worksheet used for importing, it works the way it should. If I copy and paste, it leave out the first initial of the first name.

My question is why does the manual entry of the first name work and copy and paste doesn't? Copy and paste works for the second name, but not the first. I'm doing this on Server 2008 R2.

Thanks,
Mike

0
Comment
Question by:aintgot1
19 Comments
 
LVL 39

Expert Comment

by:Krzysztof Pytko
Comment Utility
Can you post here a syntax which is prepared by you or that macro if possible?

Regards,
Krzysztof
0
 

Author Comment

by:aintgot1
Comment Utility
I don't have it with me right now. I can post it in the early evening. I forgot to bring it home.
I ran into a similar issue using a vbscript and Excel, but that is a different story. I will post it early tonight.

Thanks,
Mike

0
 
LVL 10

Expert Comment

by:gaurav05
Comment Utility
Hi,

Example 1: Importing data with -i -f filename
To import a file into Active Directory use the CSVDE -i  which tells it to import.  -f users.csv  is the filename.  So the complete command is CSVDE -i -f users.csv  Note the space between the command and each switch.  The first line, referred to as the header, should have a list of the field names. For example, DN, FirstName, Surname.

Preparation: Open the text file with the users, and add LDAP attributes to the top row.

Command: CSVDE -i -f usersi.csv

Example 2: Using the -k to force an import
Command: CSVDE -i -k -f usersi.csv

Example 3: A CSV file with the LDAP attributes in the top row.
Preparation: If you wish to use this example below, firstly copy and paste it into notepad.  Then change the attributes to match your circumstances.  For example, change WonderDom to your domain name, and change the OU=Worcester to OU=yourOU.

I suggest importing the file into Excel, making your changes, then exporting it as a .CSV file.

dn,sAMAccountName,userPrincipalName,telephoneNumber,department,userAccountControl,objectClass
"CN=Peter Graham,OU=Worcester,DC=WonderDom",Peterg,Peterg@WonderDom.com,891-1239,Production,512,user
"CN=Janie Bourne,OU=Worcester,DC=WonderDom",Janieb,Janieb@WonderDom.com,891-1239,Production,512,user
"CN=Edgar Dunn,OU=Worcester,DC=WonderDom",Edgard,Edgard@WonderDom.com,891-1230,Production,512,user
"CN=Belinda Hart,OU=Worcester,DC=WonderDom",Belindah,Belindah@WonderDom.com,891-1231,Production,512,user
"CN=May Jamieson,OU=Worcester,DC=WonderDom",Mayj,Mayj@WonderDom.com,891-1232,Production,512,user
"CN=Leroy Ota,OU=Worcester,DC=WonderDom",Leroyo,Leroyo@WonderDom.com,891-1233,Production,512,user

Command CSVDE -i -k -f user3.csv

Troubleshooting
Check that you understand LDAP attributes and syntax: DC=domain, DC=COM.
Make sure that Excel is not open at the .csv you are trying to create.
0
 

Expert Comment

by:erisraven
Comment Utility
Syntax would indeed be helpful. Check the formatting on the excel files - is perhaps the column with first names picking up an incorrect format somewhere?
0
 
LVL 39

Expert Comment

by:Krzysztof Pytko
Comment Utility
OK, so I'm waiting for samples and will help you :)

Krzysztof
0
 

Author Comment

by:aintgot1
Comment Utility
erisraven:

I will post the syntax from the excel file later. At this point I'm not sure if it is the formatting or not. If I clear the contents of the column with the first name and manually type in the first name, it is picked up and displayed correctly. If I clear the contents of that column and copy and paste the first name in that column, it is not picked up and not displayed in AD. Copying and pasting the last name works fine in the last name column. Strange. Once I post the syntax and the formula that pulls the information, I'm sure that will shed some light.

Thanks,
Mike
0
 

Author Comment

by:aintgot1
Comment Utility
Here is the excel file. On the first workheet are the names to be imported in this order. I copy and past the first and last names in columns a & b and auto fill the other information in c & d.

 Column A           Column B         Column C               Column D
First Name          Last Name         ChildOU               PrimaryOU
  John                    Doe                  Sales                   Miami              

The second worksheet contains the source script. Ther is nothing to populate except the domain and suffix.
  Script                      Username             ChileOU        ParentOU           Domain               Suffix
dsadd user "cn=        JDoe,                   ou=Sales,        ou=Miami,       dc=Contorso,      dc=com"

Here is the formula for column B:
 =CONCATENATE(LEFT(AddUsersInfoHere!A3,1),AddUsersInfoHere!B3,",")

Formula for column C:
=CONCATENATE("ou=",AddUsersInfoHere!C4,",")

Formula for column D:
=CONCATENATE("ou=",AddUsersInfoHere!D3,",")

On the final tab, this is what it would look like:
dsadd user "cn=JDoe,ou=Sales,ou=Miami,dc=globomantics, dc=com" -fn John -ln Doe -pwd P@ssw0rd -mustchpwd yes

I would edit the password to what I wanted and save it as Formatted Text and rename it to a batch file and then run it from a command prompt.

My problem is it would not pick up the first name, only the last. If I manually type in the first name, it would work fine.


Thanks,
Mike


0
 
LVL 39

Expert Comment

by:Krzysztof Pytko
Comment Utility
Yeah, I had the same problem in the past and I decided to write short and simple macro to acomplish that.
Please download this macro file, remove ".txt" from its extension (leave only .bas) and import this module into Excel file. Run this macro to generate required dsadd syntax.


dsadd.bas.txt
0
 
LVL 39

Expert Comment

by:Krzysztof Pytko
Comment Utility
Sorry, I pressed "SUbmit" before the end of the previous post :/
To extend my previous answer:

When syntax is generated, select G column and copy its content into notepad. Save that as batch file (with cmd or bat extension) and run on a DC or workstation with Administrative Tools installed.

PS: I saw that you missed some very important parts of DSADD USER syntax. I fixed it, and you have the absolute minimum to create new AD users from command-line. Each new created user will be only a member of Domain Users group.

I would suggest to set up also "Home Drive", "Home Directory", "Logon Script" and "Description". Additionally, I would suggest to put also other groups membership. If you need assistance, just let me know.

Krzysztof
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:aintgot1
Comment Utility
Thanks, I'll try it out on Monday. What did you find out was causing the user's first name not to be displayed?

Thanks,
Mike
0
 
LVL 39

Expert Comment

by:Krzysztof Pytko
Comment Utility
I can't remember if it was FN, I guess it was problem with Last Name of Display Name. I couldn't get Excel's formulas to work and as the last chance I tried with macro. After all everything was working fine :)

Please test it, and let me know how it's in your case :]

Krzysztof
0
 
LVL 39

Expert Comment

by:Krzysztof Pytko
Comment Utility
Hi,

does it work for you or you have some problems? Maybe you need further assistance? :)

Krzysztof
0
 

Author Comment

by:aintgot1
Comment Utility
Yesterday was not a good day and I didn't get the chance to try it. I will attempt to try it today.

Thanks,-
Mike
















































































0
 
LVL 39

Expert Comment

by:Krzysztof Pytko
Comment Utility
OK, if you need any assistance, just let me know :)

Krzysztof
0
 

Author Comment

by:aintgot1
Comment Utility
Krzysztof:

Sorry that I haven't posted back earlier. If possible, can you post step by step instructions? For some reason I don't think I did it right. It sounds very basic but I think I missed something.

On a side note, I figured out what went wrong in my excel file. When I copied from my excel file with the users, the users first name had a space between the left edge of the cell. That's why the first name was not picked up. When I had manually typed in the first name, there was no indentation and it worked.

Anyway, I would still like to try out your method.


Thanks,
Mike
0
 
LVL 39

Accepted Solution

by:
Krzysztof Pytko earned 500 total points
Comment Utility
Great! That's good you figured out what was the problem! :)

If you're still interested with my method, let's start short guide ;)

1) Open your Excel file with users data (make sure that leading space from First Name cell is removed :) )
2) Download attached file and rename its extension to filename.bas
3) In Excel sheet, press Alt+F11 to open Visual Basic editor
4) From menu "File" choose "Import file" and point to the location where you saved that .bas file
5) Press "play" button on tool box bar and wait for "Done" message window.
6) Close Visual Basic editor and check Column G You will see all necessary syntax for DSADD USER command
7) Copy Column G content and past it into empty "Notepad" file
8) Save "Notepad" file as batch file (with .bat or .cmd extension)
9) Run it in command-line on a DC or workstation with Administrative Tools installed. Wait until batch will finish its job.
10) Refresh Active Directory Users and Computers domain and check if users were created in specified OU with specified attributes

That's all! :]

Krzysztof
0
 

Author Comment

by:aintgot1
Comment Utility
Well, that explains why it didn't look right. I wasn't doing it correctly. That now looks simple enough that even I can do it. I'll try it out and let you know.

Thanks,
Mike
0
 
LVL 39

Expert Comment

by:Krzysztof Pytko
Comment Utility
:)
OK, if you need any other assistance, just let me know :) I would try to help

Krzysztof
0
 

Author Comment

by:aintgot1
Comment Utility
Krzysztof:

That worked out fine! I did not know about the Alt + F11 sequence, which is why it didn't look right. It worked out just right. Things have been pretty busy which is why I couldn't get back, but it works!

Thanks for your patience!
Mike
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Resolve DNS query failed errors for Exchange
In this article, we will see the basic design consideration while designing a Multi-tenant web application in a simple manner. Though, many frameworks are available in the market to develop a multi - tenant application, but do they provide data, cod…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now