Change names of csv files in a folder

Question

There are 100s of CSV files in a folder which I want to rename. There is an excel sheet which contains name of files from a folder to be renamed.

Example:
Lets consider one CSV file called TestData_30April.csv, which is in a folder. I want it to be renamed to 0.25-TestData_30April.csv. The excel sheet in Column X contains the name to be renamed (0.25-TestData_30April.csv). Likewise the excel sheet contains names of all files in the folder to be renamed.

Here is the code:

import os

import xlrd

#Excel Sheet containing name of files to be renamed in that folder

path="C:\\Users\\Desktop\\Test_Data\\Test_Summary.xlsx"

wb = xlrd.open_workbook(path) 

sheet = wb.sheet_by_index(0)

sheet.cell_value(0, 0)

#In excel sheet column X or col_values(23) contains the file name to be renamed

print(sheet.col_values(23))  

#Below line contains all the csv sheets

os.rename('C:\\Users\\Desktop\\AllData',sheet.col_values(23)) 

I want the files in the folder to be renamed to the name in the excel sheet. How do I map data from an excel sheet to that folder so that the renaming takes place?

Answer 1

Iterate over all the values in the column. Remove the number prefix from the name to get the original name, then do the rename.

dir = r'C:\Users\Desktop\AllData'
new_names = sheet.col_values(23)
for new_name in sheet.col_values(23):
    if '-' in new_name:
        old_name = new_name.split("-")[1]
        if os.path.isfile(os.path.join(dir, old_name)):
            os.rename(os.path.join(dir, old_name), os.path.join(dir, new_name))