DEV Community

Jeremy K.
Jeremy K.

Posted on

Import Multiple CSVs into One Excel Workbook with Python

#python #api #programming

In daily data processing, you often need to combine a set of CSV files into a single Excel workbook—one worksheet per CSV—so they are easier to review and share. Python’s built-in csv module can read the data, but writing each file to a separate Excel sheet requires extra logic. With Free Spire.XLS for Python, you can load a CSV directly as a workbook object and copy its worksheet into a target workbook with very little code.

Prerequisites

Install the free library via pip. It supports Python 3.6+ on Windows, macOS, and Linux:

pip install Spire.Xls.Free
Enter fullscreen mode Exit fullscreen mode

Free Spire.XLS for Python does not require Microsoft Excel, so it works on servers and in headless environments.

Note that the free version limits .xls files to 5 worksheets per workbook and 200 rows per worksheet. These limits do not apply to .xlsx, so use .xlsx for larger or more numerous sheets.

Key Steps

  1. Create a target Workbook.
  2. For each CSV file, load it into a temporary Workbook with LoadFromFile().
  3. Copy its first worksheet into the target workbook using AddCopy().
  4. Rename the copied sheet safely.
  5. Save the target workbook as .xlsx.

AddCopy() copies both data and formatting. Passing WorksheetCopyType.CopyAll ensures the entire worksheet is preserved.

Complete Code

The following Python script merges all CSV files in a directory into one Excel workbook. It also handles invalid worksheet names and duplicate names automatically.

import os
import glob
import re
from spire.xls import *
from spire.xls.common import *

INVALID_SHEET_CHARS = re.compile(r'[\\/?*\[\]:]')

def safe_sheet_name(name, max_len=31):
    """Return a valid Excel worksheet name."""
    name = INVALID_SHEET_CHARS.sub('_', name)
    name = name[:max_len].strip()
    return name or "Sheet"

def unique_sheet_name(workbook, base_name):
    """Return a worksheet name that is unique in the workbook."""
    existing = {workbook.Worksheets[i].Name for i in range(workbook.Worksheets.Count)}
    name = safe_sheet_name(base_name)
    if name not in existing:
        return name

    i = 1
    while True:
        suffix = f"_{i}"
        candidate = safe_sheet_name(base_name[:31 - len(suffix)] + suffix)
        if candidate not in existing:
            return candidate
        i += 1

def merge_csv_to_excel(csv_dir, output_path, delimiter=","):
    csv_files = glob.glob(os.path.join(csv_dir, "*.csv"))
    if not csv_files:
        print("No CSV files found.")
        return

    target_book = Workbook()
    try:
        # Remove default worksheets so only imported sheets remain.
        while target_book.Worksheets.Count > 0:
            target_book.Worksheets.RemoveAt(0)

        for file_path in csv_files:
            file_name = os.path.splitext(os.path.basename(file_path))[0]

            source_book = Workbook()
            try:
                source_book.LoadFromFile(file_path, delimiter, 1, 1)
                source_sheet = source_book.Worksheets[0]

                new_name = unique_sheet_name(target_book, file_name)
                target_book.Worksheets.AddCopy(source_sheet, WorksheetCopyType.CopyAll)
                new_sheet = target_book.Worksheets[target_book.Worksheets.Count - 1]
                new_sheet.Name = new_name

                print(f"Imported: {file_name}")
            finally:
                source_book.Dispose()

        target_book.SaveToFile(output_path, FileFormat.Version2016)
        print(f"\nMerge completed: {output_path}")
    finally:
        target_book.Dispose()

if __name__ == "__main__":
    merge_csv_to_excel(
        csv_dir="./csv_files",
        output_path="./merged_output.xlsx",
        delimiter=","
    )
Enter fullscreen mode Exit fullscreen mode

Key Points

  1. LoadFromFile parameters

    LoadFromFile(fileName, separator, startRow, startColumn) lets you specify the delimiter and starting cell. For TSV files, pass "\t"; for semicolon-separated files, pass ";".

  2. Worksheet naming

    Excel worksheet names are limited to 31 characters and cannot contain \ / ? * [ ] :. The code above replaces invalid characters and truncates long names. It also ensures duplicate names do not occur.

  3. Default worksheets

    A new Workbook() normally contains three blank sheets. The code removes them before importing, so the final workbook contains only the imported sheets. If your library version errors when AddCopy is called on an empty collection, keep one temporary sheet, perform the first copy, then delete the temporary sheet.

  4. Non-UTF-8 CSVs

    If a CSV uses an encoding such as GBK, LoadFromFile may not parse it correctly. In that case, transcode the file to UTF-8 first (for example, with Python’s csv module) or check whether your library version supports specifying an encoding.

  5. Resource cleanup

    Source workbooks are disposed after each import, and the target workbook is disposed in a finally block to avoid file locks and memory leaks.

Conclusion

This script is well suited for lightweight automation where scattered CSV files need to be collected into a single Excel workbook, one sheet per file. Free Spire.XLS for Python runs without an Office installation and provides a concise CSV import API, so you do not need to read and write cells row by row.

Top comments (1)

Collapse
 
respect17 profile image
Kudzai Murimi •

The unique_sheet_name handling for the 31-char limit and duplicate names is the part people usually forget until a batch job dies on the third file with a weird name. Good call flagging the non-UTF-8 CSV case too, that one's easy to miss until a CJK filename shows up.