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