One of my most frequently asked questions is ‘how do I import grades from an Excel spreadsheet into SIMS Assessment Manager. Here’s how:

Step 1: Clean the Excel File Ready for Import

Open your Excel file and check that the columns you want to import contains the forename and surname of each pupil, preferably in two separate columns. Ideally the spreadsheet should also contain the pupil UPN numbers too, though this is not essential, it will make import easier – especially if you have pupils with similar names. 

Edit: Lydia (in the comments section below) recommends checking the resultset you want to import into is unlocked  – otherwise your data will not be imported. That’s a good tip and easy to forget. From memory there is no error message when this happens. Go to Focus | Assessment | Resultset to check.

Step 2: Save the Excel File in an ‘XML Spreadsheet 2003’ format

Go to the Office button (assuming you are using Excel 2007 or higher) and click on the ‘Save As’ menu option. From the ‘Save as type’ drop down menu choose ‘XML Spreadsheet 2003’ and save it. Remember the new name and remember the subdirectory you save it in – you’ll need to find this file again.  You’ll be warned that the new spreadsheet may contain features that are not compatible. Choose ‘Yes’ to keep the spreadsheet in this format. Close the file down (the next step won’t work if you leave the file open).

Step 3: Open the File in SIMS

Log into SIMS in the usual way.  From the SIMS menu at the top of the screen choose Routines | Data In | Assessment | Import from Spreadsheet. From the browser, navigate to the file you created in the previous step. Click on ‘Next’.  SIMS will now open your spreadsheet and display a version of it on the screen. See below:

Step 4: Match the Column to their SIMS Equivalent

Now we have the file opened in SIMS we need to tell SIMS what each column contains. Column 2 contains the pupil surname and forename (above). Right click in the grey area that forms the three cells below the number 2. You’ll see this menu:

The column in my example contains Surname and Forename in the same column so choose the ‘Surname Forename’ option. The most useful column to match pupils with is always going to be their UPN number, so if it exists in your Excel spreadsheet you’ll always want to use it. UPN, Surname and Forename are the three most useful columns for pupil matching. If you don’t have the UPN number Surname and Forename will usually match 96% of your pupils. Date of birth is often presented by Excel in the wrong format for SIMS.

Use the scroll bars to display the columns to the right. You’ll see that the column headings correspond with the columns in our original spreadsheet. Locate the column that contains the data you want to import. Go to the three grey cells at the top of the grid and right click (see below):

Again, you’ll see this menu:

Choose the ‘Result’ option this time and you’ll see a list of all the aspects in SIMS (a SIMS aspect is just another name for a result). It should look something like this:

Choose the aspect you want to import the data into.

At the bottom of the screen is an entry for ‘Result Set’. Click on the small magnifying glass to display a list of your result sets.

Choose the result set you want to use with your aspect.

Click ‘Update’ and SIMS will re-display your spreadsheet with the details you chose added to the header rows at the top. It should look like this:

Repeat this process for the remaining results. Briefly:

• Identify the column that you want to import;
• Right click the column header;
• Choose the relevant result aspect and make sure the result set is correct

If you make a mistake, you can remove the contents by right clicking the column header and choosing the ‘Clear’ option. When you have finished, your screen should look something like this:

Step 5: Match the Pupils in the Spreadsheet with the Pupils in SIMS

At this point, SIMS will try to match the pupil names from the Excel spreadsheet with the pupils in its own database. Hopefully, you’ll get a perfect match but there are two potential problems: either SIMS is unable to match any pupils to a name on the Excel spreadsheet, or SIMS matches more than one name. Your screen should look something like this:

Notice that the first few ‘students’ aren’t students at all – they’re just the first few rows from the original spreadsheet. Naturally, SIMS hasn’t matched these rows to any pupils and the corresponding row. This isn’t a problem. Scroll down the right hand list.

If you find any blank entries on the right hand side then SIMS has been unable to match any corresponding pupil on the left hand side – this is usually where a pupil has left the school since the Excel file was created and hence most schools will be happy not to import any data for that pupil. However, SIMS might have failed to match a pupil because of an incorrect UPN or a spelling mistake in the name. If that’s the case, click the magnifying glass icon to choose the correct pupil.

If you see an entry that says, in yellow, ‘MULTIPLE STUDENTS AVAILABLE’ then SIMS has found more than one pupil with the same name. Click the notepad icon and choose the correct pupil from the list.

Step 6: Finishing Off

Now click on ‘Finish’. SIMS will perform the import, and then display a list of any issues, problems or errors that it might have encountered. Common error messages will indicate pupils with blank results, or perhaps pupils with invalid grades. You can print or save the error messages and review them later.