Reading & writing workbooks
File formats & conversion
.xlsx, .xlsm, .xls, .xlsb, .ods, CSV, JSON and Parquet — reading each and converting between them.
Start with the full guides
The long-form treatment of file formats & conversion, with the reasoning the shorter pages assume.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toConvert Excel Files to Parquet with PythonTurn slow, repeated Excel reads into fast columnar ones: convert workbooks to Parquet with pandas or Polars, keep types, partition by period, and refresh incrementally.
- How-toConvert Excel to CSV with PythonTurn .xlsx sheets into CSV without mangling dates, leading zeros or European decimals — one sheet, every sheet, or a streaming conversion for files too large to load.
- How-toAutomate LibreOffice Calc from Python with UNOUse LibreOffice as a scriptable spreadsheet engine on any server: start a headless soffice, connect through UNO from Python, open an .xlsx, edit cells, delete rows so that formulas update the way they do in Excel, recalculate, save as .xlsx and export PDF, then shut down cleanly — plus which Python can import uno and how to keep parallel jobs apart.
- How-toConvert an Excel Sheet to a LaTeX Table with PythonTurn a range from a workbook into a LaTeX table that compiles first time: read it with pandas, write it with to_latex or the Styler, escape the characters that break LaTeX (& % _ $ #), format numbers and percentages without letting a bare % comment out the row, add booktabs rules, a caption and a label, split long tables across pages, and check the result with pdflatex.
- How-toConvert an Excel Sheet to a Markdown Table with PythonTurn a worksheet into a Markdown table for a README, a wiki page, a pull request or a chat message. Use DataFrame.to_markdown, then fix what it gets wrong — unescaped pipes, line breaks inside cells, "nan" for blanks and raw float formatting — and convert every sheet of a workbook into one document.
- How-toConvert an Excel Sheet to an HTML Table with PythonTurn a worksheet into clean HTML for a web page, an email or an intranet report — pandas to_html, number and date formatting, Styler for highlights, captions and accessible headers, and several sheets on one page.
- How-toConvert an Excel Sheet to JSON with PythonTurn a worksheet into JSON: records versus columns, dates and NaN handling, nested output, every sheet at once, JSON Lines for large files, and validating the result.
- How-toConvert CSV Files to Excel with PythonTurn CSV exports into proper Excel workbooks — detect the encoding and delimiter, keep IDs as text, parse dates, combine many CSVs into one workbook with a sheet each, split files larger than Excel's row limit, and format the result.
- How-toConvert Excel Sheets to YAML or TOML ConfigTurn settings and lookup tables maintained in Excel into YAML or TOML configuration files with Python: a key-value settings sheet and table sheets read with openpyxl, Excel's floats, dates and booleans converted to the right types, missing values handled for TOML (which has no null), nested sections from dotted keys, and a round trip that proves the file says what the workbook says.
- How-toConvert Fixed-Width Text Files to Excel with pandasRead fixed-width exports from banks, payroll and mainframe systems with pandas.read_fwf and write them to Excel: column positions from the layout document, leading zeros kept, implied decimals, header, detail and trailer records in one file, record counts checked against the trailer, and the layout kept in a spreadsheet.
- How-toConvert Word Document Tables to Excel with PythonPull every table out of a .docx file into a workbook with python-docx and pandas: one sheet per table named after the heading above it, merged cells that python-docx repeats, line breaks inside cells, prices and percentages stored as text, a whole folder of documents, and old .doc files.
- How-toConvert .xls to .xlsx with PythonBatch-convert legacy .xls workbooks to .xlsx in Python — a values-only pandas converter, a formatting-preserving LibreOffice path, and the sheet-name and dtype traps.
- How-toConvert XML to Excel with pandas read_xmlTurn an XML export — orders, invoices, a feed from a legacy system — into a workbook with one sheet per record type. Use pd.read_xml with XPath and namespaces, keep nested line items linked to their parent record, preserve codes with leading zeros, and stream very large files with iterparse.
- How-toCreate Calendar Events from an Excel Sheet with PythonTurn a schedule kept in Excel — training sessions, deadlines, shifts, release dates — into an .ics file that Outlook, Google Calendar and Apple Calendar import: combine date and time columns, handle all-day rows with exclusive end dates, attach the right time zone, give each event a stable UID so re-imports update instead of duplicating, and split the file per person.
- How-toExtract Tables from a PDF into Excel with PythonGet tables out of PDF statements and reports and into a workbook with pdfplumber and pandas: ruled tables that span several pages, repeated header rows, amounts with thousands separators, day-first dates, borderless tables rebuilt from word positions, a control-total check and a formatted .xlsx.
- How-toInspect the XML Inside an .xlsx File with PythonAn .xlsx file is a ZIP of XML parts, and looking inside answers questions no library will: list the parts with zipfile, follow workbook.xml and its relationships to the right sheet file, decode cells and their t= types, resolve shared strings and number formats, pretty-print a part, and diff two workbooks part by part to see exactly what a tool changed.
- How-toRead and Write .ods Files with PythonHandle OpenDocument spreadsheets from Python with pandas and odfpy — reading sheets, writing output, the formatting limits, and converting .ods to .xlsx when you need more.
- How-toRead Apple Numbers Files in Pythonpandas cannot open .numbers files, but the numbers-parser package can: list the sheets and tables inside a Numbers document, turn a table into a DataFrame without the padding of empty rows and columns, read typed values and formatted text, handle several tables per sheet, convert a whole document to .xlsx with LibreOffice, and write a Numbers file back.
- How-toRead European CSV Exports with Semicolons and Decimal CommasCSV files saved by European Excel and ERP systems use semicolons between fields, commas as decimal points, dots as thousands separators and a Windows code page. Read them into pandas correctly — and avoid the thousands option silently turning dates like 31.01.2026 into the number 31012026.
- How-toRead Excel 2003 XML Spreadsheet FilesMany banking, ERP and web systems export "Excel" files that are really Excel 2003 XML Spreadsheet documents, often with an .xls extension, and pandas, xlrd and calamine all refuse them. Recognise the format from its first bytes, read every worksheet into pandas with a short ElementTree parser that respects skipped cells, skipped rows, merged cells and the Number, DateTime and Boolean types, and convert the result to a real .xlsx.
- How-toRead Strict Open XML Excel Files in PythonRead workbooks saved in Excel's "Strict Open XML Spreadsheet" format, which pandas, openpyxl and calamine reject with "Worksheet index 0 is invalid, 0 worksheets found" or "Cannot detect file format": recognise a Strict file, convert its namespaces to the usual transitional ones in memory so pandas can read it, convert with LibreOffice when that is not enough, and stop the files arriving.
- How-toRead .xls Files in Python with xlrd and pandasOpen legacy .xls workbooks from Python: install xlrd 2.x, read sheets into DataFrames, decode the date serials xlrd returns, and handle the 65,536-row ceiling.
- How-toRead Binary .xlsb Excel Files in PythonRead Excel Binary Workbooks (.xlsb) with pandas — the pyxlsb and calamine engines, why dates arrive as numbers with pyxlsb, how error cells look, reading sheet by sheet with pyxlsb directly, and converting .xlsb to .xlsx.
- How-toSave a Workbook as an Excel Template with PythonCreate a real .xltx template with openpyxl — styled headers, input cells, dropdowns, formulas and protection — by setting workbook.template = True, check the content type that makes Excel treat it as a template, and avoid the trap of loading an .xltx and saving it as .xlsx without switching the flag back, which produces a file Excel refuses to open.
- How-toWork with Macro-Enabled .xlsm Files in openpyxlEdit .xlsm workbooks from Python without destroying the VBA project: the keep_vba flag, why the filename matters, what openpyxl still drops, and how to verify macros survived.
- How-toWrite CSV Files That Open Correctly in ExcelA CSV that looks fine in a text editor can open in Excel with garbled accents, everything crammed into column A, IDs stripped of leading zeros and long numbers in scientific notation. Write it with a UTF-8 byte order mark, the separator and decimal mark the reader's Excel expects, ISO dates, protected identifiers and safe quoting — or decide that the file should really be an .xlsx.
- How-toExtract VBA Macro Code from an Excel FileRead the VBA inside .xlsm, .xlsb and .xls files without opening Excel: detect whether a workbook has macros at all, extract every module with oletools, save them as .bas and .cls files that can be diffed and reviewed in git, inventory the macros across a folder, and flag suspicious code before anyone enables it.
- How-toSave a Workbook in Another Format with win32comUse Excel itself, through pywin32, to convert workbooks between .xls, .xlsx, .xlsm, .xlsb, CSV, ODS and PDF — the FileFormat codes, SaveAs versus ExportAsFixedFormat, silent overwrites, per-sheet CSV export and folder batch conversion.
- How-toRead Excel Files Inside a ZIP Archive with PythonRead workbooks straight out of a .zip without unpacking it: list the members, open one with zipfile and pass it to pd.read_excel, loop over every workbook while skipping __MACOSX and lock-file junk, read archives downloaded into memory, handle password-protected ZIPs, and understand the errors pandas gives when handed the archive itself.
Compare the options
Which tool or approach to pick, and why.
- ComparisonExcel vs CSV vs Parquet for Python Data PipelinesExcel is a delivery format, not a storage format. Compare types, size and read speed, and use Parquet in the middle of the pipeline with .xlsx only at the end.
- ComparisonPick an Excel Engine for .xlsx, .xlsm, .xls, .xlsb and .odsread_excel dispatches to five different parsers. Map each extension to the engine that reads it, keep macros through an edit, and sniff files that arrive misnamed.
When it breaks
The errors this topic produces, with the cause explained rather than just the fix.
- FixFix Garbled Characters Like "Café" and "’" in Excel DataText that shows "Müller", "€100" or "’" was UTF-8 read as Windows-1252 somewhere upstream. Detect the affected cells, repair them with a one-line re-encode or with ftfy, avoid the repairs that damage correct text, recognise what cannot be recovered, and stop it happening again.
- FixFix "Excel File Format Cannot Be Determined" in pandasWhy pandas cannot pick an engine for your spreadsheet, how to name the engine explicitly, and how to handle streams, URLs and files with no extension at all.
- FixFix "Excel Cannot Open the File Because the File Format or Extension Is Not Valid"Excel refuses a file your Python script produced when its bytes do not match its .xlsx name: CSV or HTML saved under an Excel extension, a writer that was never closed, bytes mangled in text mode, or a macro workbook saved as .xlsx. Diagnose which one with a short checker and fix the writing code.
- FixFix "openpyxl does not support the old .xls file format"Why openpyxl refuses .xls files, how to read them with xlrd or calamine, how to convert a whole folder to .xlsx, and how to spot a mislabelled file before it fails.
- FixFix "Object of type Timestamp is not JSON serializable" When Exporting Excel Datajson.dumps fails on rows read from Excel because pandas hands you Timestamp, numpy int64 and NaN values that the json module does not understand — and NaN that it does write produces invalid JSON. Fix it with to_json and ISO dates, a small default= converter, or a clean conversion step, and check the output parses in strict JSON readers.
- FixFix UnicodeDecodeError When Reading CSV Files Saved by Excel"utf-8 codec can't decode byte 0xfc" or "byte 0xff in position 0" when reading a CSV someone saved from Excel means the file is not UTF-8: Excel's plain "CSV" format uses the Windows code page, "Unicode Text" is UTF-16, and "CSV UTF-8" adds a byte order mark. Identify which one you have, read it with the right encoding, and stop silent corruption from latin-1 and errors="replace".
- FixFix "XLRDError: Excel xlsx file; not supported" in Pythonxlrd 2.0 reads only legacy .xls files, so any code path that sends an .xlsx workbook to xlrd now fails. Find where xlrd is being chosen, switch that path to openpyxl, and port old xlrd-style code without pinning an outdated version.
Follow it through a track
Prefer an ordered route? These tracks cover file formats & conversion along the way, in the order that builds on itself.
Related topics
Subjects that keep turning up on the same pages as this one, most overlap first.
File formats & conversion is part of Reading & writing workbooks.