Move and rename Excel files in Python

Sharing is caring!

Last Updated on July 14, 2022 by Jay

In this short tutorial, we’ll learn how to move and rename Excel files in Python. This method works for not only Excel files, but also works for 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 the below screenshots for detail.

Instead of having 1 invoice file in each client folder, we want to move all the Excel files into one single 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:

C:\Users\JZ\Desktop\PythonInOffice\rename_excel_files_and_worksheets

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 os.mkdir()

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 int('10') or str(10).

for i in range(59):
    shutil.move(path+'\client_'+str(i+1)+'\invoice.xlsx', 
                weekly+'\client_'+str(i+1)+'_invoice.xlsx')

The 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))

Full code

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!