In this short guide, I’ll show you how to move and rename Excel files in Python. This method works for not just Excel file, but also any file format, .doc, .txt, .ppt – you name it.
A Hypothetical Example
Let’s start off by setting the stage for this task. Imagine that you manage a list of clients’ weekly invoice reports. Each week, your company’s invoicing system generates 1 report (in Excel) per client, saved in a folder named after the client’s name. See below screenshots for detail.
Instead of having 1 invoice file in each client folder, we want to move all the Excel files into one folder, let’s call this folder “week_1”. Due to the identical naming of the individual Excel files “invoice.xlsx”, we can’t just copy and paste them all into the same folder. Also, do you really want to copy/paste by hand for 59 times?
Here’s what we’ll do. We will move each of the “invoice.xlsx” file into a folder called “week_1”, then rename that Excel file by adding the corresponding client name. For example, client_1’s invoice file will be renamed to “client_1_invoice.xlsx”.
We’ll use Python’s
shutil library to achieve this task.
shutil is a standard Python library which comes with the Python installation, so you don’t need to install the library on your own.
Step 1: Set up variables and folders
import shutil path = r'C:\Users\JZ\Desktop\PythonInOffice\rename_excel_files_and_worksheets'
All the client folders are stored in this folder:
And I’m going to assign the folder location to a variable named “path”. Here, make sure to include an “r” in front of the actual folder location string to avoid the following error:
SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape
Having an “r” in front of a string makes it a “raw” string. It essentially let Python treat backslashes (\) as a normal character instead of an escape character.
Then, we’ll make another folder called “week_1”. We can use
os.path.exists() to check if a path exist, if not, we’ll create the new folder by calling
weekly = r'C:\Users\JZ\Desktop\PythonInOffice\rename_excel_files_and_worksheets\week_1' if not os.path.exists(weekly): os.mkdir(weekly)
Step 2: Move and rename Excel files in Python using shutil.move
Next, we are ready to move the Excel files. Since the folder names are well structured, we can use a simple loop, from client_1 to client_59. Note that because Python index starts from 0, thus the
i+1 below. Also, don’t forget to convert the integer values into string before you concatenate them together. You can convert between integers and strings easily using either
for i in range(59): shutil.move(path+'\client_'+str(i+1)+'\invoice.xlsx', weekly+'\client_'+str(i+1)+'_invoice.xlsx')
shutil.move() method can recursively move a file or directory to another location. Yes, recursive means that you can use it to move an entire folder including all the contents and files within the folder.
Step 3: Clean up the empty folders
Now, because we moved all the individuals files out of the client folders, the empty client folders are still on our computer. Let’s do some clean up by deleting all those empty folders.
for i in range(59): os.rmdir(path+'\client_'+str(i+1))
import os import shutil path = r'C:\Users\JZ\Desktop\PythonInOffice\rename_excel_files_and_worksheets' weekly = r'C:\Users\JZ\Desktop\PythonInOffice\rename_excel_files_and_worksheets\week_1' if not os.path.exists(weekly): os.mkdir(weekly) for i in range(59): shutil.move(path+'\client_'+str(i+1)+'\invoice.xlsx', weekly+'\client_'+str(i+1)+'_invoice.xlsx') os.rmdir(path+'\client_'+str(i+1))
With just 10 lines of code, we can achieve a lot, and save ourselves tons of time. Life is short, so start using Python, and save yourself from mindless and repetitive tasks!