Using an office suite to generate the sql for Koha’s calendar

An in-house technique we use for fast-track, accurate loading of the long list of Indian holidays into Koha.

LEGAL DISCLAIMAR: This tutorial is strictly meant for educational purpose. We are in no way responsible if you attempt to follow these steps and end up messing up your production Koha installation somehow.

Setting up yearly calendars is an important task in Koha. Especially in a country like India where we also use other calendars including lunar calendars. Since the religious festivals / holidays usually use these other calendars, the holidays often fall on different days in different years in the Gregorian calendar that Koha uses.

As support providers, we are often asked to setup the calendar for our client-partners. While Koha provides a rather useful user-interface to setup calendars under the Tools menu, we have often found that not only setting up the long list of Indian holidays a tedious affair, it is also quite prone to operator error. So for the Unique holidays we usually use a LibreOffice (a Free Software Office Suite) Calc spreadsheet to quickly generate the batch of SQL statements that can be directed imported into the special_holidays table of Koha using the SQL backend.

We ask our client-partners to send us a spreadsheet that lists the holidays and the days they fall on according to the Gregorian calendar (see the columns marked in yellow above). And then we use a couple of simple formulae to generate the necessary SQL. This approach has two advantages : (a) it’s very fast and (b) completely free of errors from our end.

In this present case, it took us less than 2 minute to upload all the 25 holidays for 2020 into this particular Koha instance.

Caveat The only risk here is that we are going to access the database directly from the command-line and that, unless you know what you are doing, can end up badly, *if* you mess things up.