Sort files into folders by file name according to a spreadsheet of names

Question

I am a teacher and use Canvas-LMS for my course. The course is broken into smaller groups led by individual teaching assistants, who grade student assignments in their own section. Canvas only provides an option to download all student submissions to a given assignment. Downloaded submissions are automatically renamed in a single folder with the format lastName_firstNameRandomNumber_SubmissionTime.pdf.

I have another Excel spreadsheet of student's names and their group numbers.

I would like to make some kind of script that can:

1. compare the portion of the file name and the student name in the Excel sheet;
2. find the group number in the Excel sheet and prepend it to the file name group1_lastName_*.pdf;
3. sort the renamed submission files into folders such as group1 and group2.

Answer 1

There are three parts to this problem.

1. Generate a new list of filenames
2. Batch updating the existing filenames
3. Separating files into directory by groupname

MS Excel and a program like Advanced Renamer (AR) a "Batch file renaming utility for Windows" can take care of this. While I use AR based on familiarity, the solution can be adapted to any other similar tool.

Generating new filenames

1. AR will allow you to generate a list of existing filenames from your files.
2. In Excel that list can be transformed using a formula in order to generate your modified filename list.

Batch update the filenames

1. AR will allow you to rename your filenames using the newly created modified filename list.

Separate directories by groupname

1. Manually:
   You can sort by name in Windows Explorer (which would effectively sort by groupname based on your preferred naming) and manually move the files based on group name to sepperate folders.
2. Programatically:
   You could do it by script using AR

---

Sample Code

Excel Renaming Formula

=LET( _oldlist, A2:A200, _namelist, C2:C200, _grouplist, D2:D200,
      _name, BYROW(_oldlist, LAMBDA(r, SUBSTITUTE(LEFT(r, FIND("_", r, FIND("_",r)+1)-1),"_",", "))),
      _group, BYROW(_name, LAMBDA(n, INDEX(_grouplist, MATCH(n, _namelist, 0)))),
      "group_"&_group&"_"&_oldlist)

^{Excel Renaming Formula is based on the data table below.}

Old Filenames	New Filename	namelist	grouplist
Acevedo_Mike_8931_2022-11-08 6.10.pdf	group_9_Acevedo_Mike_8931_2022-11-08 6.10.pdf	Mathews, Lamont	10
Alexander_Kyra_7746_2022-02-01 23.10.pdf	group_1_Alexander_Kyra_7746_2022-02-01 23.10.pdf	Holt, Mary	7
Alvarez_Wyatt_2136_2022-04-30 19.10.pdf	group_2_Alvarez_Wyatt_2136_2022-04-30 19.10.pdf	Hampton, Jon	7

Advanced Renamer Script

function(index, item) { 
  var dir = item.name.match(/group_\d+/g);
  item.newPath = item.path + dir;
}

^{Advanced Renamer Script is based on the filename format: group_#_<lName>_<fName>_<rndCode>_<timestamp>.pdf}

---

Images

Generate Filenames (1 of 3)

Generate Filenames (2 of 3)

Generate Filenames (3 of 3)

Update Filenames (1 of 1)

Separate by Groupname (1 of 4)

Separate by Groupname (2 of 4)

Separate by Groupname (3 of 4)

Separate by Groupname (4 of 4)