Merge Multiple CSV Files with Python

by:

Data AnalysisPython

This tutorial teaches how to merge multiple CSV files into an Excel file using Python. This is a very tedious task when done manually, so hopefully, this tutorial is useful for you.

1. Merge multiple CSV files with Python

The code below merges multiple CSV files into an Excel file (xlsx format) with multiple sheets – one sheet per CSV. Line 4 defined the list of files that will be merged and Line 6 the names of the sheets associated with the files defined in Line 4.

The code below assumes that you already have pandas installed which can be done with PIP.

import pandas as pd

# create here a list of files you want to merge into the an Excel file
files = [# FILES TO MERGE]

sheet_names = [# SHEET NAMES]]


writer = pd.ExcelWriter('merged_file.xlsx', engine='xlsxwriter')

for i, file in enumerate(files):
    df = pd.read_csv(file, sep=",")
    
    df.to_excel(writer, sheet_name=str(sheet_names[i]))

writer.save()


2. Merge multiple TSV files with Python

If you want to merge multiple TSV files into one Excel file using Python, all you need to do is to use the code above and change line 12 from sep=”,” to sep=”\t”.

More Resources