[Python] Gathering and change multiple excel files columns based on another excel file with...

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 files that i want to change the name:

Old New
name1 new_name1
name2 new_name2
name3 new_name3

The files i want to change:

name1 name2 name3
1 2 3
4 5 6
7 8 9

Here is the code chunk that i used to carry out the task:

import glob
import os
import openpyxl
from collections import OrderedDict # ??? cần xem module này làm gì
from openpyxl import load_workbook
pd.set_option('display.max_rows', None)

def read_excel(path, new_name):
name_n = pd.read_excel(new_name, engine='openpyxl',
sheet_name = None,
engine = "openpyxl")
list = glob.glob(os.path.join(path, '*.xlsx'))
renamee = dict(zip(name_n["Old"], name_n["New"]))
for file in list:
df = pd.read_excel(file, engine='openpyxl')
df.columns = [col.lower() for col in df.columns]
new_df = df.rename(columns = renamee) # đổi tên cột cái file data với tên mới
print(new_df.head(100))


Currently this code can only do it one file at a time, but I want the code to run through every file in my folder and change according to the sheet in "table_name".

Continue reading...