How to make multiple copies of a file with Python 3 and a file name list from an Excel sheet

Python 3, openPyXl, os,  shutil on Windows 10

The Problem

As an academic administrator, I have to prepare 70 empty grade report spreadsheets templates at the end of each academic quarter: one for each of my teachers. Each copy of the template sheet needs to be named with the teacher’s name and class number. Then the quarter, title and year is appended to the end. For example:

Stephen Hawking 404-23 Q3 Grades 2017.xlsx

The hard way would be to copy and paste a file click the file and rename it, repeating the process 70 error-prone and mind wastingly dull times.  I could also get the teachers to rename the file, but…they are teachers, not administrators so…yeah…errors again.

Python 3 to the rescue:

The Solution

Fortunately, I had a solution. I had a teacher’s list by sections in an Excel document called Teachers.xlsx. It sort of looked something like this:

Note:  I use LibreOffice for my offline work but it will be the same in Excel. Just a slightly different format. 

I would then use the names and class sections in each row to generate a copy of the Grade Sheet template file, GradeTemplate.xlsx   :

I will put the newly generated teacher grade sheets in a gradeSheets sub-directory that I also generate with our code.

Speaking of the code, let’s take a look. Then we will break it down.

The Code

Dependencies and Variables

On line 4, we import the OpenPyXL library so we can access the data in the Teachers.xlsx file. We’ll also need to import the os library to get file locations. Finally, we will use the shutil library to copy and rename new files quickly and efficiently.

Lines 9 and 10, use OpenPyXL to find our Teachers.xlsx workbook and determine which sheet (The tab down the bottom) we are working in – in our case ‘Sheet1’.

Line 13, grabs the template we want to copy and line 16 defines the folder name we will generate.

createFiles

After executing our file in line 61, we first need to find out what directory we are working in. For convenience. I have put my Python 3 code in the directory that I will be creating my sub-directories in. This will be a convenient location for me when I have to go and do this again next academic quarter.

To find the Current Working Directory – cwd, get it? – we call the os library with (Line 36):

os.getcwd()

This will call my file path:

C:\Users\yagisanatode\Documents\Programming\Projects\File Maker\

I want to then join this file path with the folder I want to create, ‘gradeSheets’, using my  folder variable in line 37. We use:

older_n_path = os.path.join(current_directory,folder)

Resulting in:

C:\Users\yagisanatode\Documents\Programming\Projects\File Maker\gradeSheets

Then in lines 39-44 we attempt to make the directory. If the directory already exists, then we will throw an exception with a warning to the user and stop the program.

Once our folder is ready and waiting to receive the teacher files we grab the teacher names and sections inside the Teachers.xlsx file with the copyRange function (More on this in a moment). The copyRange will generate a nested list inside the variable selectedRange:

Finally, on line 50 we start our loop to iterate through each row of the selectedRange variable we created from theTeachers.xlsx file and find the teachers name and their section.

Line 52 then joins the teacher’s name to their section and adds some more information about the file and wraps it all up in the string variable, file_name. i is the line we are on as the for loop iterates through the primary list. i[0] is the teacher’s name on that line and i[1] is the section.  So on the zeroth iteration file_name would look like this:

Angel Alcala 302-01 Q3 Grades 2017.xlsx

Now that our folder is created and we have generated our file name we can combine the two to make one file path on line 55.

Then on line 57 we call the shutil.copy module. This copy module takes at least two variables, the file to be copied and the destination/new file name:

shutil.copy(template, combined_file_path)

If you recall, back in line 13, template = 'GradeTemplate.xlsx'  and our combined file path would equal gradeSheets\Angel Alcala 302-01 Q3 Grades 2017.xlsx on the initial iteration of the for loop.

Finally, once the for loop has created each file for each row in our nested list we print a very successful “Done”.

copyRange

 

This copyRange function was something we created in an earlier project, so luckily we did not have to write it from scratch. You can find out more about it here:

Copy and paste ranges in excel with OpenPyXl and Python 3

copyRange takes 5 arguments, the starting column and row, the ending column and row, and the sheet name. Unlike normal numbering in programming where we start at zero, here we start our numbering at one. The end result generates a nested list of the data.

Let’s have a look at what we entered back in line 47:

selectedRange = copyRange(1,2,2,11,file_names_sheet)

We can see that we take column 1 row 2 as our starting range. We don’t need the headings in our string so we selected the second row. Then we grab the second column and the last row of data. Finally, the file_names_sheet variable provides the file and location of the information we want to grab – in our case file Teachers.xlsx, Sheet1.

The Results

I’ve added a couple of print statements in the code so we can see what we are doing along the way. This is what should show up on the shell:

2.9 seconds. Not too bad. And as you can see, the files have been created in the gradesSheets destination folder:

Conclusion

This should be a pretty easy program for you to modify for your own needs. Go ahead and try it on one of your own projects.

Want to learn how to automate your daily admin at work with Python? Udemy has some great Python automation courses that will help you learn how to automate your tasks so you can focus on what really matters.

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled professional to solve your problem quickly and cheaply. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.