# Customer File Testing Plan & Solution Strategy

**Date:** 2025-11-30
**Author:** System Analysis
**Purpose:** Comprehensive plan to handle common issues in customer Excel files

---

## Executive Summary

**Files Analyzed:** 58 Excel files from `/AIHackathonFolder/Phase2/CustomerDocument/`
**Current Success Rate:** 74.1% (43/58 files)
**Target Success Rate:** 85-90%
**Gap Analysis:** Primary blocker is legacy .xls format (26% of files, 100% failure rate)

### Quick Stats
- **File Formats:** 38% .xlsx, 36% .xlsm, 26% .xls (legacy)
- **Size Distribution:** 60% small (<0.1MB), 30% medium (0.1-1MB), 10% large (1MB+)
- **Common Features:** 81% have merged cells, 5% have inflated sheets, 7% have images
- **Performance:** Average load time 1.35s, 12% of files take >5s

---

## Test Results Summary

### Files Tested: 58 Total

#### ✓ Successfully Analyzed: 43 files (74.1%)
- All .xlsx files: 22/22 (100%)
- All .xlsm files: 21/21 (100%)
- No .xls files: 0/15 (0%)

#### ✗ Failed: 15 files (25.9%)
- **Root cause:** 100% are old .xls format
- **Error:** "openpyxl does not support the old .xls file format"

### Representative Sample Files Selected

1. **LARGE_MULTI_SHEET** (5.12MB, 6 sheets)
   - `01.01.01_流通項目仕様書【インターフェース記述書】.xlsx`
   - Load time: 11.99s
   - High merged cells

2. **INFLATED** (0.62MB, 1 sheet, 6747 rows)
   - `削除_01.02.01.34.01_遷移可能項目名.xlsm`
   - Inflated max_row detected
   - Load time: 5.29s

3. **MEDIUM_XLSM** (0.21MB, 13 sheets)
   - `1.3 イベントPF（共）システム一覧.xlsm`
   - Load time: 1.26s
   - Multiple sheets with merged cells

4. **SMALL_XLSX** (0.03MB, 2 sheets)
   - `削除_01.02.01.03.05_帳票送信運用チェック.xlsx`
   - Fast load: 0.04s

5. **HIGH_MERGE** (4.82MB, 2 sheets)
   - `1.2 JP1設定項目.xlsx`
   - Extensive merged cells
   - Load time: 8.72s

6. **WITH_IMAGES** (0.47MB, 4 sheets)
   - `01.04.02.01.01.08_システム図（人事企画（共インターフェース））.xlsm`
   - Contains embedded images
   - Load time: 0.08s

---

## Issues Categorized by Type

### 🔴 P0: CRITICAL (Blocking Issues)

#### Issue 1.1: Legacy .xls Format Not Supported
**Impact:** 15 files (26%) fail completely
**Current State:** openpyxl cannot read .xls format
**Business Impact:** Cannot process 1/4 of customer files

**Root Cause:**
- Old Excel 97-2003 binary format (.xls)
- openpyxl only supports Office Open XML (.xlsx, .xlsm)

**Proposed Solutions:**

| Solution | Pros | Cons | Effort |
|----------|------|------|--------|
| **A. Add xlrd library** | Simple, direct reading | xlrd is unmaintained since 2020, security concerns | Low (1-2 days) |
| **B. Convert .xls to .xlsx** | Works with existing pipeline, one-time conversion | Modifies original files, storage overhead | Medium (3-4 days) |
| **C. Use pandas read_excel()** | Handles both formats, actively maintained | Extra dependency, may lose some formatting | Low (2-3 days) |
| **D. Offer manual conversion** | No code changes needed | Manual process, user friction | None |

**Recommendation:** **Solution C (pandas)** - Best balance of maintainability and compatibility
- Add fallback: Try openpyxl first, fall back to pandas for .xls
- Implementation: Wrap file loading in format-aware function
- Trade-off: Accept minor formatting differences for .xls files

**Acceptance Criteria:**
- ✓ Can load and extract data from .xls files
- ✓ Success rate increases to 85%+
- ✓ No regressions on .xlsx/.xlsm files

---

### 🟡 P1: IMPORTANT (Performance & Reliability)

#### Issue 2.1: Inflated Sheet Dimensions
**Impact:** 2 files (5%) have sheets with max_row > 5000
**Current State:** V2 pipeline already skips border detection for inflated sheets
**Business Impact:** Can cause timeouts, slow processing

**Examples:**
- `選択可能項目名一覧.xlsm`: 6,747 rows
- `帳票管理情報.xlsx`: 1,048,575 rows (Excel max!)

**Root Cause:**
- Excel file has data in early rows but max_row set to maximum
- Likely due to formatting applied to entire columns
- Border detection scans all rows unnecessarily

**Current Mitigation (V2):**
- Detects inflated sheets (max_row > 5000)
- Skips border detection for these sheets
- Status: ✓ ALREADY IMPLEMENTED

**Additional Solutions:**

| Solution | Pros | Cons | Effort |
|----------|------|------|--------|
| **A. Current: Skip border detection** | Already works | May miss some table boundaries | ✓ Done |
| **B. Smart row scanning** | More accurate boundaries | Complex logic | Medium |
| **C. Use openpyxl UsedRange** | Only scan actual data | May still be inflated | Low |

**Recommendation:** Keep current solution (A), monitor results

**Acceptance Criteria:**
- ✓ Inflated sheets don't timeout
- ✓ Processing time <30s per inflated sheet
- ✓ Tables still detected correctly

---

#### Issue 2.2: Slow File Loading Performance
**Impact:** 5 files (12%) take >5s to load, largest takes 12s
**Current State:** Synchronous loading blocks processing
**Business Impact:** User experience, timeout risk

**Slow Files:**
1. `01.01.01_流通項目仕様書.xlsx`: 11.99s (5.12MB)
2. `01.04.02.01.02.04_システム図一覧.xlsm`: 9.13s (0.62MB)
3. `Optos項目一覧.xlsm`: 8.91s (0.82MB)
4. `JP1設定項目.xlsx`: 8.72s (4.82MB)

**Root Cause:**
- Large file sizes (4-5MB)
- Many sheets (9-15 sheets)
- Complex formatting and merged cells
- Synchronous openpyxl loading

**Proposed Solutions:**

| Solution | Pros | Cons | Effort |
|----------|------|------|--------|
| **A. Set reasonable timeouts** | Prevents hanging | May fail valid files | Low (1 day) |
| **B. Async/background processing** | Non-blocking UI | Complex error handling | Medium (3-4 days) |
| **C. Progress tracking** | Better UX | Requires frontend changes | Medium (2-3 days) |
| **D. Lazy sheet loading** | Faster initial load | More complex code | High (5+ days) |

**Recommendation:** **Multi-pronged approach**
- Immediate: Set 60s timeout for large files (Solution A)
- Short-term: Add progress tracking (Solution C)
- Long-term: Consider lazy loading (Solution D)

**Acceptance Criteria:**
- ✓ No file takes >60s to process
- ✓ User sees progress for files >5s
- ✓ Graceful error messages on timeout

---

#### Issue 2.3: Comment Extraction Performance
**Impact:** Not detected in analyzed files, but known issue
**Current State:** V2 pipeline already optimized comment extraction
**Business Impact:** Historical issue, now resolved

**Status:** ✓ ALREADY HANDLED in V2 pipeline

---

### 🟢 P2: NICE-TO-HAVE (Quality Improvements)

#### Issue 3.1: Merged Cell Header Extraction
**Impact:** 35 files (81%) use merged cells extensively
**Current State:** Header extractor handles merged cells
**Business Impact:** Very common pattern, must handle correctly

**Example Pattern:**
```
| Category (merged across 3 cols) |
| SubA | SubB | SubC |
| Data | Data | Data |
```

**Current Implementation:**
- V2 pipeline uses LLM for header extraction
- Merged cells detected and resolved
- Status: ✓ ALREADY IMPLEMENTED

**Proposed Enhancements:**

| Solution | Pros | Cons | Effort |
|----------|------|------|--------|
| **A. Current LLM extraction** | Handles complex merges | LLM cost | ✓ Done |
| **B. Add validation rules** | Catch edge cases | More code | Low (2 days) |
| **C. Visual merge detection** | More accurate | Complex | High |

**Recommendation:** Monitor results, add validation if needed (Solution B)

**Acceptance Criteria:**
- ✓ Headers extracted from merged cells
- ✓ Hierarchical relationships preserved
- ✓ Success rate >90% on merged cell files

---

#### Issue 3.2: Image & Shape Handling
**Impact:** 3 files (7%) have images, shapes not detected
**Current State:** V2 pipeline extracts images, shapes need service
**Business Impact:** Minor - mostly decorative content

**Current Implementation:**
- Image extraction: ✓ WORKING
- Image description: Available if enabled
- Shape extraction: Requires external service

**Recommendation:** Current state acceptable, shapes are P2

**Acceptance Criteria:**
- ✓ Images detected and logged
- ✓ No crashes on image-containing files
- ○ Shapes extracted (future enhancement)

---

#### Issue 3.3: Sheet Name Encoding
**Impact:** All files use Japanese characters, no issues detected
**Current State:** UTF-8 handling working correctly
**Business Impact:** None currently

**Status:** ✓ NO ACTION NEEDED

---

## Implementation Priority & Timeline

### Phase 1: Critical Fixes (Week 1)
**Goal: Reach 85% success rate**

1. **Add .xls format support** (3 days)
   - Implement pandas fallback loader
   - Add format detection
   - Test on all 15 .xls files
   - Expected impact: +26% success rate → 100% (all files loadable)

2. **Set processing timeouts** (1 day)
   - Add 60s timeout for extraction
   - Graceful error messages
   - Log slow files for analysis

### Phase 2: Performance Improvements (Week 2)
**Goal: Improve processing speed and UX**

3. **Add progress tracking** (3 days)
   - Emit progress events
   - Update UI with current sheet/stage
   - Better for large files

4. **Validate merged cell extraction** (2 days)
   - Test on high-merge files
   - Add validation rules
   - Document edge cases

### Phase 3: Nice-to-Have (Week 3+)
**Goal: Handle edge cases**

5. **Enhance shape extraction** (Optional)
   - Set up shape service
   - Test on files with shapes
   - Document limitations

---

## Testing Strategy

### Test Coverage Matrix

| File Type | Size | Sheets | Features | Test Count | Priority |
|-----------|------|--------|----------|------------|----------|
| .xls legacy | Any | Any | N/A | 15 | P0 |
| .xlsx small | <0.1MB | 1-5 | Merged | 5 | P1 |
| .xlsx large | >1MB | 6+ | Merged | 3 | P1 |
| .xlsm medium | 0.1-1MB | 6-15 | Merged, Macros | 4 | P1 |
| Inflated | Any | Any | max_row>5000 | 2 | P1 |
| With images | Any | Any | Images | 3 | P2 |

**Total Test Files:** 32 (55% of corpus)

### Automated Test Suite

```python
# Test scenarios
test_scenarios = [
    # P0: Format support
    ("xls_support", "Can load .xls files", ".xls files"),

    # P1: Performance
    ("timeout_large", "Process large files within 60s", "files >1MB"),
    ("inflated_sheets", "Handle inflated sheets", "max_row>5000"),

    # P1: Data quality
    ("merged_headers", "Extract merged cell headers", "high merge files"),
    ("sheet_names", "Preserve sheet names", "all files"),

    # P2: Special features
    ("images", "Detect images", "files with images"),
    ("multi_sheet", "Process all sheets", "files with 10+ sheets"),
]
```

### Success Metrics

**Target: 85-90% success rate** (acceptable trade-off)

- **Load Success:** 95%+ files load without errors
- **Extraction Success:** 85%+ files extract meaningful records
- **Performance:** 95%+ files process within 60s
- **Data Quality:** 90%+ records have correct headers and sheet names

### Edge Cases to Accept

**It's OK to fail on:**
1. Corrupted files (unrecoverable)
2. Password-protected files (no password provided)
3. Extremely complex files (>100 sheets, >50MB)
4. Files with only charts/no tables (no data to extract)

**Trade-offs:**
- .xls files may have minor formatting differences vs .xlsx
- Very large files (>10MB) may timeout - prompt user to split
- Inflated sheets skip border detection - may miss some boundaries
- Shapes without external service - won't be extracted

---

## Risk Assessment

### High Risk
- **Risk:** .xls library (xlrd) has security vulnerabilities
  - **Mitigation:** Use pandas (actively maintained) or validate files first

- **Risk:** LLM header extraction costs increase with scale
  - **Mitigation:** Monitor costs, add rule-based fallback

### Medium Risk
- **Risk:** Timeout too short for complex files
  - **Mitigation:** Make timeout configurable, start at 60s

- **Risk:** Inflated sheet detection misses tables
  - **Mitigation:** Test thoroughly, document known limitations

### Low Risk
- **Risk:** Sheet name encoding issues
  - **Mitigation:** Already handling UTF-8 correctly

---

## Monitoring & Validation Plan

### Metrics to Track
1. **Success Rate:** % files successfully processed
2. **Processing Time:** avg/p95/p99 processing times
3. **Error Types:** categorize errors (format, timeout, data, other)
4. **Record Quality:** % records with headers, sheet names

### Validation Approach
1. **Manual Review:** Spot-check 10% of extractions
2. **Automated Checks:** Validate record structure
3. **User Feedback:** Track issues reported
4. **Regression Tests:** Ensure fixes don't break existing files

### Dashboard (Proposed)
```
Customer File Processing Health
--------------------------------
Success Rate:      87% (51/58) ✓
Avg Process Time:  12.3s
P95 Process Time:  45.2s
Common Errors:
  - Timeout:       5% (3 files)
  - Format:        0% (0 files) ✓
  - Data Quality:  8% (5 files)
```

---

## Recommendations Summary

### Immediate Actions (Do Now)
1. ✅ **Implement pandas .xls loader** - Unblocks 26% of files
2. ✅ **Add 60s processing timeout** - Prevents hanging
3. ✅ **Document current V2 optimizations** - Inflated sheets, comments

### Short-term (Next Sprint)
4. 📋 **Add progress tracking** - Better UX for large files
5. 📋 **Validate merged cell extraction** - Test on high-merge files
6. 📋 **Set up automated testing suite** - Prevent regressions

### Long-term (Future)
7. 🔮 **Consider lazy sheet loading** - If performance still an issue
8. 🔮 **Shape extraction service** - If shapes become important
9. 🔮 **Caching strategy** - For repeated processing

---

## Appendix A: File Analysis Data

**Location:** `/home/hieutt50/projects/DSOL/customer_files_analysis.json`

**Summary Statistics:**
- Total files: 58
- Successfully analyzed: 43 (74.1%)
- Failed: 15 (25.9%)
  - All .xls format: 15 (100% of failures)

**File Format Distribution:**
- .xlsx: 22 (37.9%)
- .xlsm: 21 (36.2%)
- .xls: 15 (25.9%)

**Size Distribution:**
- Small (<0.1MB): 26 (60.5%)
- Medium (0.1-1MB): 13 (30.2%)
- Large (>=1MB): 4 (9.3%)

**Feature Detection:**
- Inflated sheets: 2 (4.7%)
- Merged cells: 35 (81.4%)
- Comments: 0 (0.0%)
- Images: 3 (7.0%)

---

## Appendix B: Sample File Paths

**Representative test files saved to:**
`/home/hieutt50/projects/DSOL/sample_files_to_test.txt`

**Quick test script:**
`/home/hieutt50/projects/DSOL/test_sample_files.py`

**Analysis scripts:**
- `analyze_customer_files.py` - File structure analysis
- `select_sample_files.py` - Representative sample selection
- `summarize_analysis.py` - Summary statistics

---

## Document Version History

- **v1.0** (2025-11-30): Initial comprehensive testing plan
  - Analyzed 58 customer files
  - Identified P0 issue: .xls format support
  - Documented V2 pipeline optimizations
  - Created implementation timeline