[sims] importing formatted spreadsheet

[neil27]

Hi,

 

I have previously asked for ( and received ) help in my quest to import data from excel into SIMS assessment manager. I have set up the aspects, Templates and empty marksheets in SIMs and exported the formatted markseets to Excel and populated them with the appropriate information.

I now want to import these formatted markseets back into SIMS. SIMS help say i should use Routines-Data in -Assessment Import from spreadsheet. I started to do this but stopped when I noticed that it had included on the head of my columns todays date. I can't see how SIMS will put this data into the appropriate year for the boys concerned. The result sheets are from several years ago. Can anyone tell me how to ensure that the data will be allocated correctly in the SIMS assessment manager database.

 

Thanks

[vikpaw]

It will go in to the aspect+resultset combo from the marksheet. The result set is the common way to tie the data to a year.

 

If you're not using a resultset it will use the result date and store multiple values.

 

You should be able to alter the result date in the marksheet to help with this if I recall correctly.

 

How do you want the data to go in?

[number34]

The enttry date is what you see at the top of the column and it has no bearing on the result set that is being used. Carry on importing.

[vikpaw]

But it will have a bearing if you don't use a resultset in your column. I think the results are ordered by the result date, so you might want to put a date in if there is already data in the aspect. This is only relevant if it's a grade aspect where you don't associate a resultset and want to see the history.

 

You can alter it in the spreadsheet, or on the import screen by right clicking on the date. It's easier to do it in the spreadsheet.

[neil27]

Thanks for your help. I'm sorry but I'm not sure what you mean by " a resultset in you column". All the columns have results in them, I just can't see how Sims will associate them with the correct date when I import them. Will it pick it up form the original marksheet which I exported to Excel?

 

Thanks

[LosOjos]

Thanks for your help. I'm sorry but I'm not sure what you mean by " a resultset in you column". All the columns have results in them, I just can't see how Sims will associate them with the correct date when I import them. Will it pick it up form the original marksheet which I exported to Excel?

 

Thanks

 

A resultset is not the same as a result. A resultset in SIMS is a way of identifying a specific set of results. For instance, a common use would be Term 1, Term 2, Term 3 etc. Any results entered in to a column will be allocated to the linked resultset, which can make identifying specific results much easier than relying simply on dates.

 

If you are not using resultsets, then the date on the spreadsheet is the date that the results uploaded will be set at, so you need to alter it.

 

I highly suggest getting hold of a copy of the AM7 documentation, understanding how the various components of AM7 (aspects, gradesets, resultsets) tie in to each other could save you a lot of work and headaches down the road.

[neil27]

Thanks, I'll see if I can get hold of a copy.

[number34]

Neil,

sorry about my rather misleading short reply, just read your original post and see that you mentioned result sheets not result sets. LosOjos is right to point you in the direction of the documentation and if there are any courses in your area I would recommend trying to get on one.

[vikpaw]

When i mentioned resultsets in your column, i was referring to the columns in your marksheet. You said you've set up marksheets to store your data.

Can you give us some examples, of the columns there. If you used a resultset then you should be okay so long as you still want the data put into the same 'fields' on the system.

 

If you didn't use resultsets in the marksheet, then you need to be a bit careful. Either adding a date to the imported data, or editing the marksheet template and adding some resultsets.

 

Either way, i'm sure we can get you through this before you attack the manual. You can find this by hitting the documentation button on the SIMS homepage.

 

You need to give us some more info. Which group of students are you importing for, are they current results or historic results..

[neil27]

I am trying to import historic internal school exmaination results which are currently held on Excel spreadsheets. For any one spreadsheet there are columns for the names of the pupils and results for maths, english .... I want to import these results into SIMS so that they are accessible from there ( and cn then be viewed in Discover - but that's my next part of the task!). To this end I have:

1. Set up aspects for exam results in each subject

2. Set up an examination template which has columns using the aspects in the same order as the excel spreadsheet.

3. Set up a test marksheet for one year group - I used year 6.

4. Opened the marksheet and changed the groups dates so that it picked up the pupils from the correct dates ( Sept 2008 to July 2009 )

5. Exported this into Excel as a formatted marksheet ( I think)

6. Copied and pasted the appropriate results from the school's Excel result sheet into my imported marksheet

I now want to Import this back into SIMS but I am concerned that SIMS might not have sufficient info to store it correctly. I started the Import process but stopped midway as I couldn't tell how SIMS would recognise the dates the data was associated with:

Does the Import screen have an option to set the date the data refers to?

Does the fact that the marksheet was formatted mean that it carries with it the dates I set?

I have now read the info about creating result sets but haven't found a way of associating my template with a particular result set.

Can anyone point me the right direction at this point.

Thanks

[number34]

I think that you are almost there and if there is only one result for each exam then you don't need to worry about the date of entry. If there are more results for each exam then I would either use different aspects, eg Maths Y5, Maths Y6 etc or use a result set, eg Year 5, Year 6, together with a single Maths aspect. It will really depend on what you want to do with the data once it is in SIMS.

[LosOjos]

I now want to Import this back into SIMS but I am concerned that SIMS might not have sufficient info to store it correctly. I started the Import process but stopped midway as I couldn't tell how SIMS would recognise the dates the data was associated with:

Does the Import screen have an option to set the date the data refers to?

Does the fact that the marksheet was formatted mean that it carries with it the dates I set?

 

The group membership date is really just a filter, in so much as it shows you which students were in your selected group (i.e. marksheet) on that date. The date you need to change is the result date. When you import the spreadsheet, on the screen that pops up showing your data in a datasheet, you will see a date below the title of each column. If you right click this and select "Result Date", you can change the result date for that column.

 

 

I have now read the info about creating result sets but haven't found a way of associating my template with a particular result set.

Can anyone point me the right direction at this point.

 

Result sets are not related to templates, but to aspects within a template. When you insert an aspect in to a template, you have the option to select a result set. By assigning a result set to an aspect, all results, regardless of date, entered in to that column on that template will be linked to that result set. This means that if, for instance, you created a result set called "Y6 Result" and linked that to an aspect called "Maths Exam", and you wanted to later show those results in a different template, you could do so simply by adding the same aspect linked to the same result set. It's just a simplified way of handling sets of data - rather than relying on the date to know what a result relates to, you can give it a meaningful label in the form of a result set.

[vikpaw]

Although you probably don't need resultsets I am sure Discover likes them.

 

I would make a resultsets called Y6 exam, Y7 exam etc. you could equally well just call then Y6 term 2, if exam is part of the aspect. This way any other results you associate with the same resultset can be easily compared.

 

In the assessment manager template section, load your marksheet template and there is a button to bulk change the resultsets for all columns.

 

You will then need to export a new formatted sheet and fill it in again.

 

As stated if each aspect will only ever store one value for the exam then you are fine. E.g it's called y10 English exam. Or gcse etc.

 

If its generic like an internal maths exam and you reuse the same aspect each year you would need to specify the result date in the import spreadsheet so they are ordered correctly. Or, you could add a resultset so it differentiates them. An aspect is unique. Adding a resultset to it, makes the combo of them unique.

 

I am more worried about making sure you place the correct grades against the right student. Any change in student name, spelling, preferred name different to legal, joining or leaving can cause a dangerous offset in your sheet.

 

I have a very rigorous set of guidelines for doing this I'll try and dig the document out.

 

Basically it helps to include gender and admission number columns in the marksheet to help with matching names.

 

I usually export import unformatted sheets as its more flexible even though it takes time to manually match each column with the related aspect.

[neil27]

I am thinking hat i need to scrap the template/marksheets I have made and redo the template (after creating suitable result sets) associating the aspects I use to the appropriate result set. I will then use the new template to create new marksheets and export them to Excel. I have previously checked the names from SIMS/Excel against each other and they look ok. I will then copy and paste the exam results nad import it back into SIMS setting the result date as part of the process. Does this soubnd ok?

[vikpaw]

Create resultsets and add them to your template. No need to start from scratch.

Then you can clone it and change the resultset.

[neil27]

Thanks, I have done all that and I have now imported one set of exam results from 2009. Unfortunately, while they show up correctly when I open the marksheet for the correct group of pupils when I open an individual pupils assessment results it shows my newly imported results as being on the 1st March when i imported them - rather than 6th june 2009 when they sat them. Can you tell me what i have done wrong? I couldn't see anywhere on the result set to fix a date for the results - there was one on the exported marksheet, but as that part was read only I couldn't change it.

Thanks

Edited March 2, 2013 by neil27
grammar

[vikpaw]

A resultset isn't usually fixed to a particular date, it's a general idea of the time, at least that is the common usage. e.g. Y8 Term 1, could be a different date for kids of different ages.

It will always use the result date you imported as the result date, but you should be able to ignore this. What you have probably found is that it looks like it's a present result due to having a current date on it. You should still on the import spreadsheet have a row for the result date which you can modify to push the results into the past. It shouldn't be read only. Worst case on import it should be editable by right clicking on the date, but it takes much longer to do it one at a time there.