How to issue certificates in bulk from a spreadsheet

When a spreadsheet is the right way in

Certificates for a graduating class, a workshop series or a cohort of an online academy all begin as a list that already exists: an enrolment export, a gradebook, a sign-in sheet. Retyping that list into a form invites exactly the errors a certificate cannot afford, because a printed name is final. Importing the list keeps the source and the certificate identical.

Emitcert accepts a class as a file saved as CSV, up to a thousand rows per file. A larger cohort is split into two files; the limit exists so that one file remains something a person can review.

Preparing the file

Start from the sample file offered on the import page, one per language, and keep its heading row. Headings may be written in English or in Spanish. Only two columns are required; every other cell that is left empty takes the design's default.

• name (required): the recipient as it should print on the certificate.
• course (required): the course, the programme or the degree.
• email: where the recipient's link is sent, if the option to notify is ticked.
• reference: the issuer's own key for the person, such as a student number. It makes the certificate searchable later.
• grade, hours, modality, started_on, completed_on, skills: optional details. Dates are written YYYY-MM-DD, hours as a whole number, and skills separated by a vertical bar.
• distinction: the name of one of the honours the issuer has defined, or empty. Our guide to Latin honours explains what each traditional distinction means.
• serial: left out, one is minted. A serial the issuer supplies must be its own and unique; see how to number certificates.

Saving the file correctly

The file must be text saved as UTF-8, which in most spreadsheet programs is the option named "CSV UTF-8". Names with accents, an ñ or a non-Latin script survive only in that encoding. Either a comma or a semicolon may separate the columns, so the regional setting of the spreadsheet program does not matter, and a leading byte-order mark is accepted.

The CSV format itself is defined in RFC 4180: a field that contains a comma, a double quote or a line break is enclosed in double quotes, and a double quote inside a field is written twice. A course called "Piano, Level 1" therefore needs its quotes, which a spreadsheet program adds automatically when it exports.

What is checked before anything is granted

The whole file is read first, and every row is checked: the required cells, the lengths, the e-mail addresses, the dates and each distinction against the honours the issuer has defined. Only when every row passes is anything granted. A file with a problem produces a list of the problems by line and column, and no certificate at all, never half a class.

That behaviour is deliberate. A class that is partly issued leaves the registrar to work out which graduates already hold a certificate, and a second upload would grant the first rows twice. A list of problems that can be corrected in the spreadsheet and uploaded again is a shorter path.

After the upload

Each recipient with an address receives an e-mail in the language of the design, carrying the link to their certificate and its verification page. Rows without an address are still granted, and the confirmation lists them with their link so that the institution can deliver those by other means. The issued certificates then appear in the certificate list, searchable by name, serial, reference or code, and the filtered list can be downloaded as CSV.

A checklist for the afternoon before

1. Export the list from the source system, not from a copy that someone edited.
2. Read the name column aloud against the register; this is the last inexpensive moment to find a misspelling.
3. Check the honours column against the issuer's list of honours, letter for letter.
4. Save as CSV UTF-8 and upload with the design chosen.
5. If the confirmation lists rows without an address, arrange their delivery before closing the task.