SIMS Bulk Import

[matt40k]

SIMS .net is currently the most popular MIS system used by schools across the UK. Historically it has not been possible to bulk import email addresses into SIMS .net. Capita has however released two patches giving the end user two separate options. One generates email addresses into SIMS .net based on the pupils admission number, which isn't ideal for most scenarios. The other patch allows you to import from a CSV file that is located on your D: drive. This is hard-coded into the patch an cannot be changed. This is less then ideal also.

 

This utility has been developed to over come these short comings and allows you to bulk import email addresses from a wide range of file types and from any location. It also does not require any patches to be applied. It is completely unofficial and is no way endorsed by Capita Children Services. This utility does however use the Capita SIMS .net Business Objects in order to interface with the SIMS .net database. This means it does not breach the Capita rules with regards to writing data to the SIMS .net database.

 

In additional to import email address the utility can bulk import UDF data. UDF stands for User Defined Fields and allows the end user to create data fields beyond that of the standard SIMS .net fields, for example you can store Windows Active Directory usernames in the SIMS .net database. This utility would then allow you to bulk import that information from a flat text file directly into the SIMS .net database.

 

I strongly believe that the MIS system should be the central repository for information in the School and hopefully this utility will be a step forward to achieve this.

 

http://simsbulkimport.codeplex.com/

 

Please also not that EduGeek does not endorse this product, I'm just stealing their forum

Edited November 21, 2012 by matt40k

[splattthecat]

Thanks for this Matt, we will take a look at it. I hate the idea of a patch importing a CSV file, as one of the Academies we support requested this patch from Capita and ran it when moving to InTouch, and the import crashed and left the database locked in single user mode, which we then dad to sort out.

 

This seems a much more sensible approach - perhaps you could sell it to Capita to replace the patch:D

[localzuk]

Wow, so you got your head around the nightmare documentation that is the BO API then?

[matt40k]

Indeed I have, would happy give it to Capita in exchange for more Capita pens

[vikpaw]

And it's okay to freely distribute this without infringing the rules?

I'd have thought your LA would take dibs on it and want to charge

[penfold_99]

And it's okay to freely distribute this without infringing the rules?

I'd have thought your LA would take dibs on it and want to charge

 

Given this writes back to sims, capita will be requiring a licence payment of about £50. Watch out that you don't get a large bill.

Good idea though.

[matt40k]

I asked Phil Neal if I could release it on SupportNet FOC and he said it was ok. I don't offer paid support and I don't charge, if I did, it'll be a license cost and my LA would want a cut, so I wouldn't make much to make it worth while. You have to accept the risk of using a tool that comes with "no" support - well no SLA or guarantees etc etc. I personally can't see the point of charging, Capita offer a solution, limited as it is and it can't really be automated - if it could be automated, then it might be worth it. I won't however being doing that as @rpwillis - SalamanderSoft already could\does do this and he's doing better then I could.

[dmiddlebrough]

We have been looking for something like this for years. Thanks, Matt.

 

Installation went smoothly, but when I request a licence I continually get the message "Error: updating database" - is there a problem?

[matt40k]

Don't know, will have a look tonight. Been thinking of removing the license stuff as it's pretty pointless.

[vikpaw]

If you get rid of the licencing hassle, i'll give it a go.

I'll be wanting to bulk import UDF data, with an extra caveat that the data will be unicode. Does that make a difference. I've got a patch on my system to allow unicode data (i can give you the sql for testing if required), so i can use arabic characters in UDF fields. Would be highly useful to bulk import the data directly, based on admission number if possible.

[matt40k]

The tool uses the same search as SIMS, you can match on admission number - no idea about unicode, I guess it might be possible?? I've attached it to this post because, well look over - http://www.edugeek.net/forums/mis-systems/100630-matt40k.html - anyway, it'll disappear from my website and fade from my memory.

 

If anyone wants the code go bug @PhilNeal for permission from a Capita licensing perspective, then I'll give you access to the (git) source code repository.

[stevething]

trying to have a go at using your program and cannot get any further than it asking for the server and db details.

 

have entered them as they should in the boxes and it keeps telling me to enter the server and database.

 

I dont think im doing anything wrong. Wondered if you could help.

 

Cheers

 

Steve

[matt40k]

Have a look at the logs and see what it says. I assume you've got SIMS working ok on that machine?

[stevething]

nothing comes up in the log file, all i do is enter the login details server name and db name, press login and it tells me to enter the server name and database name as if i havent entered them.

 

sims works fine on my machine.

 

does it make any difference that my pc is a windows 7 64bit machine?

[matt40k]

I've tested it on XP pro 32bit, Windows 7 32bit and 64bit, server 2003 r2 32bit and windows server 2008 r2 and it works fine.

 

If it's missing the servername and database some thing is wrong with that machine. Open sims.ini, check the SIMSApplicationDirectory path. In that path, check the connect.ini, ideally it doesn't want to have a redirect but just the server and database name.

Edited September 7, 2012 by matt40k

[stevething]

ah that might be it, we redirect our connect.ini, will give that a try

[limawhiskey]

Thanks for making and distributing the tool, it looks extremely useful.

 

I am having a problem when running an import of student email addresses. The result comes back as "Import failed" on a test of six users.

 

The CSV has fields Forename, Surname, Adno, UPN, username, email and I am choosing to match only the Admission No and email fields.

 

The output looks similar to the screenshot in the manual; the value for gender is repeated in the fields for Gender, Admission Number, Date of birth, Year and Registration, with "Import Failed" as the result.

 

The error repeated in the log is:

2012-09-14 09:47:03.4081|Error|System.NullReferenceException: Object reference not set to an instance of an object. at SIMS.Entities.EMail.ToXml(XmlDocument document)

at SIMS.Entities.EMailCollection.ToXml(XmlDocument document)

at SIMS.Entities.Communication.ToXml()

at SIMS.Processes.EditStudentBase.populateSaveParameters(IDbCommand cmd)

at SIMS.Processes.EditStudentBase.doSave()

at Matt40k.SIMSBulkImport.SIMSAPI.SetStudentEmail(Int32 personid, String emailaddr) in C:\Users\Matt\Documents\Visual Studio 2010\Projects\GingerBeard\SIMSBulkImport\SIMSAPI\Pupil.cs:line 655

 

Any thoughts on what might be wrong?

Edited September 14, 2012 by limawhiskey

[matt40k]

It sounds like it's connected to the database ok, it's picked the student up ok, however the email address field is null. Can you remove any records that has empty email addresses

[limawhiskey]

Thanks for the reply. It's just a small sample of six records to test the import. They all have email addresses.

[limawhiskey]

Oops. For future reference, don't forget to choose an option for Email Location.

 

Works quite well once you do

 

Great tool, thanks again Matt.

[gordonbarclay]

Hi Matt,

 

A fantastic tool. Recently I have been having problems and it fails, the following in in the log. Does this give a clue to what is wrong?

 

thanks

 

2012-11-15 10:01:16.6686|Info|User selected file for import: C:\Program Files\Matt Smith\SIMS Bulk Import Tool\sims.csv

2012-11-15 10:01:18.6373|Error|System.ArgumentOutOfRangeException: Index and length must refer to a location within the string.

Parameter name: length

at System.String.InternalSubStringWithChecks(Int32 startIndex, Int32 length, Boolean fAlwaysCopy)

at Matt40k.SIMSBulkImport.NumberAsText.fix(String toFix) in C:\Users\Matt\Documents\Visual Studio 2010\Projects\GingerBeard\SIMSBulkImport\NumberAsText.cs:line 76

at Matt40k.SIMSBulkImport.NumberAsText.Fixed(String file) in C:\Users\Matt\Documents\Visual Studio 2010\Projects\GingerBeard\SIMSBulkImport\NumberAsText.cs:line 47

2012-11-15 10:01:18.6373|Error|System.IO.IOException: The process cannot access the file 'C:\Documents and Settings\gdb\Local Settings\Temp\tmpE6.tmp' because it is being used by another process.

at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

at System.IO.File.Delete(String path)

at Matt40k.SIMSBulkImport.NumberAsText.Fixed(String file) in C:\Users\Matt\Documents\Visual Studio 2010\Projects\GingerBeard\SIMSBulkImport\NumberAsText.cs:line 56

at Matt40k.SIMSBulkImport.ImportFromFile.GetCSVFile(String pathName1, String fileName1) in C:\Users\Matt\Documents\Visual Studio 2010\Projects\GingerBeard\SIMSBulkImport\ImportFromFile.cs:line 141

2012-11-15 10:01:18.6373|Error|System.IndexOutOfRangeException: Cannot find table 0.

at System.Data.DataTableCollection.get_Item(Int32 index)

at Matt40k.SIMSBulkImport.Match.GetDataTable() in C:\Users\Matt\Documents\Visual Studio 2010\Projects\GingerBeard\SIMSBulkImport\Match.xaml.cs:line 200

[vikpaw]

It's odd that the error lines have location still refering to Matt's C drive.

The key one looks like the line where it says it cannot access a temporary file tmpE6.tmp for the gdb user, i guess that's you.

Is it definitely able to access the input file your referencing? Is it maybe open somewhere still?

[matt40k]

@gordonbarclay - does you file have just the email address in it?

 

It's odd that the error lines have location still refering to Matt's C drive.

 

Yer, enjoys of debug mode - gives me exact line it errored on, down side is you now know that I wrote it on a Win7\Vista\Win8 machine logged on as a user called Matt.

[gordonbarclay]

Managed to get it to work, not entirely sure what was wrong, I guess a strange character in the csv file

[jred]

Hi

 

Have used this to import email addresses previously and it worked great. I'm now trying to import serial numbers in to a UDF field and it comes up as import failed every time. If I import the email address at the same time, it brings in the email but fails on the UDF. Have tried almost every combination of fields for matching. Am I missing something really obvious?