Recommended Free Tools
Use pandas: read each CSV into a DataFrame, then write them all through a single pd.ExcelWriter. Each file becomes its own sheet, or you can stack compatible files into one sheet first. The choice of layout matters more than the code, so this guide starts with the working script and then covers the decisions that make it reliable.
The short version: one sheet per CSV
This pattern reads every CSV in a folder and writes each one to its own worksheet in a new workbook. It is an illustrative pattern built from the documented pandas API, not a script benchmarked against your data.
from pathlib import Path
import pandas as pd
input_dir = Path("csv_files")
output_file = Path("combined.xlsx")
with pd.ExcelWriter(output_file) as writer:
for csv_path in sorted(input_dir.glob("*.csv")):
df = pd.read_csv(csv_path)
sheet_name = csv_path.stem[:31]
df.to_excel(writer, sheet_name=sheet_name, index=False)
What each part does:
with pd.ExcelWriter(...): the pandas documentation says the writer should be used as a context manager; otherwise you must callclose()to save and close any open file handles. Thewithblock saves the workbook when it ends.sorted(...):globdoes not promise a particular order, so sorting makes the sheet order predictable.[:31]: Excel limits worksheet names to 31 characters. Truncating avoids the most common failure, but it is not complete protection (see below).index=False: stops pandas from writing its row index as an extra first column.
You need an Excel-writing library installed. pandas uses xlsxwriter for .xlsx files when it is installed, and otherwise openpyxl. Install one with pip install pandas openpyxl (or xlsxwriter).
Decide the layout first
| Layout | Use it when | Trade-off |
|---|---|---|
| One sheet per CSV | Files are distinct tables, or have different columns | File identity is preserved; cross-file analysis needs extra work |
| One combined sheet | Files hold the same kind of records with compatible columns (for example, monthly exports of the same report) | Easy to filter, pivot and sum; you need to track which file each row came from |
If files have different schemas, keep them on separate sheets unless you deliberately align the columns and decide what the resulting blanks mean. Writing several files into one workbook does not reconcile their schemas for you.
#1 Best Overall
Stack compatible CSVs into one sheet
Read the files, concatenate the DataFrames, then write once. Adding a column with the source filename keeps the origin of each row traceable.
from pathlib import Path
import pandas as pd
frames = []
for csv_path in sorted(Path("csv_files").glob("*.csv")):
df = pd.read_csv(csv_path)
df["source_file"] = csv_path.name
frames.append(df)
combined = pd.concat(frames, ignore_index=True)
with pd.ExcelWriter("combined.xlsx") as writer:
combined.to_excel(writer, sheet_name="All data", index=False)
Check the result: pd.concat matches columns by name, so a column spelled differently in one file (Order ID versus order_id) becomes a separate column with blanks elsewhere. Compare df.columns across files, or rename columns before concatenating, if you expect uniform output. Note also that a single Excel sheet holds at most 1,048,576 rows; very large stacked data will not fit.
pandas can also place several DataFrames on one sheet using explicit positioning options such as startrow. That keeps tables visually separate on a single sheet, but concatenating is the better choice when you want one unified table.
Match the parsing to the actual files
Do not assume every source is comma-delimited UTF-8. pandas lets you configure the delimiter and notes that some multi-byte encodings need an explicit encoding to parse correctly. When files come from different systems, inspect delimiter, encoding, headers and column types, then pass options that fit.
df = pd.read_csv(csv_path, encoding="utf-8-sig") # UTF-8 with a byte-order mark
df = pd.read_csv(csv_path, sep=";") # semicolon-delimited
df = pd.read_csv(csv_path, dtype={"zip": str}) # keep leading zeros
Use each option only when it matches the file. utf-8-sig, for example, is a fix for files saved with a byte-order mark, not a universal setting. If sources are mixed, keep a small dictionary of per-file options rather than forcing one setting on all of them.
Make sheet names safe
Truncating to 31 characters handles length only. If filenames are uncontrolled, you also need to deal with duplicates (two long names that share the same first 31 characters) and characters Excel does not allow in sheet names: [ ] : * ? / . A defensive helper:
import re
def safe_sheet_name(name, used):
base = re.sub(r"[[]:*?/\]", "_", name)[:31] or "Sheet"
candidate, n = base, 1
while candidate.lower() in used:
suffix = f"_{n}"
candidate = base[:31 - len(suffix)] + suffix
n += 1
used.add(candidate.lower())
return candidate
Create used = set() before the loop and call safe_sheet_name(csv_path.stem, used) in place of the plain slice. Excel treats sheet names case-insensitively, which is why the helper compares lowercase.
Engines and existing workbooks
If your workflow depends on a particular engine, or you want identical behavior across machines, name it explicitly: pd.ExcelWriter(output_file, engine="openpyxl"). Make sure that package is installed.
To add sheets to a workbook that already exists, the pandas documentation’s append example uses mode="a" with engine="openpyxl":
with pd.ExcelWriter("report.xlsx", mode="a", engine="openpyxl",
if_sheet_exists="replace") as writer:
df.to_excel(writer, sheet_name="Latest", index=False)
In append mode, if_sheet_exists controls what happens when the sheet name is already taken; the documented choices include replacing the sheet or overlaying onto it. Both change the existing file, so for a clean deliverable write to a fresh output path instead. Keep a backup before modifying a workbook someone else relies on.
Images are a separate matter: the openpyxl tutorial notes that Pillow is required to include images in a workbook, which only matters if you extend the script beyond tabular data.
Quick Recap
Troubleshooting
- Garbled characters: the encoding does not match; try the encoding the source system actually uses.
- Everything in one column: the delimiter is not a comma; set
sep. - Leading zeros vanish or IDs turn into numbers: read those columns as strings with
dtype. - Error about sheet names: a name is too long, duplicated, or contains a forbidden character; use the helper above.
- Import error for the engine: install
openpyxlorxlsxwriter. - Empty or partial file: the writer was not closed. Use the
withblock, or callwriter.close().
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problems
