csvlookup

What

csvlookup is a utility that performs a batch lookup on any number of csv files referencing a central master list with all the required data, or WIP the other way round as well (similar to vlookup in Excel/Sheets).

It should be able to handle any number of template files, useful for coordinator to quickly churn out csvs for team members.

Why

Coordinators maintain a central document in Excel that keeps track of the cohort's results and data across all assessments in the two-year period. Each time there is an assessment there is a bunch of routine, repetitive actions that need to be taken. These are by no means difficult to do, but can be tedious and time-consuming.

Internal recording of marks

• individual tutors will take turns to update the central document in a local network folder
  • great risk of data corruption with many different users potentially messing things up
  • inefficient - bottleneck if one user forgets to close the file after he is done

or

• individual tutors fill up their respective marks according to a template, send to coordinator to cut-and-paste or vlookup to transfer to central document.
  • risk of error is reduced but not necessarily eliminated
  • labour-intensive, potentially time-consuming for the coordinator

Uploading of marks to Cockpit

• individual tutors must upload themselves according to assigned classes
  • some tutors are averse to 'new technology', or don't trust their own ability to work with the preparation and uploading of csv files
  • tutors can key in directly in Cockpit but this is time-consuming and again, risk of human error
  • different marking pace. Coordinator might have to rush to generate and push out the csv files with marks back to tutors for uploading.

We live in the 21st century

Why are we not automating some of these mundane routine tasks?

How

$ csvlookup path/to/central_results_list.csv path/to/templates_directory/

1. Place all downloaded templates from Cockpit into a single folder. Let's name this folder "templates" but it can be named anything.
2. Note the path to this folder, as well as the path to the central results list.

e.g.

$ csvlookup home/user/Documents/assessment12021_students_results.csv /home/user/Documents/assessment1/templates/

Constraints:

• All files must be in csv format. If the central results list is maintained in an Excel file, export (save-as) the relevant sheet for the assessment to csv format. Possibly a future version that enables working directly with Excel (see to-do)
• The final mark to be uploaded to Cockpit must be in the last column of the central results list csv. A future version can remove this constraint.
• Make sure that the names on the central results list match exactly as they appear on Cockpit. Order does not need to be the same. (similar to Excel vlookup).
• Currently assumes that Student Name used for matching is in column 3 in the downloaded template.

Limitations/to-do:

• Implement batch lookup from multiple input files to central results list.
• Error handling. Should output message to inform user if there are records that did not match.
• Add comments.
• Get user input to indicate the correct column to match instead of hard-coding it.
• Auto-detect the correct column to match instead of hard-coding it.
• Auto-detect OR option for user to indicate which column to find the final mark value.
• Make the matching logic more rigorous.
• Consider working directly with Excel files with the help of Excelize.