Question: I'm working on a work project that requires the data of multiple excel files, which needed to be upload to the database. There is another file name "table_name" that contains the current columns data name, with the file name as the sheet name. I'm trying to code in python in order to change the columns name based on "table_name" Here is the table_name file that i want to change the name:  Sheet 1 name (chung):     Old New     chung1 new_chung1   chung2 new_chung2   chung3 new_chung3     Sheet 2 (nganh):     Old New     nganh1 new_nganh1   nganh2 new_nganh2   nganh3 new_nganh3    The files i want to change:  File 1 (phu_luc_chung):     chung1 chung2 chung3     1 2 3   4 5 6   7 8 9     File 2 (phu_luc_nganh):     nganh1 nganh2 nganh3     1 2 3   4 5 6   7 8 9    Here is the code chunk that i used to carry out the task: new_name = "table_name.xlsx" path = "E:\folder" # This code I'm trying to open every sheets that correspond to the data file name_n = pd.ExcelFile(new_name, engine='openpyxl') list = glob.glob(os.path.join(path, '*.xlsx')) # The code below is meant to get the "table_name" sheets as list sheets = name_n.sheet_names for file in list: # getting through each file for sh in sheets: # getting through each sheets df = pd.read_excel(file, engine='openpyxl') # the columns name has some uppercase so for easier replacement # I lowercase those names: df.columns = [col.lower() for col in df.columns] # In this part I'm trying to get the dictionary in "table_name" sheet # that correspond to the file in list that was called above: the_name = pd.read_excel(new_name, sheet_name = sh) renamee = dict(zip(the_name["Old"], the_name["New"])) new_df = df.rename(columns = renamee) #change the name according to the dict print(new_df.head(10))  Currently, the code could go through every sheets in the "table_name" and made it a dictionary with each sheet correspond to each file. But the code is not finding the right sheets meant for the specific files. How can I mark the sheets name and link it with the files name, and also some files have are in same categories like "phu_luc_chung1", "phu_luc_chung2", "phu_luc_chungdraft" (they are datasets of chung) due to terrible data management.

Answer: Still not 100% on what your doing but perhaps this will help; From your updated details seems the XLSX file "table_name.xlsx" has two sheets with the name changes; 'chung1' & 'nganh' and you want to apply the changes to all XLSX files in the List 'list' So perhaps what you want to do is create one dictionary 'renamee_dict' with all the name changes read from all the sheets in "table_name.xlsx". The changed code creates the 'renamee_dict' by reading all the Sheets in "table_name.xlsx" so it looks like; rename_dict = {'chung1': 'new_chung1', 'chung2': 'new_chung2', 'chung3': 'new_chung3', 'nganh1': 'new_nganh1', 'nganh2': 'new_nganh2', 'nganh3': 'new_nganh3'}  Then apply this dictionary to all the XLSX files in the List so whatever the Sheet Header name is the new name will be applied from the one dictionary if it applies. import glob import os import pandas as pd new_name = "table_name.xlsx" path = "E:\folder" # Read all the name changes from all the Sheets in new_name renamee_dict = {} sheets_dict = pd.read_excel(new_name, engine="openpyxl", sheet_name=None) for sheet_name, name_n in sheets_dict.items(): renamee_dict.update(dict(zip(name_n["Old"], name_n["New"]))) list = glob.glob(os.path.join(path, '*.xlsx')) for file in list: # getting through each file df = pd.read_excel(file, engine='openpyxl') # the columns name has some uppercase so for easier replacement # I lowercase those names: df.columns = [col.lower() for col in df.columns] new_df = df.rename(columns=renamee_dict) #change the name according to the dict print(new_df.head(10))