Excel Parser

Excel Parser

Table of Contents

• 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

Overview

Provide intelligent routing strategies for parsing Excel/CSV files by analyzing complexity and choosing the optimal processing path. The skill implements a "Scout Pattern" that scans file metadata before processing to balance speed (Pandas) with accuracy (semantic extraction).

Core Philosophy: Scout Pattern

Before processing data, deploy a lightweight "scout" to analyze file metadata and make intelligent routing decisions:

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

Key Principle: "LLM handles metadata decisions, Pandas/HTML processes bulk data"

When to Use This Skill

Use excel-parser when:

• 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

Skip this skill when:

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

Processing Workflow

Step 1: Analyze File Complexity

Use the scripts/complexity_analyzer.py to scan file metadata:

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

What it analyzes (without loading data):

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

Output (JSON format):

{
  "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
  }
}

Step 2: Route to Optimal Strategy

Based on complexity analysis:

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

Step 3: Execute Processing

Follow the selected path's workflow to extract data.

Complexity Scoring Rules

Rule 1: Deep Merged Cells

• 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

Rule 2: Empty Row Interruptions

• 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

Rule 3: Row Count Override

• 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

Rule 4: Default (Standard Table)

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

Path A: Pandas Standard Mode

When: Simple/large tables (most common case)

Strategy: Agent analyzes ONLY the first 20 rows to determine header position, then use Pandas to read full data at native speed.

Workflow:

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

Token Cost: ~500 tokens (only 20 rows analyzed) Processing Speed: Very fast (Pandas native speed)

For implementation details, see references/smart_excel_router.py

Path B: HTML Semantic Mode

When: Complex/irregular tables (merged cells, multi-level headers)

Strategy: Convert to semantic HTML preserving structure (rowspan/colspan), then extract data understanding the visual layout.

Workflow:

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

Token Cost: Higher (full HTML structure analyzed) Processing Speed: Slower (semantic extraction) Use Case: Only for small (<1000 rows), complex files where Pandas would fail

For implementation details, see references/smart_excel_router.py