Import and export with Excel templates

Role: Compliance manager Five screens accept an Excel template: you download it, fill it in, import it. Nothing is written until you have seen the result row by row and confirmed. The same templates serve as an export: you download the current data in the same format, correct it and import it again.

Screenshot to come

Where and who#

The buttons appear only to whoever has the right. Both editions have access. The preview also works with an inactive licence; the write needs an active licence ("Your licence does not allow writing for now: the check works, the write does not").

Step by step#

1. On the screen, click "Import". The window offers "Download the blank template" and "Download the current data".
2. Open the .xlsx file in Excel. It has three sheets:
   • Instructions: how to proceed, the rules of the object and the list of columns (required ones are marked with a star);
   • Data: one row per object, under the headers. Do not change the first row;
   • Reference: the allowed values, the controls (identifier and title), the assets or the requirements, depending on the object.
3. Fill in the "Data" sheet. Columns with closed values are drop-down lists. People are named by their Jira e-mail address, dates are written YYYY-MM-DD, lists (controls, assets) are separated by a semicolon. Empty or example rows are ignored.
4. Back in the app, "Choose an .xlsx file". The app reads the file in your browser, then sends the rows to be checked.
5. The preview counts the rows "To create", "To change", "Unchanged" and "Refused". Each refused row is in a table (Row, Column, Reason). Nothing is written yet.
6. "Import N rows" writes the accepted rows. Refused rows are not written. At the end: "Import finished: N created, N changed, N refused".

Create or change: the "Identifier" column#

• Empty identifier: the row creates an object.
• Filled identifier: the row changes the object that carries that identifier. "Download the current data" gives the rows with their identifier.
• For controls and the statement of applicability, the first column names the item to fill in (the control's identifier, the requirement's code: see the "Reference" sheet); it is required.
• An empty cell on a row that changes something is an empty value, not "leave as is". Start from the current data rather than from a half-filled file.

Not creating twice#

Rows with no identifier get an identifier computed from the fingerprint of the file and the row number.

• Running the same file, untouched, again after an interruption creates nothing twice: what is already written is left as it is.
• A modified file, even by a single cell or merely re-saved by Excel, has another fingerprint: its rows with no identifier are created a second time.
• To correct refused rows, put them in a new file that holds only them, rather than reopening the first. To change what is already imported, start from "Download the current data": the rows carry their identifier.
• Two rows of one file that name the same object, or two identical creation rows, are refused: the first is kept.

What the import checks#

Each row is judged with the rules of the object's own screen, and written with the same log line as that screen. Among the reasons for refusal:

• a required value missing, text too long, value outside the list, number out of range, date not recognised, an answer other than Yes or No;
• nobody in Jira with that address, or several people for the same address;
• an unknown or malformed identifier, an unknown control or asset (or several assets with the same name: use the identifier);
• for a risk: "A mitigated risk relies on at least one control", a level outside the installation's matrix, a residual risk above the inherent one, a residual risk above the appetite without acceptance;
• "The owner of a risk does not accept it themselves": accepting a risk is not imported. It is a person's decision, made in the Risks screen;
• for the statement: "The statement is approved: start a new revision before importing";
• "Changed meanwhile by someone else: run it again".

A requirement's implementation state comes from the controls: it is not imported. The "How we implement it" text of a control is not a column of the template: it is written in the control's drawer.

People#

A person is named by their Jira e-mail address. The app looks them up in the Jira directory with your rights: your account must have the "Browse users and groups" permission. Otherwise the preview says so, and the rows concerned stay without an owner ("Owner not found"). On export, a person is written as their e-mail when Jira shows it, as their account identifier otherwise.

Limits#

• 5 MB and 2,000 rows at most per file. Beyond that: "The file is larger than 5 MB" or "The file has more than 2000 rows (N). Split it into several files."
• An Excel .xlsx workbook only. The file is refused if it is too large once unpacked or if it contains declarations that are not read.
• Formulas are never calculated by the app: a formula cell is worth the result Excel stored with it, and a text that starts with = stays a text.
• Columns the template does not have are ignored, and the preview names them. Headers are recognised regardless of case and accents, in French or English; the template is written in the language of the screen.
• Rows are checked in packets of 100 and written in packets of 50. If the write stops (time-out, a service that does not answer), the app says so ("The import stopped with N rows not written"): run the same file again to write the rest.
• The assets export contains only the light-register assets that have a classification.

What the log keeps#

One "Import by template completed" line per file and per result: the object, the number of rows created, changed and refused, and the start of the file's fingerprint. The content of the rows is not in it; each object written leaves its own log line, as if it had been entered on screen.

What the app does not do#

• It deletes nothing by import: a row missing from the file is not removed.
• It imports no objects other than these five (not policies, incidents, people or evidence).
• It reads neither CSV nor any other file format.