[acs] CMIS - Importing data into CMIS. Can it be done?

[mathsman]

I've just started looking into using CMIS and am trying to teach myself. I've been told that Year 10 targets have to be inputted manually which is hours of work. Does anybody know if and how you can import targets on mass via an Excel spreadsheet?

[CheeseDog]

Yes it can indeed be done.

 

Our cluster of schools used this guy to create a tool for them.

 

Hugh Kirke

 

The real answer of course is to get it inputed into CMIS in the first place.

[Banjo]

You don't need a tool you can run a data import routine within Facility. Ideally the Excel sheet will contain three items; Student ID, Subject code and Target Level.

 

You can then create an import routine that will take this data straight into the Template.

[mathsman]

You don't need a tool you can run a data import routine within Facility. Ideally the Excel sheet will contain three items; Student ID, Subject code and Target Level.

 

You can then create an import routine that will take this data straight into the Template.

 

Thanks for the advice. Do you happen to have a set of instructions for that or a reference to a CMIS manual?

It would save hours of data inputting which seems such a waste of valuable time when it could be done in seconds.

[APC]

There is a manual which should be available from your support provider (LA or serco direct)... a word of caution tho - test your data transfers on a copy database, you dont want to corrupt your live database with test data etc..

[robk]

There is a manual which should be available from your support provider (LA or serco direct)... a word of caution tho - test your data transfers on a copy database, you dont want to corrupt your live database with test data etc..

I must second that!

 

Test your data transfer on a backup database. Then backup the live system before you import. These things can go very wrong very quickly.

[Banjo]

From memory it's the very last step that can cause big time issues - it's a pretty innocuous step (can't remember it from the top of my head) but if you don't get it right it ignores the import rules that you have set up on where the data should go, and slaps the results in a shotgun fashion into the database!

[PyROm]

Have a look at http://s3.parature.com/ics/support/DLRedirect.asp?fileID=87416 (you dont need the target points, you need to be logged into sercofirstline). Ignore the backfill section.

 

After doing whats in the linked pdf, select the data transfer you created, click the transfer button and select Test Run. You want to select No to the box that pops up so you can tell it which assessment/column you want it in. Select the exam from the drop down box, due to a bug you then have to hit cancel (after selecting the exam it doesnt update the options lower down until you go back in). Then hit transfer->Test Run again, this time you can click on the import column name and select which assessment table column you want i to go into. If all goes well you now do Transfer->Do Transfer.

 

Make sure you excel sheet has no spaces in the sheet names, or the name of the file itself and normally we set the columns in the excel sheet to text (if you use picklists in your assessment column you are importing into you want to import it as varchar rather then a number). Importing assessments has always been iffy for us as if you already have data in different datasets it can create a duplicate record that confuses the system (one time it will show results from the first record, the next time you view it you get the results from the second.) I normally try to avoid importing data into columns where theres already data there for the student.

[TS1988]

All of the above advice regarding backups and test runs is very important and must not be ignored - the option of setting up a stand alone for testing purposes may also be worthwhile if other data is being entered.

 

Generally for target data as long as the following fields are in the spreadsheet and populated you can then transfer straight into nsturesults.

 

- Setid (This can be specified during the import routine - however I usually just included this in my spreadsheet, e.g. 2011/2012)

- Moduleid (Id for subject - this is case sensitive)

- Deptid (Id for department - this is case sensitive - only include if your school use departments and have these setup within facility under Data|Academic|Departments)

- Criteriadata (This is the column for the target)

- Examid (This can be specified during the import routine - however I usually just included this in my spreadsheet, e.g. KS3TRGT)

- Assessid (This can be specified during the import routine - however I usually just included this in my spreadsheet, e.g. _KS3TRGT)

 

 

A quick note that the details column names I have defined above can be called anything within the spreadsheet. Try to avoid any fancy formatting and generally the spreadsheet should be formatted as text, with the obvious exception for numbers which can be set as integers.

 

During the transfer routine, all data will usually go in with data set as varchar.

[MrGravell]

Sorry to drag up an old thread. The link from PyROm is now dead - any ideas where I can get instructions on importing?

 

Also is it possible to import events? Event data is spread across two tables so this is likely to be more complex than just importing from a spreadsheet... Or am I better off just writing a script to enter it direct into the database using SQL?

[tli]

Importing into Assessment is relatively 'easy' as it involves just one table- NSTURESULTS. The events structure is a lot more complex so may well defeat the Data Transfer process

[PyROm]

Hi, the pdf is now linked off this knowledge base artice http://s3.parature.com/ics/support/KBAnswer.asp?questionID=1718&hitOffset=27+26&docID=4736

[MrGravell]

Thanks PyROm. Not sure how I actually access the document though - get a 'session expired' message. Not to worry though, I think I can get around all this crap by interacting with the database direct from PHP scripts. The database structure is a bit of a mess, but it's not totally indecipherable...

[PyROm]

Im guessing its a cookie problem, you need to add Serco firstline - Facility Support and s3.parature.com to your trusted sites list (allow non https sites). Ive done a lot of php scripts that read from the assessments section, im not brave enough to write back to it

[MrGravell]

HA HA HA HA HA!!!!!

 

Not laughing at you PyROm - am laughing at the prospect of adding serco firstline to my list of "trusted sites". As far as I can tell, firstline (which is by far and away the worst website I have EVER used) is storing my password as plain text in the database. Firstline in my list of trusted sites? I think not. It took about a week for them to sort my account out, and even now I don't have read permissions so I can't even read any forum posts. Utterly useless.

 

Why is everyone so paranoid about writing to the database? People at my school are even afraid of reading from the database. Obviously I'd try it on a test server first, and also take a backup when I tried it for real. But really, how hard can it be? Is the database that crazy? It might be...

[PyROm]

Yep, it is, the format of the nsturesults table is nasty. Rather then having a different entry for each result it has as many results as it can fit in one entry then moves onto a new entry when it hits the max, all space sperated in the form mapid result mapid result which looks like 50 B 75 7.2 63 B etc. I created a user with read only access to the database for my scripts as im only reading from it (makes sure I cant mess it up). Im just not brave enough to write back to it.

[MrGravell]

Fair enough. Events are, by comparison, relatively straightforward. Each event has one entry in APPAPPEVENT, and data for each field of each event is stored in APPAPPFIELDS. The only odd behaviour is that there's a lot of duplication of data in the two tables, and also that long comments get split into several entries in APPAPPFIELDS. But it's not terrible.

 

I can see why you don't want to touch NSTURESULTS!

[pcstru]

Why is everyone so paranoid about writing to the database? People at my school are even afraid of reading from the database. Obviously I'd try it on a test server first, and also take a backup when I tried it for real. But really, how hard can it be? Is the database that crazy? It might be...

 

Because if you stuff it up, it could cost you a lot of money. You may not notice problems at first. You might not even notice them until an update comes along that expected everything to be 'just so'. Then you have to resort to the company for support and they say ... that'll be £1200 a day please.

 

Even reading from the database can cause problems. Badly written SQL can be a game of "cripple Mr Database" and it can be easy to miss on a test server where transactional read consistency isn't in the presence of a consistent low level of writes from multiple sessions. Hell, sometimes the poor SQL will be supplied by the MIS developers!

 

My compromise is to read but never write.