Merge multiple CSV files into one Excel workbook

It is very easy to merge multiple CSV files in Python using Pandas library. This blog will explain to you all the parts of concatenating multiple CSV files and export them as one excel sheet.

Prerequisites for merging multiple CSV files using Python

1. Python 3.4 or higher
2. Pandas
3. Glob

Install Pandas

pip install pandas

Install Glob

pip install glob3

Now, simply make sure all the files that you are going to merge have the same number of columns and they fall in the same order. If not, use pandas to slice and rearrange columns before merging.

Python code snippet to combine multiple CSV files as one Excel

import pandas as pd
import os as os
import glob as gl

os.chdir(r"path_to_multiple_csv_directory")

Excel_File = pd.ExcelFile("new_excel_filename.xlsx")
Sheet_Name = Excel_File.sheet_names
length_of_Sheet = len(Excel_File.sheet_names)
print("List of sheets in you xlsx file :\n",Sheet_Name)

for i in range(0,length_of_Sheet):
    df = pd.read_excel("new_excel_filename.xlsx", sheet_name = i)
    df = df.iloc[:,0:3]
    df.to_csv(Sheet_Name[i]+".csv", index = False)
    print("Created :",Sheet_Name[i],".csv")
    
filenames = [i for i in gl.glob('*.{}'.format('csv'))]
combined_csv = pd.concat([pd.read_csv(f) for f in filenames ])
combined_csv.to_csv( "combined_csv_filename.csv", index=False, encoding='utf-8-sig')

Congratulations!! You may now have merged multiple CSV files as one excel using Python.