Addusers CSV file can be created using the following simple instructions (well they can if you’re an IT GEEK)
To run the addusers process it is necessary to have an addusers CSV (Comma Separated Value) text file for the program to read the user information from. The simplest way to create this file is to use Excel.
To start the process of creating the addusers CSV file you will need a list in xls format from SIMS which contains the following information:
• Name (Forename, Surname)
• Address line 1
• Date of birth
The first thing you will need to do is sort the data by address, this is done to randomise the student usernames; this is the only reason this field is necessary and it can be discarded after the sort (right click the column letter [B] > delete). Once the data is sorted you will need the following information in the file:
• User name
• Name (Forename Surname)
• Date of birth
• Description (as; ‘Name Intake Year’ e.g. John Smith Intake 11)
• Home drive letter (N:)
• Home drive path (as; \\userfileserver\Username$ e.g. \\studentdata\09001JSmi$)
• Profile path (\\studentdata\Student$\Profiles\All_Students)
• Login script name (login.vbs)
To create the username you will first need to break the ‘Name’ field you have into separate values using data > text to columns. Before you do this it is advisable to ensure that you have enough free columns after the name column you are splitting (any columns with data in them will be over written).
To achieve this it is best to copy and paste the Name column [A] to column J, that way all of the columns which the data will expand into are blank, plus you will still have the Name column [A] intact for later use, then; select column J > data > text to columns.
After the data has been split it will be necessary to remove any hyphens from names and to manually cut and paste any double barrelled names which had spaces into the relevant column.
At the end of this part of the process you need to end up with 2 columns (Forename and Surname, [column J and column K]) with NO SPACES and NO HYPHENS and no SINGLE QUOTES (‘) plus you will still have the original Name column [A].
At this point cut and paste the Name column [A] into column I and cut and paste the Date of Birth column [B] into column L
You will now need to create a column [M] of numbers from 001 (put 001 in row 1 002 in row 2 > select the two populated cells and drag [flood fill] down) to however many users you are adding (this column [M] will need to be formatted as text, exel may show this as an error, ignore this ‘error’).
It is now possible to start to create all of the fields you will need for the addusers CSV file to import the new users.
Within your addusers CSV you should now have columns A – M with data in columns I – M (provided you deleted the column with the address data in it).
In cell A1 you will need to type:
=CONCATENATE(“11”,M1,LEFT(J1,1),LEFT(K1,3))
This will create a username which consists of intake year (11) 3 random numbers (generated from the address sort) the first initial of the First Name and the first 3 letters of the Surname e.g. 11001JSmi
In column B cut and paste the contents of column I (the name column)
In column C cut and paste the contents of column L (the date of birth column).
In cell D1 you will need to type:
=CONCATENATE(B1, ” Intake 11″)
where 11 is the current year, don’t miss the space before Intake.
In cell E1 type:
N:
In cell F1 type:
=CONCATENATE(“\\studentdata\”,A1,”$”)
where studentdata is the file server containing the users home folders this will resolve to e.g. \\studentdata\11001JSmi$ as will be created in Bulk Adding AD Users
In cell G1 type:
\\studentdata\Student$\Profiles\All_Students
This path needs to be valid as the path to the student profile you will also need to right click and remove hyperlink on this cell.
In cell H1 type login.vbs
This is assuming the login script is available in \\[domain]\netlogon as login.vbs
Row 1 will now be complete with data you will need to drag (flood fill) columns A,D,E,F,G and H.
At this point there will still be data in columns J,K and M DO NOT DELETE this data yet.
Save the addusers CSV file as a CSV e.g. intake11.csv accept any warnings about format and close the file. Open the newly created addusers CSV file e.g, intake09.csv using Excel, delete the contents of columns J,K and M and format column C as date 00/00/00 this is because the initial student passwords need to be formatted in this manner. Once again save and close the file.
From Windows Explorer right click the addusers CSV file e.g. intake11.csv and select open with > notepad. The final step to make the file useable with addusers.exe is to insert a new line at the beginning of the document and type; [users] with the square brackets, then save and close the file.
DO NOT USE EXCEL TO REOPEN THE ADDUSERS CSV FILE FROM THIS POINT ON:
if you do so you will need to ensure you reformat column C as date 00/00/00 before closing it. You will also need to open the file in notepad and remove the 7 commas (,) from the file after the [users] entry.
written by Nate Allen
