messy-xlsx 0.8.0

A Python library for parsing messy Excel files with intelligent structure detection and formula evaluation

messy-xlsx

Parse messy Excel files (XLSX, XLS, CSV) to clean pandas DataFrames with intelligent structure detection, merged cell handling, and type normalization.

Install

pip install messy-xlsx

# Optional: formula evaluation
pip install messy-xlsx[formulas]

# Optional: legacy .xls support
pip install messy-xlsx[xls]

# Everything
pip install messy-xlsx[all]

Quick Start

from messy_xlsx import MessyWorkbook, SheetConfig, read_excel

# Quick read
df = read_excel("data.xlsx")

# With options
df = read_excel("data.xlsx", sheet="Sheet1", skip_rows=2, normalize=False)

# Workbook API
with MessyWorkbook("data.xlsx") as wb:
    df = wb.to_dataframe(sheet="Sheet1")
    all_dfs = wb.to_dataframes()  # All sheets
    structure = wb.get_structure()

# From bytes (S3, cloud storage)
import io
wb = MessyWorkbook(io.BytesIO(content), filename="data.xlsx")

Configuration

from messy_xlsx import SheetConfig, MergeStrategy, HeaderDetectionMode

config = SheetConfig(
    # Row handling
    skip_rows=0,
    header_rows=1,
    skip_footer=0,
    cell_range=None,                       # "A1:F100"

    # Detection
    auto_detect=True,
    header_detection_mode="smart",         # or HeaderDetectionMode.SMART
    header_confidence_threshold=0.7,

    # Parsing
    merge_strategy="fill",                 # or MergeStrategy.FILL
    include_hidden=False,

    # Normalization
    normalize=True,
    normalize_dates=True,
    normalize_numbers=True,
    normalize_whitespace=True,
    sanitize_column_names=True,            # BigQuery-compatible names

    # Formulas
    evaluate_formulas=True,
)

wb = MessyWorkbook("data.xlsx", sheet_config=config)

All string-based config values accept both raw strings and enum types:

from messy_xlsx import MergeStrategy

# These are equivalent:
SheetConfig(merge_strategy="fill")
SheetConfig(merge_strategy=MergeStrategy.FILL)

# Enums compare equal to strings:
assert MergeStrategy.FILL == "fill"  # True

Invalid values raise ValueError at construction time:

SheetConfig(skip_rows=-1)              # ValueError
SheetConfig(merge_strategy="banana")   # ValueError

Multi-Sheet

from messy_xlsx import read_all_sheets, analyze_excel

# Read all sheets
results = read_all_sheets("data.xlsx")
for name, df in results.items():
    print(f"{name}: {len(df)} rows")

# Analyze without loading
info = analyze_excel("data.xlsx")
for sheet in info:
    print(f"{sheet.name}: {sheet.row_count} rows, {sheet.column_count} cols")

Output

Output is compatible with BigQuery/Arrow. Column names are sanitized by default and mixed-type columns are coerced to strings.

Dependencies

• Python >= 3.10
• fastexcel >= 0.11
• openpyxl >= 3.1
• pandas >= 2.0
• numpy >= 1.24

Optional:

• formulas, xlcalculator (formula evaluation)
• xlrd (XLS support)

Development

# Install with dev dependencies
make install

# Run tests, lint, type check
make ci

# Run benchmarks
make benchmark

# Serve documentation locally
make docs

License

MIT