Preparing a File for Importing Demographic Data

©Copyright 2008, Budd Churchward

Quite likely, when a new quarter or semester begins some of your classes change. It may become necessary to update the roll room lists. The most efficient way to do this is to prepare a simple file of data and have the Import Wizard read it and make the changes for you.

It is very important that this file be prepared carefully. You begin by directing the program you use for scheduling and or attendance to export a file to a spread sheet like Excel.

Next you will use Excel to convert the file to a form that the wizard can read. The prefered file type is called CSV. This stands for comma separated variables. What it means is that if you were to peek at the file it would look like this:

12978,Alexander,Jason,Mr. Jones,9,555-1212,M

Here we see each field of data separated by commas. We have a student ID number, last name, first name, teacher's name, grade level, phone number and gender.

When you look at the same file in Excel it looks like this:

Cleaning Up the Data

Next, because the import file will be data separated by commas, you must be sure that there are no commas anywhere in your data. For example if your teachers are listed last name first with a comma between them you must remove the comma.

Here is how:

• Click on the letter at the top of your teacher column to highlight the whole column.

• Use the keyboard shortcut of holding down [Ctrl] while typing the letter h to bring up the find and replace window.
  

• Put a comma in the Find what: box. Leave the Replace with: completely empty.

• Finally click the Replace All button as you see above.

When the wizard imports your data file it will try to figure out what the teacher's staff code is by looking at the first four letters of the name. If your staff is listed, like our first example above, with Mr. and Mrs. you can use the same technique described here for commas to remove these titles. Don't forget to include spaces that you want removed when you are filling in the Find what: box.

You can speed up the import process and make the Wizard's task more likely to succeed if you remove all the columns that we don't need to import. For example, if you are updating class lists, remove all the columns except the student's ID number and the teacher's name. The student's name, phone number, grade level etc. are not necessary at this time. The simpler the file the better.

If you miss something and there is a blank line or an extra punctuation mark, the Wizard will probably crash and you will see an error message. You will need to review you data file and find the problem then try again.

Saving the File

• In the file menu of Excel you want to select: Save As: and use the tiny arrow at the end of the box to navigate to your current hls data folder. The path to the folder is probably: c:/hls/hls08 where the last two characters represent the beginning of the current school year.
  

• You can give your file any name you choose. Also, change the file type to CSV by clicking the small arrow and selecting it from the list. You will probably need to scroll to find this choice.

• Click the button.

• Excel is going to warn you about things that do not matter to us. When you see this message simply click Yes.
  

• Excel is going to warn you again when you close its window. It will try to get you to save the file once more but you already have so this time click No.
  

Your file is now ready for the Import Wizard in HLS to collect your data and change your class lists.