# User Story: Fix File Discovery & Add .xlsm Support

**Story ID:** story-v2-pipeline-testing-1
**Epic:** V2 Pipeline Comprehensive Testing
**Created:** 2025-12-01
**Status:** Ready for Development
**Story Points:** 3
**Priority:** High

---

## User Story

**As a** data engineer
**I want** the batch processor to discover all Excel file formats (.xlsx, .xls, .xlsm)
**So that** all 58 CustomerDocument/ files are tracked and can be processed through the V2 pipeline

---

## Context

**Current Situation:**
- Batch processor only discovers 37/58 files (64% coverage)
- Missing glob pattern for .xlsm (macro-enabled Excel) files
- 21 .xlsm files untracked and unprocessed
- Line 222 in batch_extract_v2.py needs update

**Problem:**
Cannot achieve 95% success rate target if 36% of files aren't even discovered.

**Solution:**
Add .xlsm pattern to glob search and implement pre-flight file validation.

---

## Technical Details

**File to Modify:**
`batch_extract_v2.py`

**Line 222 Current Code:**
```python
for pattern in ["**/*.xlsx", "**/*.xls"]:
    excel_files.extend(self.folder_path.glob(pattern))
```

**Updated Code:**
```python
EXCEL_PATTERNS = ["**/*.xlsx", "**/*.xls", "**/*.xlsm"]

for pattern in EXCEL_PATTERNS:
    matches = list(self.folder_path.glob(pattern))
    logger.debug(f"Pattern {pattern} found {len(matches)} files")
    excel_files.extend(matches)
```

**Additional Enhancement - Pre-flight Validation:**
```python
def validate_excel_file(file_path: Path) -> tuple[bool, Optional[str]]:
    """Validate file before processing."""
    # Check exists and readable
    if not file_path.exists():
        return False, "File not found"

    # Check size (< 100MB)
    size_mb = file_path.stat().st_size / (1024 * 1024)
    if size_mb > 100:
        return False, f"File too large: {size_mb:.1f}MB"

    # Verify valid Excel format
    ext = file_path.suffix.lower()
    if ext not in ['.xlsx', '.xls', '.xlsm']:
        return False, f"Unsupported format: {ext}"

    # For .xlsx/.xlsm: verify valid ZIP
    if ext in ['.xlsx', '.xlsm']:
        try:
            import zipfile
            with zipfile.ZipFile(file_path, 'r') as zf:
                if 'xl/workbook.xml' not in zf.namelist():
                    return False, "Invalid Excel structure"
        except zipfile.BadZipFile:
            return False, "Corrupted file"

    return True, None
```

---

## Implementation Steps

1. **Update glob patterns**
   - File: `batch_extract_v2.py` line 222
   - Change: Add `"**/*.xlsm"` to patterns list
   - Test: Run and count discovered files

2. **Add validation helper**
   - Location: After `find_excel_files()` method
   - Add: `validate_excel_file()` function
   - Purpose: Pre-flight checks before processing

3. **Integrate validation**
   - File: `batch_extract_v2.py` in `process_file()`
   - Add validation call before pipeline execution
   - Skip invalid files with clear error message

4. **Verify discovery**
   ```bash
   uv run python -c "
   from pathlib import Path
   from batch_extract_v2 import BatchProcessor
   bp = BatchProcessor('CustomerDocument/')
   files = bp.find_excel_files()
   print(f'Discovered: {len(files)} files')
   print(f'  .xlsx: {sum(1 for f in files if f.suffix == \".xlsx\")}')
   print(f'  .xls:  {sum(1 for f in files if f.suffix == \".xls\")}')
   print(f'  .xlsm: {sum(1 for f in files if f.suffix == \".xlsm\")}')
   assert len(files) == 58, f'Expected 58, got {len(files)}'
   "
   ```

5. **Test edge cases**
   - Run on test dataset with mixed formats
   - Verify validation catches corrupted files
   - Test with password-protected file (should skip gracefully)

---

## Acceptance Criteria

1. ✅ Batch processor discovers all 58 files in CustomerDocument/
   - 22 .xlsx files
   - 15 .xls files
   - 21 .xlsm files

2. ✅ File validation function implemented
   - Checks file exists and is readable
   - Validates file size (<100MB)
   - Verifies Excel format validity
   - Returns clear error messages

3. ✅ Invalid files skipped gracefully
   - Logged with reason
   - Marked in status JSON
   - Processing continues

4. ✅ Verification script passes
   - Asserts 58 total files
   - Counts by format match expected
   - No duplicates in list

5. ✅ Documentation updated
   - README mentions .xlsm support
   - Code comments explain validation logic

---

## Testing Strategy

**Unit Tests:**
```python
def test_find_excel_files_includes_xlsm():
    bp = BatchProcessor("CustomerDocument/")
    files = bp.find_excel_files()

    xlsm_files = [f for f in files if f.suffix == ".xlsm"]
    assert len(xlsm_files) == 21

def test_validate_excel_file_valid():
    result = validate_excel_file(Path("test.xlsx"))
    assert result == (True, None)

def test_validate_excel_file_invalid_size():
    large_file = create_large_test_file()  # >100MB
    result = validate_excel_file(large_file)
    assert result[0] == False
    assert "too large" in result[1].lower()
```

**Manual Verification:**
```bash
# Count files before change
find CustomerDocument/ -name "*.xl*" | wc -l  # Should be 58

# Run batch processor
uv run python batch_extract_v2.py --dry-run  # Check discovery only

# Verify status JSON
cat batch_extraction_status.json | jq '.files | length'  # Should be 58
```

---

## Dependencies

**External Dependencies:**
- None (uses existing stdlib and project deps)

**Internal Dependencies:**
- `pathlib.Path` - File path handling
- `zipfile` - Excel structure validation
- `structlog` - Logging

**Blockers:**
- None - can start immediately

---

## Definition of Done

- [ ] Code changes committed to feature branch
- [ ] glob patterns include .xlsm
- [ ] File validation function added and tested
- [ ] Verification script passes (58 files discovered)
- [ ] Unit tests written and passing
- [ ] Manual testing completed
- [ ] Code reviewed
- [ ] Documentation updated
- [ ] Ready for Story 2 (test suite needs all files)

---

## Reference

**Tech-Spec:** `docs/feats/wrapup_pipeline_v2/tech-spec.md`
**Epic:** `docs/feats/wrapup_pipeline_v2/epics.md`
**Key Files:**
- `batch_extract_v2.py:38-85` - find_excel_files() method
- `batch_extract_v2.py:96-211` - process_file() method

---

**Story Status:** Ready for Development