Converting a Huge Excel File to CSV or Parquet Safely

The workbook is the wrong place for a large dataset, and it often arrives as one anyway. Converting it to CSV or Parquet once makes everything afterwards easier: pandas reads it in seconds, DuckDB queries it in place, a database loads it in a bulk copy. The conversion itself is where care is needed, because a workbook mixes types, uses its own date system and may not fit in memory. This post shows three ways to convert, the choices that matter (types, dates, encoding, sheets) and the checks that prove the result. It is a spoke of Excel files that are too big or too slow.

Decide the target first

CSV is universal. Every tool reads it. It stores everything as text, so types are lost and must be restated by the reader, and it is large. Rules to fix in advance: encoding (UTF-8, without a byte order mark unless an old Excel will read it), delimiter, quoting and line endings. It is the right choice for exchange with systems that cannot read Parquet and for database bulk loads (loading a CSV file into PostgreSQL).

Parquet is a typed, columnar, compressed format. It keeps integers, decimals, dates and strings, reads selected columns fast and is a fraction of the size of CSV. It is the right choice for analysis in pandas, DuckDB, Spark and warehouses (converting CSV to Parquet).

Choose Parquet for your own analysis, CSV when the consumer needs it. Often you write both: Parquet as the working copy, CSV as the exchange copy.

Option 1: stream with openpyxl

openpyxl's read-only mode iterates a sheet without loading it:

import csv
from openpyxl import load_workbook

wb = load_workbook("big.xlsx", read_only=True, data_only=True)
for ws in wb.worksheets:
    out = f"{ws.title.replace('/', '_')}.csv"
    with open(out, "w", newline="", encoding="utf-8") as f:
        w = csv.writer(f)
        rows = 0
        for row in ws.iter_rows(values_only=True):
            w.writerow(["" if v is None else v for v in row])
            rows += 1
    print(ws.title, rows)
wb.close()

Each sheet becomes a file. newline="" stops the csv module doubling line endings on Windows. data_only=True writes the cached results of formulas. values_only=True yields plain values. The csv module quotes fields that contain the delimiter, quotes or line breaks. Dates come out as datetime objects, written in ISO form by str, which is usually what you want.

Two caveats. openpyxl in read-only mode may report the sheet's dimensions wrongly if the file was written by another tool, so do not rely on ws.max_row; count rows as you read. And rows can be ragged: read-only mode gives tuples of the length of the used range for each row, but trailing empty cells may be missing, so pad to the header length if you need a fixed number of columns.

For Parquet, write batches with pyarrow as shown in reading a 500 MB Excel file without running out of memory. Fix the schema before writing, or read everything as strings and cast later.

Option 2: DuckDB

DuckDB can read xlsx and write either format in one statement, streaming through the data:

INSTALL excel; LOAD excel;
SET memory_limit = '4GB';

COPY (SELECT * FROM read_xlsx('big.xlsx', sheet = 'Data', all_varchar = true))
TO 'data.parquet' (FORMAT parquet, COMPRESSION zstd);

COPY (SELECT * FROM read_xlsx('big.xlsx', sheet = 'Data'))
TO 'data.csv' (HEADER, DELIMITER ',');

all_varchar = true reads every column as text, which avoids wrong type guesses; cast later with TRY_CAST so failures are visible. Without it, DuckDB infers types from the data, which is convenient and can guess wrong when a column has numbers followed by text. The read_xlsx function and its options depend on the DuckDB version; check the documentation for yours. DuckDB is a good choice for a one-off conversion from a command line and for validating the result in SQL right away (loading Excel files with DuckDB).

Option 3: LibreOffice headless

For CSV, LibreOffice can convert without Python:

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

The filter options string sets the delimiter (44, comma), the quote (34), the encoding (76, UTF-8) and, with the final -1, writes every sheet to its own file (supported in recent versions). Without the last option only the first sheet is exported. LibreOffice loads the whole document into memory, so it is not a streaming tool: for a very large file it is slow and memory-hungry, and it is useful for modest sizes and for formats the other tools do not read. Check the exact filter option syntax in the LibreOffice documentation for your version.

Types and dates

Excel has numbers, text, booleans, errors and dates, where a date is a number with a date format. A converter that sees a number cannot tell a date from an amount without the cell's number format. openpyxl does this for you: it returns a datetime when the format is a date format. DuckDB and calamine do something similar. Problems arise with:

• The 1904 date system. Old Mac workbooks count from 1904, which shifts every date by about four years. openpyxl exposes the base in wb.epoch; check it (Mac and Windows date systems in Excel).

• Dates stored as text. A column of 2025-03-14 strings stays a string. Convert on purpose.

• Dates formatted as numbers, or numbers formatted as dates. A cell with a format of m/d/yyyy holding 42 comes out as 1900-02-11.

• Times and durations. Fractions of a day; check how your reader returns them.

• Large integers and identifiers. Excel keeps 15 significant digits. A 16-digit card number stored as a number was already rounded in the file (Excel number precision and the 15 digit limit). Read identifier columns as text, so leading zeros survive (keeping leading zeros in Excel).

• Error cells. Cached #N/A or #DIV/0! come through as strings in some readers. Decide whether to null them.

• Booleans become True/False, which a CSV writes as text; a database may expect t/f or 1/0.

Write down the type of every column before converting. For Parquet this is the schema; for CSV it goes into a data dictionary beside the file.

Encoding and CSV details

Write UTF-8. If an old version of Excel must open the CSV by double-click and shows accented text wrongly, a byte order mark helps, and other tools may then show it as a stray character in the first header (UTF-8 BOM in CSV files). Prefer a UTF-8 file without a BOM and open it through the import wizard.

Use a comma delimiter unless the consumer expects otherwise, quote fields with embedded delimiters or newlines (the csv module does it by default), and write ISO dates. Line endings: \n is fine for most tools; if the consumer is a Windows program that insists on \r\n, say so. Decimal points should be dots, not locale commas: a converter that formats numbers with the machine locale produces 1,5 in some environments, so write numbers with repr or the library's neutral output.

Multiple sheets and layout

A workbook with several sheets is several tables. Convert each to its own file named after the sheet, and keep a manifest of sheet name, file, rows and columns. Sheets that are not data (notes, charts, pivot output) should be skipped on purpose. If a sheet has a title block above the header, skip those rows (read from the header row) (skipping header junk when reading Excel). Merged cells arrive as a value in the first cell and blanks below, which must be filled down (unmerging cells and filling values down). Hidden rows are still in the file and will be exported; decide whether they are data.

Verify the conversion

Compare the result with the workbook:

1. Row count. The number of data rows in each output equals the workbook's. For the workbook, count while streaming, or in Excel use a count of the key column.

2. Column count and names. Header row identical.

3. A control total. The sum of a numeric column in the output equals the sum in Excel (=SUM(C:C)), with the same cached values.

4. Spot checks. Read ten rows from the middle and the end of the output and compare them with the sheet. A conversion that cuts off the last row is a common fault.

5. Types. For Parquet, print the schema. For CSV, load it back with a typed reader and look for failures.

SELECT COUNT(*) AS rows, SUM(amount) AS total, MIN(order_date), MAX(order_date)
FROM read_parquet('data.parquet');

Keep the numbers in a note with the date and the file name. The workbook may be deleted later, and the note is the proof the conversion was complete.

Do it once, and keep the recipe

Write the conversion as a script, with the paths and the schema in a config. Run it and archive the output together with the script and the checksum of the source (sha256sum big.xlsx). If the workbook is replaced by a corrected version, rerun and compare. A conversion done by hand in a GUI cannot be repeated or audited.

When converting is not the right move

The workbook is the deliverable. If readers need formulas, formatting and charts, a flat file loses them.

The data is small. A few thousand rows read directly in pandas.

The workbook changes daily. Converting each time is a pipeline; build it properly, or fix the source so it stops producing workbooks (watching a folder for new Excel files and importing them).

The layout is a report. A formatted report with subtotals is not a table; clean it first (cleaning Excel data before analysis).

See the Apache Parquet documentation and DuckDB's pages on Excel import and export. To check a small converted extract as JSON, use the CSV to JSON converter.