How Do I Combine Multiple Csv Files Into One Excel Files With Different Sheets Using Python

I am trying to combine multiple csv files into one excel file whereby each file is it’s own sheet in the xls file. Below is a python script that can convert all csv files in a fo

Solution 1:

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 = 0for csv_file in glob.glob(os.path.join('../your_path/', '*.csv')):
    sheet_name = 'Sheet_' + str(counter)
    counter += 1
    worksheet = workbook.add_worksheet(sheet_name)
    withopen(csv_file, 'rt', encoding='utf8') as f:
        reader = csv.reader(f)
        for r, row inenumerate(reader):
            for c, col inenumerate(row):
                worksheet.write(r, c, col)
workbook.close()

Solution 2:

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

Solution 3:

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')
    withopen(csvfile, 'rt', encoding='utf8') as f:
        reader = csv.reader(f)
        for r, row inenumerate(reader):
            for c, col inenumerate(row):
                worksheet.write(r, c, col)
    workbook.close()

Solution 4:

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 namefor 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")