Extracting data from SIMS
To work alongside SIMS, you can set up an automated daily process to update STEARsoft with the most up-to-date class lists, timetables and student and staff details. It might take an hour or so to set up, but once done it should give a good solution. Here's what to do:
Ideally you will set this up before running the STEARsoft Administrator settings wizard. Running the Wizard, and picking ODBC will then guide you through the ODBC Configuration steps within STEARsoft below and relate all other settings appropriately.
- Create a new account in SIMS so that this process doesn't interfere with an individual account. Perhaps call it stearsoft.
- In your new SIMS account create the reports detailed below.
- create a windows .bat file that will run 'CommandReporter' (a SIMS utility that will extract .csv files for the reports you created).
- Use Windows Task Scheduler to run your .bat file daily (eg at 2am).
- Add the Microsoft text driver (*.csv) to your System ODBC data sources.
- STEARsoft needs to be installed on this computer.
- Configure the ODBC table links within the Administrator section of STEARsoft.
- Configure a daily ODBC import within STEARsoft (eg to run at 3am).
In a bit more detail
The reports in SIMS will produce what STEARsoft will refer to as tables. Please refer to the content detail required for your SIMS reports that shows what your reports (up to 10 of them) must include, and what they can include. You might name your 10 reports as follows:
Your .bat file might look something like this:
CommandReporter /USER:stearsoft /PASSWORD:yourpassword /REPORT:"stearteachers" /OUTPUT:"c:\sims\teachers.csv"
CommandReporter /USER:stearsoft /PASSWORD:yourpassword /REPORT:"steartimetable" /OUTPUT:"c:\sims\timetable.csv"
CommandReporter /USER:stearsoft /PASSWORD:yourpassword /REPORT:"stearclass" /OUTPUT:"c:\sims\class.csv"
CommandReporter /USER:stearsoft /PASSWORD:yourpassword /REPORT:"stearclassstudent" /OUTPUT:"c:\sims\classstudent.csv"
CommandReporter /USER:stearsoft /PASSWORD:yourpassword /REPORT:"stearstudent" /OUTPUT:"c:\sims\student.csv"
CommandReporter /USER:stearsoft /PASSWORD:yourpassword /REPORT:"stearday" /OUTPUT:"c:\sims\day.csv"
CommandReporter /USER:stearsoft /PASSWORD:yourpassword /REPORT:"stearperiod" /OUTPUT:"c:\sims\period.csv"
CommandReporter /USER:stearsoft /PASSWORD:yourpassword /REPORT:"stearroom" /OUTPUT:"c:\sims\room.csv"
CommandReporter /USER:stearsoft /PASSWORD:yourpassword /REPORT:"stearhouse" /OUTPUT:"c:\sims\house.csv"
CommandReporter /USER:stearsoft /PASSWORD:yourpassword /REPORT:"stearform" /OUTPUT:"c:\sims\form.csv"
I have chosen a directory c:\sims\ to put all the output files into. You could choose something different, but you must put all files in the same directory which should otherwise be empty.
If the CommandReporter function isn't found, you may need something like the following at the start of your .bat file:
cd program files\sims\sims.net
It may also work better with a /QUIET option on each line. For example:
CommandReporter /USER:stearsoft /PASSWORD:yourpassword /QUIET /REPORT:"stearteachers" /OUTPUT:"c:\sims\teachers.csv"
You can use Windows Task Scheduler, but you may have an alternative favorite to run a daily task. Just search on the internet for 'windows task scheduler' for all sorts of help, if you're not familiar with doing this.
To Add the Microsoft text driver to your system:
If you need more detailed help in finding and opening 'Data Sources (ODBC)' on your version of windows, please remember the search box in windows and the internet which has lots of help (search for: odbc text driver).
- You will need to open Control Panel and then possibly 'Administrative tools'. Anyway, you need to find and open 'Data Sources (ODBC)'.
- Note that STEARsoft can only access System Data sources, so you must go to the System tab to add the ODBC driver.
- Click the 'Add...' button and pick the 'Microsoft Text Driver (*.txt; *.csv)' option and click 'Finish'.
- Choose a name (eg simsforstearsoft) and then 'Select Directory...' to pick our directory (c:\sims).
- We'll then need to go to 'Options>>'. Change the Extensions List to be just *.csv (rather than Default *.*)
- Then go into the 'Define Format...' screen. You should see the extracted csv files (you need to have successfully run the CommandReporter .bat file at least once).
- Click on each file one by one. For each file tick the 'Column Name Header' and 'Guess' button to check the data is being interpreted sensibly. If you get strange data at the STEARsoft end (or something not matching), this may be a place to come back to and check.
- Press all the necessary 'OK' buttons to accept your modifications and add this ODBC Driver.
To configure the ODBC links in STEARsoft: The easiest is to have done the above before running the Administrator Wizard. At this point you run the Wizard and choose the ODBC option. To delete what you've already done in STEARsoft and start again with the wizard:
The steps below may help with whichever method you choose, including adjusting the data you already have:
- Take a note of your customer ID and password found at the top of the Administrator page of STEARsoft. You'll need to re-enter these if you delete all your data.
- Go to the 'STEAR Administrator' program from the 'START' menu of Windows and click 'Stop server'.
- You can then delete (or rename) your STEARsoft data directory (by default found at C:\Program Files\STEARSoft\Reg\data).
- Then return to 'STEAR Administrator' and click 'Start server'.
- Open the web interface for STEARsoft and log into the Administrator section.
- Choose the 'ODBC import settings' option in the left panel. Your ODBC Microsoft Text Driver should appear in the drop-down list. Select it and click 'Next' (you can leave the username and password blank).
- On the next screen (ODBC table names), you just have to match the table names using the selection boxes. If you used the names suggested above, this should be easy. Ignore and scroll down past the 'Advanced flood-fill options' to click 'Next >'.
- This takes you to 'ODBC column names' where you match the column names with your exported data. Click the 'Finish' button at the bottom when you've picked them all.
- Using the left panel options, set up 'Days of the week', 'Times of the day', 'House names', 'Form names'. In each of these screens use the 'ODBC data' button to import from the ODBC source (your SIMS data).
Watch out in days of the week and times of the day that you don't produce duplicate days and times. It might be that you have to almost ignore what would be imported and hand-craft the ODBC_ID column using regular expressions (a form of matching). For example, if you are extracting a single field from SIMS that describes both the period and day (eg p5d3 for period 5 on Wednesday), you can use the expression .*d3 for the Wednesday setting and p5.* for Period 5 in your times of the day.
- Choose the 'ODBC: Import teacher accounts' option from the left panel. Tick the teachers to import (there's a catch all tick at the top)
- If you've set up and enabled the emailing option in STEARsoft, and you've imported teacher email addresses from SIMS, you then have an option to automatically send personalised welcome, account-activation emails to each teacher.
The daily import schedule in STEARsoft is set up from the 'ODBC: Import teacher classes' option in the Administrator left panel. Follow the prompts on that screen. Note that the percentage options allow you to guard against some disasters... For example, should a change (intended or unintended) in SIMS cause lots of classes to disappear or lose their students, STEARsoft will 'block' the import process if such a large change happens. What constitutes a 'large change' is defined by the percentage options. For example, if you have 1000 classes and you set the classes percentage to 1% then if the number of classes changes by more than 1% ie to above 1010 or below 990, then STEARsoft won't over-write the data it already has. No import will be done for anything - not even students or timetable records.
Although the first steps above are specific to SIMS, once you get to the ODBC and STEARsoft stages you may find the Generic ODBC tutorial useful as it includes lots of screen-shots to take you through some of the processes.
If you find any of the guidance here can be improved, or if you are stuck and would like help, please see the Forum topic for Importing data from SIMS.