Rename the name of files in a folder with the help of Python Script, using name map from Excel sheet

Question

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

The files in folder are named as

TestData_30April.csv

TestData_20April.csv

TestData_18April.csv etc

while the excel sheet contains the name as

0.25-TestData_30April

0.98-TestData_20April

0.33-TestData_20April etc

My Aim is to rename

"TestData_30April.csv" to "0.25-TestData_30April.csv" similarly for all other files as well.

Here is the Code that I wrote (which does not work as expected)

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"

#Folder Containg all orginal file names

dir = "C:\\Users\\Desktop\\Wear_Data"

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

list_of_filename_in_folder = [] # name of the files in the folder

list_of_filename_in_excel = [] #name of the files in excel

path_to_folder = ''  # base path of folder  

for name in list_of_filename_in_excel:

    excel_file_name = os.path.join(path_to_folder, name,'.csv')

    dir_file_name = os.path.join(path_to_folder,name.split('-')[1],'.csv' )

    if os.path.exists(dir_file_name):

      print('changing file name {} to {}'.format(name.split('-')[1],name))

      os.rename(dir_file_name, excel_file_name)

    else:

      print('no file {} with name found in location'.format(name.split('-')[1]+'.csv')

Answer 1

Edit2: as per excel file sample, try the following code:

import os 
with open('myfile.csv', encoding='utf-8') as f:
    buff = f.read()

buff = buff.splitlines()[1:]

for data in buff:
    data = data.split(',')
    old_name, new_name = data[21] + '.csv', data[23] + '.csv'

    if os.path.exists(old_name):
        print('changing file name {} to {}'.format(old_name,new_name))
        os.rename(old_name, new_name)
    else:
        print('no file {} with name found in location'.format(old_name))

old answer

Edit: the contents of file "myfile.csv" in below example are:

0.25-TestData_30April
0.98-TestData_20April
0.33-TestData_20April 

in case you have problem reading your file, first make sure it is an csv formated file and you can open it in any text editor, if you still have problem you can try to open it with encoding='utf-8':

with open('myfile.csv', , encoding='utf-8') as f:
        file_names = f.read().split()

you can try this simple code based on your excel file is a csv file

import os

with open('myfile.csv') as f:
    file_names = f.read().split()

for name in file_names:
    new_name = name + '.csv'
    old_name = name.split('-')[-1] + '.csv'
    if os.path.exists(old_name):
        print('changing file name {} to {}'.format(old_name,new_name))
        os.rename(old_name, new_name)
    else:
        print('no file {} with name found in location'.format(old_name))

Tested output:

changing file name TestData_30April.csv to 0.25-TestData_30April.csv
no file TestData_20April.csv with name found in location
no file TestData_20April.csv with name found in location