CSV/Excel Handling Specifications

Goals

• Safely load tabular data without crashing.
• Detect and handle messy spreadsheets (multiple blocks, missing headers, merged cells artifacts).
• Produce reliable outputs (clean dataframe for clean table or structured JSON for messy spreadsheet) with validated types.

Encoding, Delimiters, and Locale

• CSV encoding: Try UTF-8; if garbled, attempt common fallbacks (e.g., gbk, cp1252) based on context.
• Delimiters: Detect common separators (,, \t, ;, |) during inspection.
• Locale formats: Be cautious with comma decimal separators and thousands separators.

Inspection (always first)

• Identify file type, encoding (CSV), and sheet names (Excel) before full reads.
• Prefer small reads to preview structure:
  • CSV: pd.read_csv(..., nrows=20); if uncertain delimiter: sep=None, engine="python" (small nrows only).
  • Excel: pd.ExcelFile(path).sheet_names, then pd.read_excel(..., sheet_name=..., nrows=20).
• Use df.head(n) and df.columns to check:
  • Missing/incorrect headers (e.g., columns are numeric 0..N-1)
  • "Unnamed: X" columns
  • Unexpected NaN/NaT, merged-cell artifacts
  • Multiple tables/blocks in one sheet (blank rows separating sections)

Preprocessing

• Treat as messy if any of the following is present:
  • Columns contain "Unnamed:" or mostly empty column names
  • Header row appears inside the data (first rows look like data + later row looks like header)
  • Multiple data blocks (large blank-row gaps, repeated header patterns)
  • Predominantly NaN/NaT in top rows/left columns
  • Notes/metadata blocks above/beside the table (titles, footnotes, merged header areas)
• If messy spreadsheets are detected:
  • First choice: use clean_messy_spreadsheet tool to extract key tables/fields and output JSON.
  • Only fall back to manual parsing if tool fails, returns empty/incorrect structure, or cannot locate the target table.

Querying

• Never load entire datasets blindly.
• Use minimal reads:
  • nrows, usecols, dtype (or partial dtype mapping), parse_dates only when necessary.
  • Sampling: skiprows with a step pattern for rough profiling when file is huge.
• For very large CSV:
  • Prefer chunksize iteration; aggregate/compute per chunk.
• For Excel:
  • Read only needed sheet_name, and consider narrowing usecols/nrows during exploration.

Data Quality & Type Validation

• After load/clean:
  • Validate types:
    • Numeric columns: coerce with pd.to_numeric(errors="coerce")
    • Datetime columns: pd.to_datetime(errors="coerce")
  • Report coercion fallout (how many became NaN/NaT).
  • Standardize missing values: treat empty strings/“N/A”/“null” consistently.

Best Practices

• Always inspect structure before processing.
• Handle encoding issues appropriately
• Keep reads minimal; expand only after confirming layout.
• Log decisions: chosen sheet, detected header row, dropped columns/rows, dtype conversions.
• Avoid silent data loss: when dropping/cleaning, summarize what changed.
• Validate data types after loading