Step 1: Import the required libraries, simply run the cell below:¶

In [1]:

import os
import pandas as pd

Step 2: Replace the folder path and excel_file path below, then run the cell:¶

Remarks: In your excel, please make sure you have a column called 'Prefix' and a column called 'Student ID:'¶

This programme is written to rename the file to Student ID, if you want it to end with 'Student ID_xxx', please edit and uncomment the code for 'Situation 2'¶

In [6]:

# Path to the folder containing CV files
folder = r'C:\Users\hyyiu\Desktop\File Renaming\Automation\Demo\Test Files'
excel_file = r'C:\Users\hyyiu\Desktop\File Renaming\Automation\Demo\20230907.xlsx'

# Read the Excel table mapping Response ID to Student ID 
df = pd.read_excel(excel_file)  

# Loop through all the files in the folder
for filename in os.listdir(folder):

    response_id = filename[:5]

    # Check response ID before try  
    if not response_id.isdigit() or len(response_id) != 5:
        continue  

    try:
        # Check if the response id exists, then find the corresponding 'Student ID'
        # Then rename it to 'StudentID_CV'
        
        student_id = df[df['Prefix']==int(response_id)]['Student ID:'].item()
        file_name, file_ext = os.path.splitext(filename)
        ending = '_CV'
        new_filename = f'{student_id}{ending}{file_ext}'
        suffix = 1
        
        # If the same new file name already exist, 
        while os.path.exists(os.path.join(folder, new_filename)):
            
            # still rename the file but add the number of copy at the end as suffix
            new_filename = f'{student_id}{ending}_{suffix}{file_ext}'
            suffix += 1

        os.rename(os.path.join(folder, filename),  
                  os.path.join(folder, new_filename))
        
        # When it's renamed successfully, print the new file name
        print(new_filename)

    except:
        continue
    
print("Files renamed")

22104987D_CV.pdf
22096397D_CV.docx
23044382G_CV.pdf
23032006G_CV.pdf
22103907D_CV.pdf
21101187D_CV.pdf
21101187D__CV_1.pdf
20075229D_CV.docx
20073965D_CV.pdf
22074336D_CV.docx
22098637D_CV.pdf
19036316D_CV.docx
19068567D_CV.docx
21084884D_CV.docx
23070124G_CV.pdf
22009275D_CV.pdf
20042345D_CV.pdf
22022239D_CV.pdf
21102055D_CV.pdf
22098805D_CV.docx
22098805D_CV.pdf
20041233D_CV.pdf
20052091D_CV.pdf
20049519D_CV.pdf
23050538G_CV.pdf
23050538G__CV_1.pdf
21082142D_CV.pdf
22103631D_CV.pdf
23105366D_CV.docx
23105366D__CV_1.docx
22021569D_CV.pdf
23047062G_CV.pdf
23047062G__CV_1.pdf
23041856G_CV.pdf
21038246D_CV.pdf
21097494D_CV.pdf
21101431D_CV.pdf
23096108D_CV.pdf
21102665D_CV.docx
21102665D__CV_1.docx
19050892D_CV.pdf
22077626D_CV.pdf
23091036D_CV.pdf
23044918G_CV.pdf
23050614G_CV.docx
23058555G_CV.pdf
23106432G_CV.docx
23106432G_CV.pdf
23054756G_CV.pdf
23054756G__CV_1.pdf
22042663G_CV.pdf
22042663G__CV_1.pdf
22052717G_CV.pdf
23028505D_CV.docx
23028505D__CV_1.docx
23048602G_CV.pdf
23048602G__CV_1.pdf
23059248G_CV.pdf
23059248G__CV_1.pdf
23055371G_CV.docx
23055371G_CV.pdf
22007739G_CV.docx
23042563G_CV.pdf
23110293G_CV.pdf
20083994D_CV.pdf
20083994D__CV_1.pdf
23043127G_CV.pdf
23043127G__CV_1.pdf
23087723D_CV.docx
22067991D_CV.pdf
23026159G_CV.pdf
21096322D_CV.docx
21096322D__CV_1.docx
23093122D_CV.pdf
22056668G_CV.pdf
23045883G_CV.pdf
23045167G_CV.pdf
23045167G__CV_1.pdf
23103708G_CV.pdf
22021401D_CV.pdf
22096736D_CV.pdf
23044695G_CV.pdf
23044695G__CV_1.pdf
22101796D_CV.pdf
23051627G_CV.docx
230689043G_CV.docx
230689043G_CV.pdf
22082935D_CV.docx
22100753D_CV.pdf
21096497D_CV.pdf
21096497D__CV_1.pdf
Files renamed

In [ ]: