Importing Users into Mac OS X Server 10.4 (from a student information system)
Required (Extra) Software:
- Microsoft Excel or OpenOffice
Download a Cheat Sheat
Contents:
Step 1 - Getting the data
Step 2 - Opening the file in Excel
Step 3 - Cleaning the data
Step 4 - Manipulating the data
Step 5 - Configuring for Export/Import
Step 6 - Save as CSV (Windows) Document
Step 7 - Clean the Quotes or Not
Step 8 - Importing the users
Step 1 [Getting the data]:
Get an export from your district database of the necessary student information. You will need some sort of text file. It can be tab delimited (.txt) or comma separated (.csv) or just plain excel workbook (.xls). If you have a choice, get tab delimited; that's what I'll demonstrate in this document. You should be able to find a district support person or even an on-site administrator to do this for you. Often times they will give you the file on a floppy disk, so you may need to find a usb floppy drive or ask them to email you the file.
Make sure to get the appropriate information! Ultimately, you will need the following, which, except for password should be unique:
-Username
-Shortname
-Password
-Unique ID (User ID)
These can come directly from the district database, or they can be modified, or even, completely made up. We will look at some simple ways to manipulate the data later. For now, consider getting:
-Firstname
-Lastname
-Grade or graduation year
-DOB (Birthday)
-District ID
-Teacher (or Homeroom teacher)
Step 2 [Opening the file in Excel (or OpenOffice)]:
First open Excel. Then select "Open" from the "File" menu. Browse to the text file that you obtained in Step 1. Select the file and click on the "Open" button.
Note: If you are unable to select your file, enable "All Documents" from the drop down at the top of the open dialog box.
Depending on your file type, excel may bring up a wizard to help you open it correctly.
Select Delimited in the first screen, and click the "Next" button. |
 |
Select the appropriate delimiter in the second screen, and click on the "Next" button again. |
 |
To avoid compromising the original data, copy the entire column that you would like to manipulate and paste it into a new column.
Copy column A
Paste in column G
Now it's time to remove the bad data. Select "Find" from the "Edit" menu (or click on the Apple button and the "F" key simultaneously). Since the "G" column is still selected, we will only be searching within this one column.
Find " " (spaces) and Replace with "" (Nothing)
You can use the "Find/Replace" feature to find spaces, apostrophes, hyphens, etc. and remove them, or to find them so you can manually change the data.
Here it is with all the data normalized in column G. Notice that I deleted the apostrophe, but I edited out the second name where there was a hyphen. This is completely up to your discretion, and it would not negatively affect the import if you chose to keep both names.
Step 4 [Manipulating the Data]:
Now it's time to take the data you have and make it look the way you want. For many purposes a graduation year is necessary, so let's add a grad year column. In order to efficiently add this column to a large file, we need to sort things a bit. This requires a title row. So, select row "1" by clicking on the "1" at the left.
Next select "Rows" from the "Insert" menu. Excel will give you a new row and you can label your columns appropriately.
Now click on the little diamond symbol at the top left of your document (<>). Then, select the Data menu -> Filter menu -> AutoFilter.
Now we can sort by any of our columns, and excel will treat each row as an individual record. So, click on the drop down menu next to "Grade" and select "Sort Descending".
In column "H" we can add the grad year now. Put the grad year for your Seniors (or 8th graders, or 5th,... whoever is at the top) in cell H2. Select H2 and, holding down the shift key, scroll down to the last Senior. With all the interim cells highlighted, select "Fill" -> "Down" from the "Edit" menu. Repeat with each grade.
Note: If you are an elementary or middle school, consider using the year that students will graduate from high school, then you have the option of shipping their home folders with them up to the next school. A district-wide standard can be very useful.
Now let's configure a primary shortname in column "I". Short names must be unique and consistent. That is, no matter how many times I re-import the users, I want to use the exact same short name. To accomplish this, I recommend using the DID as the short name, or at least as a part of it. It's also useful in some circumstances to have the grad year included in the short name.
Note: It is possible to have multiple short names, but the primary short name cannot be changed and generally directly corresponds with the name of the home folder. I recommend configuring one short name as just the district ID. This way you can insure that a user can login even if they go by a different last name or a different first name or the district has them graduating a year early or late, etc..
Let's get to it. In cell I2, enter the formula, "=RIGHT(H2,2)&G2&C2", which means the right 2 characters of cell H2 combined with cell G2 and cell C2. Some databases have spaces all over that need to be removed. If this is the case, you can use the trim() command to take out spaces at the beginning or end.
Once you have I2 correct, use the handy "Fill" -> "Down" from the "Edit" menu again, and you should be done with the primary shortname!
Step 5 [Configuring for Export/Import]:
Since we're going to bypass Passenger or any third party Import File Makers, there is some obscure stuff that we need to configure, so let's set it up in a separate sheet. To create the new sheet, right-click (ctrl-click if you don't have a modern mouse) on the tab at the bottom of the sheet, in my case it's labeled "districtexport.txt", and select "Insert ...". In the window that pops up, select "Worksheet". Now, for the sake of being organized, I'm going to switch the worksheets order and label them (by double-clicking), so they look like this:
I'm also going to save it for posterity as an Excel Workbook, IMPORTTEMPLATE.xls
Now, to begin filling out our "Export This" WorkSheet. For some reason, we can't use the first column or we get inconsistent import results. So, put "Void" or "DON'T IMPORT" in A1 and fill down to match the number of total records that you have in "SeedData".
Next, let's configure our shortnames. In B1, we want I2 from Seed Data and a colon to separate the two values and C2 from Seed Data.
So the formula will be "=SeedData!I2&":"&SeedData!C2". As always, fill down to modify all records.
Note: If you want additional short names, you can add them with colons in between each. Every shortname must be unique as compared with every other short name of every other user.
The process to complete the rest of "Export This" is going to contain many identical steps, so I'm going to skip over the step by step, and focus on design and formulae from here.
- Column C will be "Real Name", which I generally put as last name, comma, space, first name, space, district ID. This assumes that they will be logging in with one of the shortnames in column B. This allows a natural sorting by last name in Workgroup Manager and the ID again insures uniqueness and consistency. The formula is: =SeedData!G2&"\, "&SeedData!B2&" "&SeedData!C2. Since we will eventually be saving this as a comma delimited file, the "\" will be used to escape the comma, such that WGM doesn't count it as a separate field.
- Column D will be password which I'm seeding with date of birth. So the formula will be: =SeedData!D2.
- Column E will be the first of the Home Directory attributes. WGM calls this one NFS Home Directory. It is the full automount path to a users home directory. The first part is /Network/Servers/; then the fqdn of the home directory server (I'm using server.domain.edu as a place holder); then the path to the home folder from the root of the server, in my case /Volumes/DataHD/Homes/grad year/primary shortname.
"Homes" is the share point and grad year is an interim directory between the share point and the actual home folders. This is often referred to as "Advanced" or "Nested" home directories. The reason for configuring your server this way is that each automount that you configure consumes additional resources on your server. It's generally the case that if you have 5 autoumounts, each client workstation that's logged in will make five separate connections to the server. This will quickly use up the available server resources. In fact, if at all possible, I try to have only one sharepoint mounted per client. To accomplish this I put all group folders and home folders within a single automount.
In any case, the formula I'm using here is: ="/Network/Servers/server.domain.edu/Volumes/DataHD/Homes/"
&SeedData!H2&"/"&SeedData!I2
This will need to be modified if you have more than one Home Directory server. You could dynamically modify it within the file or create two (or more) files.
- Column F will be the second of the Home Directory attributes. WGM calls this one simply, "Home Directory" It has two distinct parts which are defined using xml. The first is the afp path to the share point (notice the backslash to escape the colon). The second part is the path from the share point to the home directory. Here is the formula: ="<home_dir><url>afp\://server.domain.edu/Homes</url><path>"
&SeedData!H2&"/"&SeedData!I2&"</path></home_dir>"
- Column G will be the Authentication Method. This is required in order to make the server store the password securely within Password Server. Otherwise, it assumes a crypt password which is less secure, and doesn't work with very many services. It doesn't really have a formula, but is a fill down of: dsAuthMethodStandard\:dsAuthClearText (notice once again we have the backslash to escape the colon).
- Column H will be a comment field in which I'm putting the teacher's name that we got from the district import. The formula is: =SeedData!F2.
- Column I will be Password Policies. Again there is no formula, but a fill down of : newPasswordRequired=1:canModifyPasswordforSelf=1.
The colon is not escaped due to the fact that these are actually two separate password policies. The first says that the user must change their password at first login and the second allows them to change their password in general. If you don't want students to change their passwords, then you could import canModifyPasswordforSelf=0 instead. There are many other importable password policies. The easiest way to see them is to create a user manually, set up various policies in WGM and export that user.
Step 6 [Save as CSV (Windows) Document]:
|
Finally a quick step! Once you have the data looking exactly how you like it, select "Save As..." from the "File" menu. Choose where you want to save the file and what you want to name it. Then, select "CSV (Windows)" from the "Format" drop down menu. Do not select CSV (Comma delimited). WGM needs the Unix Line Feeds which a Macintosh file does not have. Windows files have both line feeds and carriage returns. Thus, a windows formatted file will work. Finally, click save. If you get an annoying message or two, like those to the right, congratulations! You did it correctly, just click "yes" or "okay" or whatever you need to do to get Excel to save your file. |
  |
Step 7 [Clean the Quotes or Not]:
Note: If you've been struggling through this entire process, take heart we're almost finished.
Since Excel doesn't know about our handy escape character (\), it put quotes around the Real Name field, because it has a comma in it. The quotes don't really hurt anything and you can import as is. But, they may bug you. If you want them gone:
- Open the csv file that you just saved in "Text Edit".
- Select "Find" -> "Find..." from the "Edit" menu.
- Put a " in the "Find" field.
- Click the "Replace All" button.
- Select "Save" from the "File" menu
Step 8 [The reason we're all here - Importing the users]:
- Open Workgroup Manager and Connect to your server using the directory admin user.
- Select "Import" from the "Server" menu.
- Select the csv file that we saved above.
- Select "Ignore new record" from the "Duplicate Handling:" drop down menu. Note: If you want to replace existing records, you should first delete them, and then import.
- Leave the other drop downs at "None" unless you have a preset that you want to use for all of the new users.
- Leave the ID boxes blank, and click "Import".
A new window should pop up asking you to enter Record Type, Special Characters, and Field Mappings.
The record type is Users. The special characters should match this:
Record delimiter: 0x0A - Newline
Attribute delimiter: 0x2C - ,
Attribute value delimiter: 0x3A - :
Escape character: 0x5C - \
The Field Mappings should match this:
All but AuthMethod can be selected from the drop down menu. For AuthMethod, select "Other" from the drop down menu. And select AuthMethod from the secondary menu that comes up.
Notice some of the other options. All of these can be imported. If you would like to import something that I haven't covered, try some reverse engineering. Create a user account, configure it with the desired options, and export it. The export file will have a record description at the top with attributes that correspond to those you can select in this import window. Otherwise the file is colon delimited. Find the attribute your interested in, then count the colons to see which value is attached to that attribute.
At long last, you are ready to click the "Import" button. Go ahead, it usually doesn't bite.
Congratulations! You did it. Now, test some users to make sure it worked and take the rest of the weekend off.