Obtain all students enrollment reports in print to pdf files (not printed and re-scanned, if this happens it won’t work)
Place all pdf files in one directoru called Enrolment_Reports
Copy file pdf_2_csv.pl to same directory
run python pdf_2_csv.py
All files should now be in csv format
Check all files to make sure the commas are all in the right places. Sometimes the pdf table is not correct and this causes errors in the csv. It is better to fix any problems now than later.
If needed run sed -i ‘s/matching_pattern/replacement_pattern/g’ *.csv for any issues with the files.
Example of no result is:
AURTTC001,Inspect and service cooling systems,,,,10
Example of a result is:
AURETR029,Diagnose and repair charging systems,12/09/2019,1/04/2020,Competent,30
run ls *.csv | grep -Po '.*(?=\.)' > student_list.csv
Open student_list.csv and do a search/replace for newline and replace it with a comma, save the file.
Copy file csv_2_xls.pl to directory with csv files
run perl csv_2_xls.pl catch_ups.xlsx *.csv
Now there should be an excel file in the directory
Open the excel file with libreoffice and add a sheet to the front and call it summary
Go to the first student sheet and copy and paste all the units of competency in to column A, starting at A3
Place Student Name in cell A1
go to cell B1 and type = in the cell, then navigate to student_list tab and select the first name and then press enter.
Select the square in the bottom right hand corner of cell B1 when it is highlighted and drag it to the number of students required. When you run out of students, the number 0 will be present in the cell.
Go back to B3 and paste the following formula in to the cell
=IF(VLOOKUP($A3,INDIRECT(B$1&".A7:F42"),5,0)="","YTC",VLOOKUP($A3,INDIRECT(B$1&".A7:F42"),5,0))
Press enter and you should have a result in the cell. YTC will appear when there is no result recorded
As done with the names, grab the square in the highlighted cell with the result and drag horizontal to the last name and then vertically to last row.
If all the sheets are correct, you should have a full results table. If there are #N/A then you have a problem on the student sheet. You need to fix by cutting and pasting. Note: only column 1 and 5 are used from the student sheet. The other columns may be blank if not needed.