Importing Users into Mac OS X Server (from a student information system)

Required (Extra) Software:
- Microsoft Excel or OpenOffice
- Passenger from MacinMind

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 - Saving as "Text" file
Step 6 - Importing into Passenger
Step 7 - Configuring Passenger
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.



For different types of fields Excel keeps the data in different ways. If you want to preserve the data exactly as it looks, select a column and specify that it's format is "Text". Then, click the "Finish" button.


Post-Import:


Step 3 [Cleaning the data]:

Notice that we have some anomalies in the sample data. One last name has a hyphen. One has an apostrophe. One of the first names is actually a first and middle. These are okay for the "Longname" or what Passenger calls the "Username". However, if you try to put any special characters in the "short name" or ldap "UID", the import will have some very strange results.

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 instance, let's make the shortname be the first initial and last name. We can easily accomplish this with a simple Excel function formula.


=left(b1)&g1

The function formula will take the left most letter of column B and combine it with the contents of column G. To easily apply this formula to each line select the cell in which you placed the formula (H1 in this case), hold down the "shift" key, and select the last cell (in this case it's H5; in your case it may be H2733).



Now select the Edit menu -> Fill menu -> Down.


Voila!

Notice that we have two shortnames that are identical (ssmith). You can leave it and let Passenger append a number to the duplicate names, or you can make sure they are unique in excel. For example, you could add a dash and the district ID # to the short name - the function formula in this example would be =left(b1)&g1&"-"&c1.

Now let's create some UID numbers. In order to set the numbers based on graduation year, we will need to resort the data, which requires a title row. 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".



The UID could now be based on the district ID. For Jose, 200517895. To do this with a function formula use 2005&c1. Alternatively, we could make the UID serialized 2005001, 2005002, etc. If all of your district ID numbers are the same number of digits, the first option is preferable. However, often times the district ID numbers will vary in length. The problem with this is, in Workgroup Manager, when you sort by ID, you will end up with multiple groupings for each year.

To accomplish the second option, type 2005001 in the first available cell next to the first user that is graduating in 2005, then type 2005002 in the next row.


Yes, I changed my data.

Now highlight both cells and shift click on the last cell in this column that lists a senior. Select the Edit menu -> Fill menu -> Series.



In the Series window select the "Columns" radio button and the "Linear" radio button and set the Step value to 1. Then click OK.



Now repeat the process for the other grade levels.

Note: There are many different functions in excel that are useful for manipulating your data. I have shown just a couple of the ones that I use commonly. To see other functions and how to use them, select an empty cell. Then select "Function" from the "Insert" menu. It will bring up a window where you can search and select different functions.


The Function window displaying the "Left" function used above.


Step 5 [Saving as "Text" file]:

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 "Text (Tab delimited)" from the "Format" drop down menu.

Finally, click save. If you get an annoying message or two, like the one 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 6 [Importing into Passenger]:

- Open Passenger.
- Click the "Import" button at the top left.
- Select the radio button next to "Import users from delimited or XML file".
- Click "OK".
- Select the file that you saved from excel.
- Click "OK".

Now, this part is very subjective. It depends entirely on your data.
- For my example, I will select the "Ignore first line" check box, because my first line is just labels and not actual user data.
- Next, I select "<Ignore>" from the "Available Fields" list and click the ">>Add>>" button, because the first column is the unclean last name data that I don't want in my directory.
- Then, "First Name" and ">>Add>>".
- The District ID I could use as a comment or as the password. I'll use it as a comment. So, I select "Comment" and ">>Add>>".
- I'm going to use the DOB for the password. So, "Password" and ">>Add>>".
- I'm going to ignore ("<Ignore>" and ">>Add>>") the grade. We could use this as a comment, but we only have one comment field. If you wanted the district ID and the current grade in the comment, you could combine them in Excel.
- I'm also going to ignore the homeroom teacher ("<Ignore>" and ">>Add>>"), but this could be useful for creating groups later on. So, if you're going to combine fields for comments, consider adding this one.
- Next, I select "Last Name" and ">>Add>>" (this is our modified last name field).
- "Short Name" and ">>Add>>". This is the first initial combined with the last name.
- Finally, "User ID" and ">>Add>>".



Once you think you have things lined up correctly, click on "Sample Import" to double check.

If the sample looks good, click "OK" on the sample window and "OK" again on the import window.


Step 7 [Configuring Passenger]:
Note: If you've been struggling through this entire process, take heart we're almost finished.

Let's start with the "Export" settings.
- Select "Mac OS X Server:" from the drop down menu at the bottom right, then click the "Setup" button.
In the "Mac OS X Setup" window:
- Uncheck "Create User ID # starting with:", unless you didn't setup unique ID numbers in Excel.
- If you want your users to use terminal, you can leave the "Shell:" drop down at its default setting. Otherwise, select "None". Note: This setting is not for security. If you want to keep them out of terminal completely, you should deny them access in Workgroup Manager.
- Select "Password Server" from the "User Password Type:" drop down. Note: NEVER select "Basic". This was an old and insecure password type that will fail for most uses.



We're done here. Unless you have a specific reason, leave the other tabs alone (Generally, these settings can be more easily configured in Workgroup Manger), and click "OK".

Now, back in the main Passenger window:
- Unless, you took care of them in Excel, select the checkbox next to "Append number for duplicates". Note: If you don't check this Passenger will not export until you correct any duplicates. A dialog will show you the duplicates, so that you can go back and edit them individually. You can do this right in Passenger by clicking the "Edit Data" button.
- Leave "Concatenation" alone. It's for directly manipulating Usernames and shortnames in Passenger. Hopefully, you already took care of that in Excel.
- At the bottom left, Passenger requires you to put in a password. Why? Who knows, it must be for some other application of Passenger. Just humor the programmers and put two identical words in there (apple apple for the unoriginal).



Finally, click the "Export" button at the bottom right, and save the file (you may want to change the name so it doesn't overwrite your other file).


Step 8 [The reason we're all here - Importing the users]:

- Open Workgroup Manager and Connect to your server.
- Select "Import" from the "Server" menu.
- Select the file you exported from Passenger.
- 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".

Congratulations! You did it. Now, test some users to make sure it worked and take the rest of the weekend off.