Hi Ross,
Many less professional school administrators use a management tool to run their school, e.g. based on excel. To migrate data into gibbon is still a rather impossible task.
There are three common requests that I often hear:
- How do I import student exam marks?
- How do I import financial data, e.g. expenses?
- How do I import payments, e.g. for school fees?
Currently gibbon supports different data imports, e.g. for users, library, outcomes, families, etc. However, because many important imports are still missing, gibbon remains a no-go option for some school administrators.
Wouldn’t it make sense to come up with a more complete migration module? Is it the best way to handle migration at the module level as it is done currently?
Kind regards,
Roman
Roman,
Yes, this would make great sense. As always, it comes down to resources, of which we are perennially short. We are currently working through the v12 todo, but it looks like some might spill over into v13. As such, I am not sure we can commit to anything like this at the moment. However, as time permits, importing is one thing we do generally try and add into the system.
Thanks,
Ross
Hi Ross,
I’m slowly running out of time. My deadline to come up with a prototype is in early july. Therefore I want to tackle the import functions mentioned above.
Let me have your thoughts on it. I don’t want go into the wrong direction, since all code should ultimately become part of core gibbon.
Kind regards,
Roman
Roman,
That is unfortunate to hear. I will not likely be able to get these imports built before July. In this case, your options include hiring a programmer to implement these features (and hopefully submit them back to the Gibbon core), or using manual import based on spreadsheets and SQL to achieve the same aims.
I agree, such functionality should eventually be part of the core, but again, it comes down to limitations based on our resources.
Thanks,
Ross
Hi Ross,
Data migration is not really my field, but manual import based on spreadsheets and SQL sounds interesting.
Can you please be more specific? How do I undertake this? Are there specific tools your recommend to undertake this task?
Kind regards,
Roman
Roman,
So, you might take a spreadsheet of your current data, and then look at how its structure is different to the structure in Gibbon. You can massage the data, and use SQL sub queries, in order to get data into the right format.
As an example, before Gibbon had the ability to roll over student/class enrolment from one year to another
- Select all student enrolments in the current year using SQL (in this case it only gets Year 12 enrolments):
- SELECT gibbonCourseClassPerson.gibbonPersonID, gibbonCourse.nameShort AS course, gibbonCourseClass.nameShort AS class, role FROM gibbonCourseClassPerson JOIN gibbonCourseClass ON (gibbonCourseClassPerson.gibbonCourseClassID=gibbonCourseClass.gibbonCourseClassID) JOIN gibbonCourse ON (gibbonCourseClass.gibbonCourseID=gibbonCourse.gibbonCourseID) JOIN gibbonPerson ON (gibbonCourseClassPerson.gibbonPersonID=gibbonPerson.gibbonPersonID) WHERE gibbonSchoolYearID=20 AND gibbonCourse.nameShort LIKE ‘%12%’ AND gibbonCourse.nameShort NOT LIKE ‘%TUT%’ AND role=‘Student’ AND status=‘Full’ ;
- Take the results, export them to a spreadsheet.
- Use find and replace to turn all instances of 12 in the course name into 13 (e.g. VAHL12 becomes VAHL13).
- Use a spreadsheet formula to create one SQL statement for each line in the spreadsheet, using subqueries if needed:
- =CONCATENATE(“INSERT INTO gibbonCourseClassPerson SET gibbonPersonID='”,A1,“‘, gibbonCourseClassID=(SELECT gibbonCourseClassID FROM gibbonCourseClass JOIN gibbonCourse ON (gibbonCourseClass.gibbonCourseID=gibbonCourse.gibbonCourseID) WHERE gibbonCourse.nameShort=’”,B1,“’ AND gibbonCourseClass.nameShort='”,C1,“’ AND gibbonSchoolYearID=21), role=‘Student’;”)
- Run all the new SQL statements on the database.
Data massage and import is a great skill to have…but never the most interesting part of ICT!
Thanks,
Ross
Hi Ross,
I’m very grateful for you sharing this. I believe this info will help me tremendously with the upcoming tasks of migrating data into the prototype without having import functions ready available.
Kind regards,
Roman
Roman,
My pleasure…happy to help : )
Ross