#!/usr/bin/env python
# coding: utf-8

# ## 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")


# In[ ]:





# In[ ]:





# In[ ]:





# In[ ]: