Converting Excel to CSV for Every Sheet in a Workbook

Excel can save a workbook as CSV, and anyone who has tried it with a workbook of five sheets knows the catch: it writes the sheet you are looking at, and says nothing about the other four. The people who need every sheet as a CSV are usually loading a database, feeding a pipeline or archiving data in a format that will outlast the application. A short script does it reliably and can be rerun. This post covers the export with pandas and openpyxl, the choices that decide whether the files are usable (encoding, dates, identifiers, names) and how to prove that every row made it. It is a spoke of converting Excel to CSV, JSON, XML and YAML.

What Excel does by default

File, Save As, CSV writes the active sheet only. Other sheets are not saved, and Excel shows a message that "some features might be lost" or that only the active sheet will be saved. There are several CSV variants in the Save As list: CSV (Comma delimited) uses the system's ANSI code page, which loses characters outside it; CSV UTF-8 (Comma delimited) writes UTF-8 with a byte order mark; Unicode Text writes tab-separated UTF-16. In locales where the list separator is a semicolon, the comma-delimited variant may write semicolons. Dates are written as displayed, in the cell's format, which may be a locale-specific one, and numbers as displayed too, so a cell showing 1,234.50 can arrive with the comma (Excel and the CSV comma and semicolon problem). For data exchange, these defaults are a minefield; a script with explicit choices is better.

Export with pandas

read_excel can read all sheets at once. With sheet_name=None it returns a dictionary from sheet name to DataFrame:

import pandas as pd
from pathlib import Path

src = "workbook.xlsx"
out = Path("csv_out")
out.mkdir(exist_ok=True)

sheets = pd.read_excel(src, sheet_name=None, dtype=str, keep_default_na=False)
for name, df in sheets.items():
    df.to_csv(out / f"{name}.csv", index=False, encoding="utf-8", lineterminator="\n")

Two arguments matter. dtype=str reads every column as text, so identifiers keep their zeros and numbers are written as stored, not reformatted. keep_default_na=False stops pandas from turning strings such as NA or null into missing values, which would then be written as empty fields; with it, an empty cell is an empty string and a cell containing NA stays NA. Use index=False so the DataFrame's row numbers are not written as an extra column. encoding="utf-8" is the default, and writing it down documents the intent.

Reading everything as text has a cost: dates stored as real dates become strings like 2025-03-14 00:00:00. If you want clean ISO dates, read normally and format the date columns:

sheets = pd.read_excel(src, sheet_name=None)
for name, df in sheets.items():
    for col in df.select_dtypes(include=["datetime64[ns]"]).columns:
        df[col] = df[col].dt.strftime("%Y-%m-%d")
    df.to_csv(out / f"{name}.csv", index=False, float_format="%.10g")

(Identifiers then need dtype={"id": str} per sheet; reading all sheets with one dtype argument applies it to every sheet, so decide per sheet if the sheets differ.) Reading as typed values risks the numbers being converted to floats, so 123 may be written as 123.0 when a column contains blanks; use dtype="Int64" for integer columns with blanks, or text. The right choice depends on what the CSV is for: a database load wants clean typed values, while an archive wants what the workbook held.

float_format controls the text of floating-point numbers. Without it, pandas writes the shortest representation that round-trips, which is usually right. Decimal points are dots, whatever the machine's locale, which is another reason to prefer the script over Save As.

Choose the sheets on purpose

A workbook may hold sheets that are not data: a cover page, notes, a chart sheet, a lookup table. Exporting them all produces files nobody wants. Decide which to export. With openpyxl you can see the state of each sheet:

from openpyxl import load_workbook

wb = load_workbook(src, read_only=True, data_only=True)
for ws in wb.worksheets:
    print(ws.title, ws.sheet_state, ws.max_row, ws.max_column)
wb.close()

sheet_state is visible, hidden or veryHidden. Hidden sheets often hold working data or lookups, and sometimes sensitive things. Skip them by default and export them only when you have decided to. Chart sheets have no cells and are listed apart from worksheets in openpyxl (wb.chartsheets). Filter by name pattern if the data sheets follow a convention.

Sheets can have a title block above the header, merged cells and subtotal rows. A CSV of a report-style sheet is a faithful copy of the mess; clean the layout first if the CSV is meant for loading (cleaning Excel data before analysis).

Safe file names

A sheet name becomes a file name, and sheet names may contain characters that are valid in Excel and awkward on disk: spaces, accents, slashes are not allowed in sheet names but other punctuation is. Two sheet names that differ only in case (Excel forbids that) or after sanitizing may collide. Write a function that produces safe, unique names:

import re

def safe(name, used):
    s = re.sub(r"[^\w\- ]+", "_", name).strip(" ._") or "sheet"
    s = s[:80]
    base, n = s.lower(), 2
    while s.lower() in used:
        s = f"{base[:76]}_{n}"
        n += 1
    used.add(s.lower())
    return s

Keep a mapping from sheet name to file name in the manifest. Number the files if the order of sheets matters (01_orders.csv), since a dictionary preserves the order of sheets but a directory listing sorts by name.

Large workbooks: stream with openpyxl

read_excel loads each sheet fully. For a large workbook, stream rows with openpyxl's read-only mode and write as you go:

import csv
from openpyxl import load_workbook

wb = load_workbook(src, read_only=True, data_only=True)
manifest = []
used = set()
for ws in wb.worksheets:
    if ws.sheet_state != "visible":
        continue
    fname = safe(ws.title, used) + ".csv"
    rows = 0
    with open(out / fname, "w", newline="", encoding="utf-8") as f:
        w = csv.writer(f)
        for row in ws.iter_rows(values_only=True):
            w.writerow(["" if v is None else v for v in row])
            rows += 1
    manifest.append((ws.title, fname, rows))
wb.close()

The csv module quotes fields containing the delimiter, quotes or line breaks. datetime values are written with str, which gives 2025-03-14 00:00:00; convert them if you want pure dates. data_only=True gives the cached results of formulas. Note that ws.iter_rows returns rows up to the used range, including trailing empty rows, so trim them. See converting a huge Excel file to CSV or Parquet for the large-file details.

LibreOffice, for all sheets at once

LibreOffice can export every sheet without Python:

soffice --headless --convert-to 'csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,false,false,false,-1' workbook.xlsx

The filter options give the comma delimiter (44), the double quote (34), UTF-8 (76) and, with the last token -1, one file per sheet named workbook-SheetName.csv in recent versions. The cell text is written as formatted, so dates and numbers follow the cells' formats; set the formats to ISO in the workbook first if that matters. Verify the options against the documentation for your version. LibreOffice loads the whole document, so it suits modest files.

Verify

Compare each CSV with its sheet.

1. Row count. The number of lines in the CSV, minus the header, equals the sheet's data rows. Count records, not lines: quoted fields can contain newlines.

2. Column count. Every row has the same number of fields.

3. A control total. The sum of a numeric column in the CSV equals Excel's.

4. Spot check. Open the first and last rows and a row with an accented name, a date and a leading-zero identifier.

5. Encoding. file -i name.csv reports charset=utf-8; the first bytes show no byte order mark unless you wanted one (UTF-8 BOM in CSV files).

Write the manifest as a CSV: sheet, file, rows, columns, sha256 of the file. It documents the conversion, and the hashes let you check that a file has not changed since.

Pitfalls

• Trailing empty rows and columns from formatting-only cells. Trim them.

• Formulas. CSV holds values. A workbook saved by a library without a calculation engine has no cached values; the CSV has empty fields.

• Number formats lost or kept. Reading values gives the real number; reading display text (Save As) gives formatted text. Choose the first for data.

• Percent and currency. 12% is 0.12 as a value. Say which you wrote.

• Booleans come out as True/False; databases may expect other forms.

• Line breaks inside cells are legal in CSV if quoted, and break naive line-based tools.

• Dates before 1900 or the 1904 system. Check the range.

When a CSV per sheet loses too much

The sheets are related. Foreign-key relationships between sheets are not in the CSVs; document them, or load into a database that holds them.

Formatting is the content. Colors, comments and merged cells do not survive.

Types matter. CSV carries no schema. Add a data dictionary, or use Parquet (converting CSV to Parquet).

Formulas are the point. Export values to CSV and keep the workbook for the logic.

See the pandas reference for to_csv and the Python documentation for the csv module. To check a small output as JSON, use the CSV to JSON converter.