Import Data from Moodle-Lists

If examinations are carried out in cooperation with the LET as online examinations, results lists can be exported from the examination Moodle ("Moodle's results export") and after processing for grade calculation be uploaded to eDoz using the function described here.

A step-by-step guide to the procedure, which includes both Moodle and eDoz, can be found protected pageat this link [ETH login required]. The following is an overview of what is specifically important when using the eDoz function.

Necessary prerequisites for using the function

A so-called Moodle examination course ("Prüfungskurs") for the respective semester and examination must have been set up beforehand in the teaching database. This is done in the application Sempro+ by employees of LET.
The scores of the exam were exported from Moodle to an Excel file (extension xls or xlsx).
From the scores of the Moodle examination a grade (i.e. a value between 1 and 6 in increments of 0.25) or, in case of a non-graded semester performance, a resultat (pass/fail, represented by the values 1 or 0) was calculated in the Excel sheet. For this purpose, a new column must be inserted that contains the grades/results. When calculating the grades/results all features of Excel can be used like e.g. additional sheets, formulas, external references etc.
The titles of the columns to be imported must be unique. Columns with empty titles cannot be selected for import.
The file name must follow a specific convention. Moodle creates this file name correctly upon export, as long as the name of the examination course in Moodle has not been changed in violation of the convention. The file name must start with the examination course number (format XXX-XXXX-XX[S or J]) followed by a space and the semester code (e.g. 2019W). This is followed by a hyphen and an arbitrary string (by default the name of the corresponding test activity in Moodle). Example: 376-0152-00S 2019W-Anatomy and Physiology II.xlsx. Attention! The leading part of the file name (i.e. in the example: 376-0152-00S 2019W-) must not be changed! Otherwise, eDoz will not be able to identify the correct examination course.

Choices to be made after reading the file

eDoz interprets the first row of the Excel list as column headings and reads them into an internal selection list which ignores empty fields. Furthermore, column titles that occur multiple times cannot be used for the import. If the first row contains data to be imported the checkbox "First row contains only column titles" must be deselected. (However, the assignment of the required fields must in this case be done on the basis of the values of the first row which then, of course, must be unique among all columns. We recommend strongly to have column titles in the first row.)
By default, Moodle's results export contains an average over all students in the last row of the list. If this row is missing, resulting in the list containing data to be imported in the last row, the checkbox "Last row contains average over all students" must be deselected.
It is compulsory to assign the relevant columns from the Excel file to the fields "Matriculation number" and "Grade" (or "Result" for non-graded semester performances) that are required by eDoz.
Optionally, you can also assign columns for "Dropout/no show" and "Grade trend" (the latter only for session examinations).

Valid entries

Matriculation number: either a number with up to eight digits and "leading zeros" omitted or text with hyphens in the format XX-XXX-XXX.
Grade: decimal value between 1 and 6, rounded to a quarter. Decimal marker is the point (comma will be changed automatically to point by import function).
Result: 0 (for "fail"); 1 (for "pass").
Grade trend: + or -
Dropout/no show: *
Empty fields for grade, result, grade trend and dropout are considered to be valid input ("no entry") and imported as such.
Suggestion: To prevent Excel from interpreting the + or - signs as part of a formula you can format the column as text or prefix it with an apostrophe.

Validation rules

If an input does not match the above validation rules, an error will be reported.
If for a given matriculation number on the Excel list no valid registration for the performance assessment can be found or a grade/result for the corresponding student is already contained in a completed list, an error will be reported. Hence, only data are imported that correspond to students who are displayed on the eDoz input mask.
If a list contains the same matriculation number in more than one row, an error will be reported.
If a row contains a grade/result and a dropout at the same time, an error is reported.
If a row contains a grade trend but no grade, the trend will be ignored. This means especially that if a dropout is entered together with a grade trend, only the dropout is imported.
Note: Upon import, any existing grades will be overwritten with valid new values, provided that entering grades/results is still possible for the student in question in the eDoz input mask. Empty values are regarded as valid input.

Differences to the general import for eDoz grades/results lists

Only Excel files are accepted, no CSV files. Save the lists directly in Excel format when exporting from Moodle.
Neither a hash code nor an ID column for each single examination registration are used. The sole identifier for a data record is the matriculation number. Nevertheless, only grades for students with a valid registration for the performance assessment can be imported.
The order of columns in the Moodle list can be altered arbitrarily.
The column titles can be chosen arbitrarily. However, only columns with a unique title can be imported.
The columns to be imported into eDoz are assigned by the user via the existing column titles.
In the column that contains the grades formulas and references can be used. eDoz always imports the last value displayed in Excel (this applies equally to Excel errors like #NAME?, #VALUE, # DIV/0! etc.).
For ungraded semester performances, the import of Moodle lists uses the following codes for the result: 1 for "passed"; 0 for "failed".
If the same matriculation number occurs in more than one row, neither value is accepted but errors are reported.

Further tips for editing the Excel file and using the import

Sorting of the file is possible as long as the consistency of the records remains guaranteed. (All columns must always be sorted together, never just a single column!)
We recommend that you either enter all grades manually in eDoz or exclusively work with export/import files. Otherwise, there is quite a risk that entries entered directly in eDoz are later accidentally overwritten by a file import.
If more than one person supplies grades/results to a performance assessment (e.g. in the case of exercise groups by several assistants), we recommend that only grades/results from their own students be delivered for each group. Otherwise, there is a risk that e.g. empty values overwrite already existing results for students of the other groups.
If the log of the data import contains errors due to inconsistent data, we recommend to check again all data records in order to make sure that there are no additional errors, like rows having been skipped or confused.
Do not use more than one browser tab or browser window at a time. "Tabbed Browsing" is not supported by eDoz and can lead to inconsistent data!