CSV format

There are two files, and they are not interchangeable.

• The configuration file — the certifications and their editions. Described below.
• The notification texts file — the wording of the five notices, one row per notice and language. Described at the end of this page.

The configuration file

Eighteen columns and two kinds of row: one row describes a certification, and the rows under it describe its editions. Every row carries only the columns of its own kind, and a value in a column belonging to the other kind is an error rather than something ignored.

All eighteen columns must be present, in this order.

Both kinds of row

Column	Description
record_type	certification or edition. The first column says what the row describes.
cert_id	Which certification the row is about. Either the certification's number, as every export writes it, or new:something for a certification this file is creating — see Creating a certification from a file. Anything else is an error.

A certification row

Column	Description
cert_name	Certification name. Required. If it differs from the name stored against that number, the file is refused unless renaming is allowed — see Renaming.
validity_period	Days a certificate stays valid after completion. Required, greater than 0.
due_days	Days to complete after enrolment before the status turns Overdue. Empty means 0.
warning_days	Days before expiry at which the status becomes Expiring. Decides the status only. Empty means 0.
days_before_expiry	The days on which this certification sends its expiry warning, as a list like 60,30,7. One email per value. Empty sends none.
days_after_due	The same, for the overdue reminder, counted after the completion deadline.
days_after_expiry	The same, for the expiry notice, counted after the expiration date.
hard_deadline	Annual deadline as DD/MM, or empty. The day must exist in every year.
manager_emails	Manager addresses, separated by commas, semicolons or spaces.
notices	Which of the five notices this certification sends, comma-separated: enrolled, completed, expiry_warning, overdue, expired. The column is authoritative, empty included: a row listing none switches all five off. A notice this site has no notification template for is refused: such a site cannot send it from here or from the certification page either.
is_active	1 active, 0 inactive. Required, and never left blank: an empty cell read as 0 would switch a certification off without saying so.

An edition row

Column	Description
edition_order	The position this course holds in the renewal sequence. Required.
course_shortname	The course's short name, which is how the course is identified. Required.
course_id	The Moodle course id, where the file gives one. A cross-check, not the key: if it names a different course than the short name does, the file is refused. That is what a file written on another site looks like.
chaining_method	manual, custom_field or idnumber. What the row means, and whether an import writes it — see below.
chaining_param	The value the recognition read off the course. Written by an export; ignored by an import.

How a course is named

The short name identifies the course, spelled in the file exactly as the site holds it — which is how an export writes it.

• A short name that resolves to nothing refuses the file, naming the line. It is not skipped: a typo passed over in silence would take the edition it describes with it.
• A short name the site holds with spaces around it names that course. Every cell is trimmed as it is read, so the file cannot carry those spaces, and a site holding such a short name would otherwise refuse the export it had just written. Two courses differing only by that padding name nothing and are reported as missing: no reading of the file says which was meant.
• A short name differing from the site's only in case refuses the file, and the refusal names the spelling the site holds. Resolving it would attach a certification to a course the file did not name.

What each chaining method means

chaining_method	Written by an import?	How the row is applied
manual	Yes	Writes the edition, exactly as the Editions page does
custom_field	Yes	Writes CERT_<cert_id>_<edition_order> into the course's Certification Edition custom field, and the edition follows from it
idnumber	No	Carried so that the file is a complete record of the site, and left alone. A course's ID number belongs to whoever administers the catalogue

A custom_field row needs the site to have that field. LOOP creates Certification Edition when it is installed and an administrator can delete it afterwards; a file declaring a custom_field edition on a site without it is refused, because the mark would have nowhere to go and the edition the row asks for would never appear.

A row chained by ID number that disagrees with what the course actually says is reported as read and not written, naming the course. Nothing about the course changes.

The other direction is reported too: a manual or custom_field row that puts a course at a position its own ID number contradicts is applied — both outrank the ID number — and the divergence is named. The ID number is left as it is, so if that edition is ever removed the next synchronisation reads the ID number and brings the old position back.

Rows, blocks and order

An export writes blocks: the certification row, and immediately under it its editions — manual ones by position, then the ones recognised from the courses themselves. Certifications come by name and then by number, so the same site exported twice produces the same file byte for byte, and two exports can be compared or kept under version control.

There are no blank rows between blocks. A blank row interrupts the range that a spreadsheet works out for itself when you sort or filter, and a file opened to be worked on is how this file is mostly opened. The certification row is the only row of a block with a name in it, which is separation enough to read by.

Nothing requires an import file to be in that order, and nothing requires a certification to carry edition rows at all.

A row with nothing in it is skipped, whether it is a blank line or a full row of separators — which is what a spreadsheet saves when its used range is wider than its data.

One position, one course

A course belongs to a certification at most once, and one position of the chain is held by one course. Two rows of the same certification claiming the same position, or naming the same course twice, are refused with both line numbers.

The rule holds against the site as well, and not only inside the file. A row claims the position it names, and an import frees what holds it: a manual edition is removed, and the mark of another course is taken off. Two holders it cannot free, and either refuses the file:

• a custom_field edition, where the row claiming the position is manual. The mark is the cause of that edition, and a manual row does not ask for the mark to move.
• an idnumber edition, whatever the row claiming the position is. That field is never written by an import, so nothing can be done about the holder.

In both cases the way to say what you mean is to give the holding course a row of its own — at another position, or at this one with another chaining method — and the position is freed as part of writing that row.

What holds a position is read off the marks on the courses as well as off the editions themselves, so a mark holds its position from the moment somebody writes it and not from the next synchronisation: a course marked an hour ago, with no edition row of its own yet, holds that position against the file just the same.

Creating a certification from a file

A certification that does not exist yet has no number for its editions to name, so the file gives it a temporary one: new: and a label of your choosing, used identically on its certification row and on its edition rows.

The prefix is not decoration. Without it, any value that is not a number — a 7 with a stray space, a letter O typed for a zero — would silently become a creation instead of an update.

A file that creates is not a file to apply twice

The label means nothing outside the file, and the certification it created has a number no row of that file carries. Applying it again creates a second certification: names are not identities, and there is nothing else to recognise it by. Export the configuration after the first run — from then on the rows carry numbers and the file can be applied as often as you like with no effect.

Renaming

A number that exists here with a different name in the file is ambiguous: either the file renames that certification, or it is pointing at one it did not mean. So it has to be said out loud. From the import page the rename appears in the list of what would change, and confirming the import says yes to it. From the command line it needs --allow-rename.

Delimiters

Comma, semicolon or tab. The delimiter is detected from the file, and can be forced from the upload form or with --delimiter on the command line.

The version of the format is its set of columns

There is no version column and no preamble line: the header row is the version. A file written for the previous format — seventeen columns, one row per certification-and-edition pair — is recognised for what it is and named as such, rather than measured against columns it was never written for. Export the configuration again and edit that file.

A header matching no known format is refused with the columns that were expected, in order. The reliable way to build an import file is to start from an export of the same site.

Example

record_type,cert_id,cert_name,validity_period,due_days,warning_days,days_before_expiry,days_after_due,days_after_expiry,hard_deadline,manager_emails,notices,is_active,edition_order,course_shortname,course_id,chaining_method,chaining_param
certification,7,Safety Induction,365,30,60,"60,30,7","0,15","1,30",31/03,safety@example.com,"enrolled,completed,expiry_warning,overdue,expired",1,,,,,
edition,7,,,,,,,,,,,,1,SAFETY-2026,412,manual,
edition,7,,,,,,,,,,,,2,SAFETY-2027,509,manual,
edition,7,,,,,,,,,,,,3,SAFETY-2028,610,custom_field,CERT_7_3
edition,7,,,,,,,,,,,,4,SAFETY-2029,711,idnumber,SAFETY-2029 CERT_7_4
certification,12,First Aid,730,45,90,"90,30",0,,,,"enrolled,completed",1,,,,,
edition,12,,,,,,,,,,,,1,FIRSTAID-A,77,manual,
certification,new:forklift,Forklift Licence,1095,60,90,"90,30",,,,,"enrolled,completed",1,,,,,
edition,new:forklift,,,,,,,,,,,,1,FORKLIFT-2026,812,manual,

What this file does not carry

• The wording of the notices: that is the other file. Which notices a certification sends is in this one, in the notices column.
• Anything about people: no status, no certificate, no enrolment. This file is the configuration.

The notification texts file

Four columns, one row per notice and language:

trigger_event,lang,subject,full_message
enrolled,en,Your {certificationname} training,"<p>Hello {firstname}…</p>"
enrolled,de,Ihre {certificationname}-Schulung,"<p>Hallo {firstname}…</p>"

Column	Description
trigger_event	One of enrolled, completed, expiry_warning, overdue, expired.
lang	A Moodle language code, for example en, de, it, en_us.
subject	The subject line. Placeholders are allowed.
full_message	The body, as HTML. Placeholders are allowed.

Rules:

• A language missing from the file is left as it is, so a translator can deliver one language at a time.
• To remove a translation, give its row an empty subject and an empty body. This is refused for the site's default language and for English.
• A row with a subject but no body, or a body but no subject, is refused.
• The same notice and language twice in one file is refused, naming both lines.
• A language your site has no pack for is accepted with a warning: the text is stored and used if the pack is added later.

The recipient, whether a notice is switched on, the calendar attachment and the send delay are not in this file and cannot be set from it.