converting all the .xls to .xlsx

Question

Hello I am having an issue to convert all the .xls files to .xlsx. other challenge is each .xls file have multiple sheets and I have lot of files to convert. Can you some one help me with a solution

Answer 1

import glob
import pandas as pd
import os
from pandas import ExcelWriter

_list_of_xls_files = glob.glob(r'C:\Users\enter_your_pc_username_here\Documents\*xls')

for _xls_file in _list_of_xls_files:
    df = pd.read_excel(_xls_file,sheet_name = None)
    _list_of_tabs_inside_xls_file = df.keys()

    with ExcelWriter(str(_xls_file).replace('.xls','.xlsx')) as writer:

        for n, _sheet_name in enumerate(list_of_tabs_inside_xls_file):

            df[_sheet_name].to_excel(writer,'sheet%s' % n)

Source:

1 Using Pandas to pd.read_excel() for multiple worksheets of the same workbook