How do I combine multiple CSV files into one excel files with different sheets using Python

The parameter sheet_name is constant. To export to multiple worksheets, you need to specify a different name for each worksheet. This is the tested solution:

workbook = Workbook('../your_path/joined.xlsx')
counter = 0
for csv_file in glob.glob(os.path.join('../your_path/', '*.csv')):
    sheet_name = 'Sheet_' + str(counter)
    counter += 1
    worksheet = workbook.add_worksheet(sheet_name)
    with open(csv_file, 'rt', encoding='utf8') as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader):
            for c, col in enumerate(row):
                worksheet.write(r, c, col)
workbook.close()

Create dataframe for each csv file and copy it in one excel as individual sheet.

Please refer below link.

https://xlsxwriter.readthedocs.io/example_pandas_multiple.html

You are creating discrete xlxs files by doing this;

for csvfile in glob.glob(os.path.join('.', '*.csv')):
    workbook = Workbook(csvfile[:-4] + '.xlsx')

Instead, you should create your file(Workbook object) outside of the for loop, just for once, and then create new sheet in the loop.

This should work

import os
import glob
import csv
from xlsxwriter.workbook import Workbook
"""with open('output.csv', "rt", encoding = 'UTF-8') as fin:
    with open('outputconvert.csv', "wt", encoding = 'UTF-8') as fout:
        for line in fin:
            fout.write(line.replace(';',','))"""

workbook = Workbook('name_your_file.xlsx')
for csvfile in glob.glob(os.path.join('.', '*.csv')):
    worksheet = workbook.add_worksheet('testws')
    with open(csvfile, 'rt', encoding='utf8') as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader):
            for c, col in enumerate(row):
                worksheet.write(r, c, col)
    workbook.close()

one can load the .csv files from a directory and combine them all into one .xlsx excel file using the following code:

import pandas as pd
import sys
import os
import glob
from pathlib import Path

extension = 'csv'
all_filenames = [i for i in glob.glob('*.{}'.format(extension))]

writer = pd.ExcelWriter('fc15.xlsx') # Arbitrary output name
for csvfilename in all_filenames:

    # in case your locale settings use , instead of a dot
    txt = Path(csvfilename).read_text()
    txt = txt.replace(',', '.')

    text_file = open(csvfilename, "w")
    text_file.write(txt)
    text_file.close()
    
    print("Loading "+ csvfilename)
    df= pd.read_csv(csvfilename,sep=';', encoding='utf-8')

    df.to_excel(writer,sheet_name=os.path.splitext(csvfilename)[0])
    print("done")
writer.save()
print("task completed")