Combine files in the folder to one excel workbook with different sheets

Question

I would like to convert multiple csv file within the folder into xlsx file and then combine them into one single excel workbook with different sheets.

For example:

I have the following files in the folder:

asia (P) 2022-11-21_10_52.csv
as (P) 2022-11-21_10_52.csv
europe (P) 2022-11-21_10_53.csv
america (P) 2022-11-21_10_54.csv
.
.
.

I have already converted them into excel workbook and renamed the sheet by using

import pandas as pd
import os
from glob import glob

for csv_file in glob (r"Y:\files\2022 11 21\*.csv"):
    fname=os.path.split(csv_file)[1]
    print(fname)
    sname=fname.split(' (')[0]
    print('sheet name =', sname)
    df = pd.read_csv(csv_file)
    xlsx_file = os.path.splitext(csv_file)[0]+'.xlsx'
    df.to_excel(xlsx_file, sheet name=sname, index=None, header=True)

This will give me the outputs:

asia (P) 2022-11-21_10_52.csv
sheet name = asia
as (P) 2022-11-21_10_52.csv
sheet name = as
europe (P) 2022-11-21_10_53.csv
sheet name = europe
america (P) 2022-11-21_10_54.csv
sheet name = america
.
.
.

I then want to combine all the .xlsx files into one single workbook which contains all the sheets from the xlsx files in the folder. My folder should now contains:

asia (P) 2022-11-21_10_52.xlsx
as (P) 2022-11-21_10_52.xlsx
europe (P) 2022-11-21_10_53.xlsx
america (P) 2022-11-21_10_54.xlsx
.
.
.

I would like to have one new file call "Testing.xlsx" with all the excel files in the folder into different sheets(asia, as, europe, america etc.) I have tried

from pandas import ExcelWriter

writer = ExcelWriter(r"Y:\files\2022 11 21\Testing.xlsx", engine='xlsxwriter')

for xlsxfiles in glob (r"Y:\files\2022 11 21\*.xlsx"):
    excel_file = pd.ExcelFile(xlsxfiles)
    for sheet_name in xlsxfiles.sheet_names:
    df_excel = pd.read_excel(xlsxfiles, sheet_name=sheet_name)
    df_excel.to_excel(writer, sheet_name, index=False)
writer.close()

but this will give me error

for sheet_name in **xlsxfiles.sheet_names**:
AttributeError: 'str' object has no attribute'sheet_names'

I then tried

excellist=glob (r"Y:\files\2022 11 21\*.xlsx"):
for filename in excellist:
    excel_file = pd.ExcelFile(filename, engine='openpyxl')
    print(excel_file)
    excel_file.close()

But it gave me ValueError

excel_file = **pd.ExcelFile(filename)**

    ext = inspect_excel_format (
          content_or_path=path_or_buffer, storage_options=storage options)
    if ext is None:
        raise ValueError(
              "Excel file format cannot be determined, you must specify "an engine manually")
engine = confid.get_option(f"io.excel.{ext}.reader",silet=True)
if engine == "auto":

Value Error: Excel file format cannot be determined, you must specify an engine manually

Can you please help me on the above codes or do you have any other suggestions that I can put all the xlsx files into one single workbook with different sheets?

Combine files in the folder to one excel workbook with different sheets

0 Answers0