CUIP Tech Site
For Tech Coordinators, NSP-CUIP Techs, and CUIP Staff
| Previous | Up | Next |
CUIP Tech SiteFor Tech Coordinators, NSP-CUIP Techs, and CUIP Staff |
|
addusers.exe by entering the right data in "ADDUSERS".
addusers.exe will be able to read the file by entering [USER] in cell A1 of worksheet 'ADDUSERS'.
addusers.exe input file format.
Now that you've got the file from the AR database on a server, you have all the information you need about the student body to create network account for them. From here on out, you'll be spending most of your time with Microsoft Excel. What you'll be doing is, in essence, tricking Excel into speaking a language it doesn't actually know how to speak, DOS. Excel is good at using the same formula on sets of similar data to generate standardized lists, and DOS is good at reading certain properly formatted lists of information. Unfortunately, the two don't know a single thing about each other, so keep in mind that everything you do in Excel has to be done exactly if DOS is going to understand it properly.
In this step, you'll be transforming some of the data from the AR database into a format that can be read by the DOS command-line program addusers.exe, which you downloaded and installed in the last section. For more information about what addusers does, see Addusers Overview. To start with, open Excel, and once it's open, open the file "students.csv" from \\Server\LoginProject\Data\Students. Excel should now have the data from AR separated into columns where the original had commas. It should have the columns: @FNAME, LNAME, ID, BIRTHDAY, GRADE, GENDER, and CHARAS. If everthing looks OK, choose "Sort..." from the "Data" menu. In the "Sort by" field, put LNAME, and in the the "Then by" field, enter @FNAME. This orders the database by last name and then by first name. Make sure you check the box next to "Header Row" under "My List Has...," or the category names will be sorted along with the rest of the data.
The formulas we will be using from here on out will often refer back to this worksheet. Because DOS needs everything to be exact, it is absolutely essential that the first sheet stay in this sorted order. If the order changes, other sheets, which you will start creating in just a moment, will change to. If those changes aren't perfectly synchronized, the different batch files we want to create will get all out of order vis-a-vis one another, and the accounts will come out all wrong. For this reason, we want to make sure that other worksheets only refer to this sheet unless absolutely necessary.
Since we will be using and re-using the students' User ID, so we want them to be in this sheet where they will always be tied down to the proper students' data. In H1, enter UID and in H2, enter the formula
=CONCATENATE(B2,LEFT(A2,1))
We should unpack this tangled mess so that you know what it does. Once you've typed it into H2 and hit enter, text will appear in the cell. Where did come from? How did it get put together? Look back at the formula. The "=" sign tells Excel that what comes next is a formula, a recipe for making something out of other, separate bits. "CONCATENATE" is a function that combines two or more strings into one. The strings to be combined are between the parentheses that follow CONCATENATE, and are separated by a comma. But what we've done here is use formulas within a formula.
B2 is the first of these formulas for strings to feed into CONCATENATE. It means: "Get the data that's in cell B2." Column B is the last names of the students, and B2 is the first one. The second formula comes after the comma after first one and reads LEFT(A2,1). This formula uses the function LEFT(), which tells Excel to start at the left of some string and take some number of characters. The string comes first and the number of characters comes second in the LEFT() function, so this formula means: "Start at the left of the data in cell A2 and take one character." The A column contains the first names of the students, so this formula retrieves the first initial. So altogether, =CONCATENATE(B2,LEFT(A2,1)) tells Excel to combine the last name and first initial of the student in the second row into a single thing and put it into the cell where you entered the formula. You can check that it works by comparing H2 with A2 and B2.
On of the beautiful things about Excel is that this is only time you have to do this. Now you can use Excel's AutoFill function to make all the other User IDs. First scroll down to the last entry. Make note of the number of the last row with data in it. Now, highlight cell H2, and click and hold on the little box or cross in the lower right hand corner of the cell border. Your cursor should change shape. Drag downward, staying in column A, to the row that matches the last row of the first worksheet. When you let go of the mouse button, you should see all the rows you've just higlighted fill in with more User IDs.
The last thing to do in this step is to go back to the top of the list of User IDs. Start at the top, look down at each one in turn. If you find two or more that are the same, add numbers after them to make them different. It is essential that none of the User IDs be the same, or some student won't get their own account and you'll have errors further on as follow these instruction. In order to do this, double-click on the cell with the first of the duplicate UIDs. This should bring up the formula =CONCATENATE(BX,LEFT(AX,1)). For the first duplicate, add ,1 between the last two parentheses, so the new formula in that cell will look like
=CONCATENATE(BX,LEFT(AX,1),1)
For the second duplicate, bring up the formula and insert ,2, and so on. Do this for each set of duplicates, so that instead of three JACKSONMs and two ADAMSQs, you will have JACKSONM1, JACKSONM2, JACKSONM3, ADAMSQ1, and ADAMSQ2.
The next column, column I, lists the password that students will have when they first login to their accounts. For the purposes of this document, the password will be the students ID #. So, in cell I1, enter Password. Since the students' ID numbers are already listed in column C the formula to enter in I2 is very simple:
=C2
Now use the AutoFill function just like you did before, and VOILA! every User ID is now accompanied by a password. Some may be wondering how in the world we'll manage to get each student their password when we've sorted them by name and not by classroom, teacher, or even grade. Well, because we've created the user IDs and passwords on this sheet, we can now resort this sheet without fear of throwing future sheets out of whack. So once everything is finished and up and running, it will be possible to sort this sheet by, say, classroom, before name and then distribute the results each class' teacher(s).
Now we have a worksheet with all the source data we will use from here on out. Now we need to manipulate this data into a form that the program addusers.exe will understand. However, we need to leave this first sheet alone, at least for now, so create a new worksheet in this workbook. You can do this either by pulling down the "Insert" menu and choosing "Worksheet" or by right-clicking on the tab at the bottom of the window that says "students.csv" and choosing "Insert" then "Worksheet". A new tab should appear next to "students.csv" that says "Sheet1". Double-click on the tab and change the name to "ADDUSERS". This worksheet will be made using a variety of formulae to transform the source data on the "students.csv" worksheet into a format that addusers.exe can read.
The first thing to do in "ADDUSERS" is to give it a title that tells addusers.exe what to do with it. In cell A1 of "ADDUSERS", enter [User] in cell A1. When this worksheet is exported as a file on its own, [User] will be on its own line, letting addusers.exe know what to do with the stuff that follows.
The first thing addusers.exe looks for after [USER] is a set of lines, each of which needs to start with a UID. So in the first cell of the first row after [USER], cell A2, enter
=students.csv!H2
Recall that in the first worksheet, named "students.csv", H2 is the first cell in the column that lists UIDs. The formula students.csv!H2 tells Excel "Get the data that is in cell H2 of the worksheet named students.csv and put it here." Now, AutoFill, the rest of column A in worksheet "ADDUSERS" with the formula you just entered in cell A2.
After the user name, addusers.exe needs to know the user's full name. So in B2, enter
=CONCATENATE(students.csv!B2," ",students.csv!A2)
This time we are using CONCATENATE() to add the first name to the last name with a space in between, which creates a single string that has each students' first name.
The next thing that addusers.exe needs to know is the password that will go along with each UID. Since we already made the passwords in the "students.csv" sheet (in Column I), we can just tell Excel to put it in the "ADDUSERS" sheet the way we did with the UIDs. In Cell C2 of "ADDUSERS", enter
=students.csv!I2
, and AutoFill it. Already, we're well on our way, but there's more work to be done. Saving now would be a good idea.
The next thing we need to enter is the Profile Name for that goes along with each user ID. It's simplest if each students' profile name is just the same as their full name, so in D2 enter:
=B2
and use Autofill.
In the next column we need to put the drive letter where the students' home directory will live. This is up to you and your network's configuration, but "H:" is a good bet, since it can stand for "Home." So in cell E2, enter the drive's letter followed by a colon and do the AutoFill dance.
Now, in column F, we're going to fill in the path that the computers the students' log in to will use to find the students' home directories. This path starts with \\SERVER. The two slashes will tell the computer to look on the network rather than on the local drives, and you will replace SERVER with the network name of the server where those folders will be stored. The next part of the path is name of the network share that corresponds to each students' home directory. To keep matters simple, the name of the network share will be the same as the student's User ID. It ends with a dollar sign to make sure that it isn't visible through the Network Neighborhood or the Network Places. This gives some security to the students' network folders. The formula for this is another CONCATENATE():
=CONCATENATE("\\SERVER\",A2,"$")
Make sure to replace SERVER with the network name of the computer that will hold the home directories. Enter this formula into F2 and use AutoFill to apply it to every student.
The next two things we need to add in are the path to the profiles directory and the login script. The profiles directory and student login script are the same for all the students,
\\SERVER\profiles
and
student.bat
, respectively. So enter the first into G2 and the second in H2, and AutoFill both at once.
And now, we're all set with this sheet. So save the the whole workbook, but make sure to change the format to "Excel Workbook" and set the location to the "Batch" folder under "Students" in the "LoginProject" folder. Now, make sure you're still on the "ADDUSERS" sheet, pull down the "File" menu and choose "Save As...". Set the format to "CSV (comma-delimited)." This will save this sheet as lines of data, with each column separated by a comma instead of in separate columns. This is a format that addusers.exe can read. Set the save location to the "Batch" folder under "students" in "LoginProject".
Now, if you open up the file you've just created with a text editor like Notepad or Wordpad, and you've done everything right, the first two lines will look like:
[User]
SMITHA,Andrew Smith,9999,Andrew Smith,H:,\\SERVER\SMITHA$,\\SERVER\Profiles,student.bat
If there is a series of commas following [User], delete them or addusers.exe may not be able to properly interpret the file.
The last thing you need to do is use this file you've just created to actually create the students' user accounts on the Primary Domain Controller. There are two different ways to do this. This first is if you are actually sitting at the PDC. To do this, click "Start" and then "Run". In the "Run" box, type in command and hit ENTER. This will open a DOS command-line interface that will look like C:\>. When you see this, type in
addusers /c C:\...\LoginProject\students\batch\addusers.csv
Replace C:\... with whatever drive letter and other directory path you need before \LoginProject\students\batch\addusers.csv. Then, hit ENTER, and you should see the accounts being created. Watch carefully for errors. You may have to go back and correct these later.
The other way to do this, and usually more comfortable because the PDC is usually in a crowded, stuffy, dusty MDF, is to do this from another WinNT or Win2K machine. If you do this, open the DOS window the same way, but type in
addusers /c \\SERVER ...\LoginProject\students\batch\addusers.csv
The new part is \\SERVER, and tells addusers.exe which computer should have the accounts entered in. Make sure to replace ... with whatever the path to "LoginProject" is. Then hit ENTER and watch your handiwork fly!
Next: Batch User Creation - MD
Previous: Batch User Creation - Before You Start
Up: Batch User Creation - Table of Contents
See also: CUIP Home
See also: CUIP Tech Home
CUIP Tech Site Map(not yet working)
This page is: http://tech.cuip.net/topics/logins/addusers.html
Author: Ben Buckley, benb@cuip.net
Last updated at 09:11 2005n June 17, 2005.
It has had
674750
visitors.