Data migration

Administration
1

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

2

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


3

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

4

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

5

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

6

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

  1. Select all student enrolments in the current year using SQL (in this case it only gets Year 12 enrolments):
    1. 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’ ;
  2. Take the results, export them to a spreadsheet.
  3. Use find and replace to turn all instances of 12 in the course name into 13 (e.g. VAHL12 becomes VAHL13).
  4. Use a spreadsheet formula to create one SQL statement for each line in the spreadsheet, using subqueries if needed:
    1. =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’;”)
  5. Run all the new SQL statements on the database.
Hope this helps!

Data massage and import is a great skill to have…but never the most interesting part of  ICT!

Thanks,

Ross

7

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

8

Roman,

My pleasure…happy to help : )

Ross