How can I assign to PDF file names the names of students listed in Excel/Numbers?

Cool Workflows
1

Using my Mac, I’d like to assign to batches of PDFs (about 25 at a time) file names that contain the names of my students, and I’ve had no luck finding advice online about how to do this. The PDFs in question are grade sheets that I mark up on my iPad when reading students’ assignments. I’m wondering whether there is a way for me to, for example, type up my students’ names into an Excel or Numbers document and then run a workflow that’ll pull those names from the document and apply them to the grade-sheet PDFs so that I end up with 25 or so PDFs, each bearing different student names. I have Keyboard Maestro at my disposal, so if that would work in this situation, please let me know (David: consider adding a video about it to your upcoming Keyboard Maestro field guide–and dedicate to me, please). If Hazel would work, I’d be happy to purchase that program for this purpose. If Automator would work, please let me know and I’ll give that a shot.

If you think using Siri Shortcuts on my iPad would be a better route to go, please let me know that. (I recently purchased David’s field guide for that.)

Lastly, I should say that when I download my students’ assignments from my university’s learning management system, their names automatically get added by the LMS to the files. If there’s a way (I’m thinking Hazel) to run a workflow that gets those names from those files and assign them to the grade-sheet PDFs, that would save me the trouble of having to type up my students’ names into an Excel/Numbers document, but typing up those names once at the beginning of the semester isn’t much of a problem (actually, all I have to do is download from the LMS a .csv file that lists their names in separate cells–along with a bunch of other info in columns I can easily delete).

Thank you in advance for any help you can provide me with.

2

I think I would need a bit more specifics about the data you are working with to design the workflow in detail, but I do think I can give you some suggestions that might get you on the right track.

I’m assuming that you have a listing of student names, an arbitrary number of them, and that you have a template PDF that contains a grading sheet, and your goal is to have one copy of that PDF for each student, named something like “student-name-assignment-name.pdf”.

Assuming that is all true, I would basically use the concepts put forth by Rosemarie and David for the workflow to create multiple calendar entries in Fantastical from a Numbers spreadsheet, because you could download that workflow to see specifics of the implementation.

The basic idea would be to have a listing of the student names. That could be in Numbers, but you could also type them up in a plain text document as well, one name per line, in the format you need like “lastname.firstname” for instance.

I would select the listing of names and copy it to the clipboard.

Then, in Keyboard Maestro, you have a macro that iterates over each line in the clipboard (this is very easy to do, and again I refer you to the above-mentioned workflow for the template for this, or if you cannot find it, I can probably post screenshots.

Anyway, for each line in the clipboard, you then have KM create a duplicate of the template grading file and rename it using the lastname (which is in a variable from the iterate over lines in clipboard action) and a variable containing the assignment name.

To get the assignment name into the macro, you could just do a prompt for input action.

If the grading template PDF is a standard file, you just include the name of the file in the macro in the action to duplicate the file, but if it changes for each assignment, then you could also prompt for the file to use as the template.

KM has all the actions you need for this.

You could also do all of this in APpleScript if you are familiar with it, and reading a file line by line and doing the file duplication and renaming in AppleScript is quite easy, but I would just do it in KM - that’s what it’s for!

In terms of getting the listing of the student names, here’s an idea, once again based on assumptions about the data you have.

Let’s assume that you download for a given assignment one file for each student, and the name of the file is something standard, like their name in some format.

Just download all the files to a Hazel watched folder. Have Hazel for each file execute a script that appends the file name to a text file, which you then open, select all, and copy to the clipboard and then run the KM macro above.

One final thought:

If it is the case that for a given assignment you download one file from each student, and the files are named something convenient like “student-name-assignment-name.doc” (assuming they are Word documents as is typical in the academic environment), then what I would actually do is just have Hazel watch the download folder, and each time one of these files appears, just have Hazel run a script that duplicates the grades template file and renamed the duplication to the file name of the .doc file with -grades appending to the file name, and you are magically done!

Hope this is helpful, recognizing I made a lot of assumptions about what you are trying to do and the format of the data you have, but maybe this will give you a start.

3

This is absolutely excellent! Thank you so much! I’m gonna give this a shot sometime tomorrow, I hope, but definitely within the next few days. Again, thank you for the help.

4

I have to admit that I am a bit lost on what exactly you want to do for the first case. When all that you want to do is to rename batches of files, a program such as A Better Finder Rename will do wonders. Otherwise, what exactly do you mean by “assign to batches of PDFs file names that contain the names of my students”?

As for the case to import file names from your LMS to a spreadsheet, this is easy. You can export the gradebook as a CSV, use either Excel or Numbers to import the CSV, and then (as you say) delete the columns that you do not want. But then, what again do you mean by “assign them to the grade-sheet PDFs”? Perhaps you want to SPLIT the names to create one separate Worksheet for each student? Or do you mean create hyperlinks from the student’s name to a folder containing their work (all within the spreadsheet app)?

Maybe you can give a specific example here, perhaps using two or three student names (e.g. john doe, sally smith, mary roberts) and two or three assignments (e.g. homework 1, report 1, homework 2). How is this automation to work for this situation?

–
JJW

5

Hi f-dawg,

You said when you download your students’ assignments, their names “get added to the files.” - do you mean that the filenames of the downloaded files are the students’ names? E.g. “John Doe.pdf” etc.?

If so, try this:

  1. Put your grade sheet template file on the Desktop and name it “Template.pdf”
  2. Create a new folder on the Desktop and name it “Grade Sheets”
  3. Create the Keyboard Maestro macro that I’ve pasted in the screenshot below (I’ve assigned it to Control-Option-Command-G in this example)
  4. Go to the Finder and select all the downloaded assignment files (if they’re in a separate folder with no other files in there, just select one of them and hit Command-A to select them all)
  5. Now hit Control-Option-Command-G to trigger the Keyboard Maestro macro. It will extract the student names from the filenames of all the assignment files that you have selected and create corresponding grade sheet template files for them in the Grade Sheets folder.

If you prefer different file locations, filenames, etc., just tweak the macro to suit your needs. The macro is pretty self-explanatory (with the possible exception of the regular expression that I used).

I hope this helps you out :slightly_smiling_face:

KM%20Macro
KM Macro.png784×650 52.9 KB

6

Thank you for the response, JJW. I’ve received what appears to be really good advice from both nlippman and Arved at this point, so I’m going to see what I can do with their instructions.

7

Arved: thanks so much for your advice. I was just about to download the workflow nlippman had recommended and use it to guide my creation of a KM macro, but it appears you’ve provided me with something I can use a bit more quickly (and quickly is how I have to work till the end of the semester), so I’m going to try out your suggestion first. Again, thank you–and everyone else–for the help. Very much appreciated.

8

It worked! Took me a couple of minutes to realize that “/” is not created by hitting “Shift + v,” but once I got that figured out, things worked. One thing I’ll want to work on in the future is eliminating from the file name a string of numbers that my school’s LMS (Canvas) adds to each student’s submission (I believe it’s that string of numbers that enables me to batch re-upload the students’ submissions after I’ve downloaded them and modified their contents --by marking them up with my stylus). For example, when a student by the name of Joe Sample submits his assignment, the filename reads as follows:

smithjoe_32268_1425532_Paragraph assignment #2.pdf

The last part of the file name–“Paragraph assignment #2”–is the name the student gives to his submission. It would be nice to eliminate that too. If anyone happens to know, is it possible to modify the KM macro Arved created so as to eliminate everything that follows the first underscore ("_") and, while preserving that underscore, appending the following: “(PA2 grade sheet)”?

9

See screenshot below. Is this what you’re looking for? (I’ve drawn red boxes around the areas that have changed.)

Here’s the regular expression, so you don’t have to decipher it from the screenshot (sorry about that earlier :slightly_smiling_face:):

(?:.*\/)([^_]+)

Hope this works for you.

KM_Macro_2
KM_Macro_2.png788×690 58.6 KB

10

Hi f-dawg,

Sorry, I was in a rush earlier. I just looked at your post again and realized that I didn’t address the last part of your question.

But that’s easy: just take the macro above and add _(PA2 grade sheet) into the filename, so that the very last line of the macro reads like this:

~/Desktop/Grade Sheets/%Variable%filename%_(PA2 grade sheet).pdf

Everything else can stay the same as in my screenshot above.

11

Thank you, Arved. I’m gonna give this a shot tomorrow or the next day and will let you know how it goes.

12

You can also look into shell scripting.

A simple example on how to get a list (names) from a text file and creating copies of a template (both assumed to be on the Desktop):

cat ~/Desktop/names.txt | while read line; do cp ~/Desktop/template.pdf ~/Desktop/$line".pdf";done

This can be expaned to include text manipulation, variables, tec.

13

And to add to @Lars’ suggestion:

If you want to get the filenames into a text file, you can just select all your downloaded assignment files in the Finder, hit Command-C to copy, and then create a new text file (e.g. using TextEdit) and hit Command-Option-Shift-V to paste the filenames in there. This will give you just the filenames (without the extension), one per line.

(Don’t hit just Command-V in TextEdit, as that would paste previews of your PDF files in there. However, if you use a plain text editor like Byword or BBEdit, then you can just hit Command-V, and it will paste the filenames in there.)

Side note: if you Command-C the files in PathFinder (rather than the standard macOS Finder) and then paste into a text file, you will get not just the filename but the entire file path (and the extension, too). Not that you would want that, but just saying.

(P.S.: I don’t know why “filename” is one word for me, but “file path” isn’t. But that’s just the way it is, lol.)

14

Different ways to do that:

  • pipe ls to a text file or
  • use “for variable in *.pdf” to just run the command on every pdf file in the current directory

There’s a lot of power in the shell! :slight_smile:

1 Like