Spreadsheets
This documentation provides guidance on the spreadsheet options and categorisation methods available. If you encounter any issues, refer to the troubleshooting sections in the relevant section.
Available Spreadsheets
There are two types of spreadsheets available for categorisation:
Macro-enabled spreadsheet (.xlsm) – This version includes a macro that assists in manual categorisation - see here for instructions
Non-macro-enabled spreadsheet (.xlsx) – This version does not include a macro but still allows manual categorisation. - see here for instructions
The spreadsheets are identical, however, the macro aids in manual categorisation by running code which makes multiple selection of deviation categories easier. It is highly recomended to use the macro enabled spreadsheet, but if your institution blocks the use of 'macro-enabled' spreadsheets, then you must use the 'non-macro-enabled spreadsheet'.
Once you have formatted the data and are ready to upload, you must export the file to a .csv format to upload into the application. See Exporting to a .csv format in Excel
Macros can pose security risks if they come from an untrusted source. Malicious macros can execute harmful commands on your computer. Make sure any macro enabled spreadsheets come from a trusted source. If someone sends this spreaadsheet to you, it may have been tampered with, so always make you use the version provided in this GitBook.
Spreadsheet structure
Each file contains a set of predefined headers, which structure the data for categorisation.
Header Name
Header Label
Requirement
SITEID
Study Site Identifier
Mandatory
STUDYID
Study Identifier
Mandatory
DVSPONDES
PD Descriptor Assigned by Sponsor
Mandatory
DVCAT
Category for Protocol Deviation
Non-mandatory
DVDECOD
Protocol Deviation Coded Term
Non-mandatory
DVTERM
Protocol Deviation Term
Non-mandatory (auto-populated
DVENTC
Event Date
Non-mandatory
DVENTCVIZ
What Event Date, DVENTC, Represents
Non-mandatory
Categorisation Options
There are multiple ways to categorise data:
Uncategorised Data – Data can remain uncategorised.
Manual Categorisation – Users manually assign categories.
AI Categorisation – The system automatically categorises uncategorised data during upload.
Mixed Approach – Users can mix manual and AI categorisation, leaving some data uncategorised for AI processing.
Uncategorised Data
Data can be left uncategorised if categorisation is not required. Uncategorised entries will remain unchanged unless AI categorisation is enabled during upload.
All data can be recategorised once uploaded. This allows uncategorised entries to be categorised after they are uploaded.
Manual Categorisation
Both spreadsheets support manual categorisation, with the macro-enabled version making the process easier. Users manually select DVCAT and DVDECOD values from dropdown lists. If the selected DVCAT is changed, the DVDECOD is updated accordingly.
Macro-Enabled Spreadsheet (.xlsm)
Instructions
If you have recently downloaded the file, you need to unblock the macro. You only need to do this once.
Enter Data:
Use the dropdowns to select a DVCAT. You can shift click to select multiple DVCATs.
The DVDECOD dropdown will populate based on the selected DVCAT.
Select a DVDECOD.
The DVTERM should populate automatically.
Add a new row for each additional entry.
Unblocking macros in Windows
Right-click the file in File Explorer.
Select Properties > General
Find "Security:" and select the Unblock checkbox and click on "Apply".
Non-Macro-Enabled Spreadsheet (.xlsx)
Instructions
Enter Data:
Use the DVCAT dropdown.
The DVDECOD dropdown will populate based on the selected DVCAT.
Select a DVDECOD.
The DVTERM should populate automatically.
Multi-Categorisation:
Separate multiple DVCATs with a semicolon (;).
This disables the DVDECOD dropdown.
Manually enter multiple DVDECODs, separating them with a semicolon (;).
If correctly formatted, the DVTERM field will auto-populate with each corresponding DVTERM, separated by a semicolon.
Troubleshooting
Ensure DVCATs are spelled correctly and separated properly.
Check DVDECODs against DVTERMs:
If correctly entered, DVTERMs will populate automatically, separated by a semicolon.
If there is an error, the DVTERM field will remain empty.
Reference Sheet2 to view all valid DVCATs, DVDECODs, and DVTERMs.
Do not modify Sheet2.
Note: On Sheet2, commas in DVDECODs are replaced with underscores. This is only for the macro enabled template.
AI Categorisation
AI categorisation is available through the web application when uploading data. If enabled, any uncategorised entries (those without DVCATs and DVDECODs) will be processed automatically to predict a categorisation.
How to Use AI Categorisation
Simply leave entries uncategorised.
The AI model will assign categories during the upload process.
Mixed Categorisation
Users can apply a combination of manual and AI categorisation:
Some entries can be manually categorised while others remain uncategorised for AI processing.
This approach is useful when manual categorisation is preferred for certain data but AI assistance is needed for the rest.
Invalid Categorisation
If a categorisation is invalid, the data will be uploaded as uncategorised. Categorisation is considered invalid when:
The DVCAT(s) is unrecognised (only valid DVCATs are considered).
The DVDECOD is unrecognised or does not belong to the assigned DVCAT.
In the case of multi-categorisation, all assigned DVCATs are first processed, rejecting the invalid ones. The DVDECODs are then verified against the list of valid DVCATs.
Exporting to a .csv format in Excel
To save the spreadsheet as a .csv file in Excel:
Ensure you are on Sheet 1.
File > Save As > CSV (Comma delimited) (*csv)
If you get a popup saying "The selected file type does not support workbooks that contain multiple sheets" click on OK.
Last updated