How to Combine CSV files with Python

In today’s email:

• Combining .csv files using code instead of copy/paste

TRIVIA

MAIN ARTICLE

Video Walkthrough (3 min)

Combining Multiple CSV Files into One Excel Workbook Using Python

Managing multiple CSV files can be tedious. Python makes it easy to combine them into one Excel workbook with just a few simple steps.

Prerequisites 

Before we begin, make sure you have Python installed.

Python is a free download here.

You’ll also need two libraries: pandas (for handling data) and openpyxl (to work with Excel files). Install them using this command:

pip install pandas openpyxl

Setting Up the Environment 

You can write and run Python code in an editor like Visual Studio Code or Jupyter Notebook. It's best to use a virtual environment to keep your project’s dependencies organized. Create the environment, activate it, and install the required libraries.

(let me know if you’d like to see more beginner level walkthroughs of setting up code environments.)

Step-by-Step Guide

Step 1: Importing Libraries 

We start by importing pandas, os, and openpyxl.

import pandas as pd

import os

- pandas handles the CSV and Excel files.

- os lets us list files in a folder.

Step 2: Setting File Paths 

Next, define the folder containing the CSV files and the path for the output Excel file:

csv_folder_path = "path_to_folder"
output_excel_path = "combined_output.xlsx"

Step 3: Iterating Over CSV Files 

We use os.listdir() to get all files in the folder. If a file ends with .csv, we read it into a pandas DataFrame.

for csv_file in os.listdir(csv_folder_path):
    if csv_file.endswith('.csv'):
        df = pd.read_csv(os.path.join(csv_folder_path, csv_file))

Step 4: Writing Data to Excel Sheets 

We use pd.ExcelWriter() to write the data from each CSV to a new sheet in an Excel file. The sheet name will be the name of the CSV file (without the .csv extension).

with pd.ExcelWriter(output_excel_path, engine='openpyxl') as writer:
    for csv_file in os.listdir(csv_folder_path):

        if csv_file.endswith('.csv'):

            df = pd.read_csv(os.path.join(csv_folder_path, csv_file))

            sheet_name = os.path.splitext(csv_file)[0]

            df.to_excel(writer, sheet_name=sheet_name, index=False)

Step 5: Saving the Combined Excel Workbook 

After all the data is written, the workbook is saved automatically when the with block finishes. No need to manually save it!

Example Code

Here’s the complete script to combine your CSV files into one Excel workbook:

import pandas as pd
import os

csv_folder_path = "path_to_folder"
output_excel_path = "combined_output.xlsx"

with pd.ExcelWriter(output_excel_path, engine='openpyxl') as writer:
    for csv_file in os.listdir(csv_folder_path):
        if csv_file.endswith('.csv'):
            df = pd.read_csv(os.path.join(csv_folder_path, csv_file))
            sheet_name = os.path.splitext(csv_file)[0]
            df.to_excel(writer, sheet_name=sheet_name, index=False)

print(f"All CSV files have been combined into {output_excel_path}")

How to Run

When you’re ready to run, you can save the file as a .py file (combine_csv.py), open the terminal/command prompt, navigate to the script location, and run this command:

python combine_csv.py

When to Use 

I have been meaning to figure this simple script out for a long time. It’s a fast way for me to combine files easily, and I’ve already put it to use a few times.

Hope it’s helpful for you too!

NEXT STEPS

Whenever you’re ready, here’s how I can help:

1. Business tech stack (FREE)
   My recommendations for software and tools I use to create content and run my online business.

2. Sponsor Got Sheet
   Got Sheet teaches business operators, teachers and entrepreneurs how to get good at spreadsheets. Interested in sponsoring an issue or a series of Got Sheet newsletters? Reach out to get more information.

3. Personal budget tool
   As a Got Sheet subscriber, I want you to have a 75% discount on the personal budget I built 10+ years ago and am still using to this day.

4. YouTube

   If you aren’t subscribed yet, come on over to my YouTube channel where I make all my spreadsheet, coding and productivity tutorials

HAPPY SPREADSHEETING!

Enjoyed this issue?
Subscribe below or Leave a testimonial

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.