• Overview
• Core Philosophy: Scout Pattern
• When to Use This Skill
• Processing Workflow
• Complexity Scoring Rules
• Path A: Pandas Standard Mode
• Path B: HTML Semantic Mode
• Best Practices
• Troubleshooting
• Dependencies
• Resources

1. Metadata Scanning - Use

   openpyxl

   to scan file structure without loading data
2. Complexity Scoring - Calculate score based on merged cells, row count, and layout
3. Path Selection - Choose between Pandas (fast) or HTML (accurate) processing
4. Optimized Execution - Execute with the most appropriate tool for the file type

• Processing Excel/CSV files with unknown structure or varying complexity
• Handling files ranging from simple data tables to complex financial reports
• Need to optimize between processing speed and extraction accuracy
• Working with files that may contain merged cells, multi-level headers, or irregular layouts

• File structure is already known and documented
• Processing simple, well-structured tables with confirmed format
• Using predefined scripts for specific file formats

scripts/complexity_analyzer.py

python scripts/complexity_analyzer.py <file_path> [sheet_name]

• Merged cell distribution (shallow vs deep in the table)
• Row count and data continuity
• Empty row interruptions (indicates multi-table layouts)

{
  "is_complex": false,
  "recommended_strategy": "pandas",
  "reasons": ["No deep merges detected", "Row count exceeds 1000, forcing Pandas mode"],
  "stats": {
    "total_rows": 5000,
    "deep_merges": 0,
    "empty_interruptions": 0
  }
}

• is_complex = false → Use Path A (Pandas Standard Mode)
• is_complex = true → Use Path B (HTML Semantic Mode)

• Condition: Merged cells appearing beyond row 5
• Interpretation: Complex table structure (not just header formatting)
• Decision: Mark as complex if >2 deep merges detected
• Example: Financial reports with merged category labels in data region

• Condition: Multiple empty rows within the table
• Interpretation: Multiple sub-tables in single sheet
• Decision: Mark as complex if >2 empty row interruptions found
• Example: Summary table + detail table in one sheet

• Condition: Total rows >1000
• Interpretation: Too large for HTML processing (token explosion)
• Decision: Force Pandas mode regardless of complexity
• Rationale: HTML conversion would exceed token limits

• Condition: No deep merges, continuous data, moderate size
• Interpretation: Standard data table
• Decision: Use Pandas for optimal speed

1. Sample First 20 Rows
   • Read only the first 20 rows using

     pd.read_excel(..., nrows=20)

   • Convert to CSV format for analysis
2. Determine Header Position
   • Examine the sampled rows to identify which row contains the actual column headers
   • Common patterns: Row 0 (standard), Row 1-2 (if title rows exist), Row with distinct column names
3. Read Full Data
   • Use

     pd.read_excel(..., header=<detected_row>)

     to load complete data
   • The header parameter ensures proper column naming

For implementation details, see

references/smart_excel_router.py

1. Convert to Semantic HTML
   • Load workbook with

     openpyxl

   • Build HTML table preserving merged cell spans
   • Use

     rowspan

     and

     colspan

     attributes to maintain structure
2. Extract Structured Data
   • Analyze HTML table structure
   • Identify hierarchical headers from merged cells
   • Extract data preserving semantic relationships

For implementation details, see

references/smart_excel_router.py

• Symptom:

  FileNotFoundError

  or permission errors
• Causes: Invalid path, file locked by another process, insufficient permissions
• Solutions:
  • Verify file path is correct and file exists
  • Close the file if open in Excel or another application
  • Check read permissions on the file

• Symptom:

  BadZipFile

  or

  InvalidFileException

• Causes: Incomplete download, file corruption, wrong file extension
• Solutions:
  • Re-download or obtain fresh copy of the file
  • Verify file is actual Excel format (not CSV with .xlsx extension)
  • Try opening in Excel to confirm file integrity

• Symptom:

  MemoryError

  or system slowdown
• Causes: File too large for available RAM
• Solutions:
  • Use

    read_only=True

    mode in openpyxl
  • Process file in chunks using Pandas

    chunksize

    parameter
  • Increase system memory or use machine with more RAM

• Symptom: Garbled text or

  UnicodeDecodeError

• Causes: Non-UTF8 encoding in source data
• Solutions:
  • Specify encoding when reading CSV:

    pd.read_csv(..., encoding='gbk')

  • For Excel, data is usually UTF-8; check source data generation

• Symptom: Truncated output or API errors
• Causes: Complex file exceeds token limits despite row count check
• Solutions:
  • Force Pandas mode even for complex files
  • Split sheet into smaller ranges and process separately
  • Extract only essential columns before HTML conversion

• Symptom: Wrong columns or data shifted
• Causes: Unusual header patterns not caught by sampling
• Solutions:
  • Manually specify header row if known
  • Increase sample size beyond 20 rows
  • Use HTML mode for better structure understanding

• openpyxl

  - Metadata scanning and Excel file manipulation

• pandas

  - High-speed data reading and manipulation

• scripts/complexity_analyzer.py

  - Standalone executable for complexity analysis

• references/smart_excel_router.py

  - Complete implementation reference with both processing paths