# TCBA Data Extraction Process
> Detailed documentation of regex patterns and extraction techniques for converting legacy Office HTML.
## Overview
This document provides the complete reference for extracting structured data from Microsoft Word and Excel-generated HTML files used throughout the TCBA website.
---
## Source File Analysis
### Word-Generated HTML Characteristics
```html
```
### Excel-Generated HTML Characteristics
```html
```
---
## Team Page Extraction Patterns
### 1. Team Name Extraction
**Goal**: Extract clean team name from header
**Source Pattern**:
```html
Brooklyn Bridegrooms – 1950-1953
```
**Regex**:
```python
# Primary: From h3 header
h3_match = re.search(
r']*>.*?["\']>([A-Z][^<\d]+?)\s*\d{4}',
content,
re.DOTALL | re.IGNORECASE
)
# Fallback: From title tag
title_match = re.search(r'([^<]+)', content)
```
**Post-processing**:
```python
team_name = re.sub(r'<[^>]+>', '', h3_match.group(1)).strip()
# Remove trailing dashes (various unicode dash types)
team_name = re.sub(r'\s*[-–—]\s*$', '', team_name).strip()
```
### 2. Years Range Extraction
**Goal**: Extract team active years (e.g., "1950-1953", "1973-Present", "1936-1942 & 1943-1950")
**Source Patterns**:
```html
Brooklyn Bridegrooms – 1950-1953
Team Name 1936-1942 & 1943-1950
```
**Regex**:
```python
# Format 1: After em-dash
years_match = re.search(
r'>\s*[-–—]\s*(\d{4}[-–—\d&;ampltp\s]+(?:\d{4}|[Pp]resent))',
content
)
# Format 2: In h3 tags
if not years_match:
years_match = re.search(
r']*>.*?(\d{4}[-–—]+(?:\d{4}|[Pp]resent))\s*<',
content,
re.DOTALL
)
```
**Post-processing**:
```python
years = re.sub(r'<[^>]+>', '', years_match.group(1))
years = re.sub(r'\s+', ' ', years).strip()
years = years.replace('&', '&').replace(' - ', '-')
years = years.replace('–', '-').replace('—', '-') # Normalize dashes
```
### 3. GM Name Extraction
**Goal**: Extract General Manager name
**Source Pattern**:
```html
John Smith, GM
```
**Regex**:
```python
gm_match = re.search(r'>([^<]{2,40}),\s*GM<', content)
if gm_match:
gm = gm_match.group(1).strip()
gm = re.sub(r'\s+', ' ', gm).strip() # Normalize whitespace
```
### 4. History Brief Extraction
**Goal**: Extract historical description text that may span multiple `` tags
**Source Pattern**:
```html
History Brief:The team was founded...
They won...
```
**Regex**:
```python
# Primary: To end of paragraph
history_section = re.search(
r'History\s*Brief:?\s*(.*?)',
content,
re.DOTALL | re.IGNORECASE
)
# Fallback: To next section
if not history_section:
history_section = re.search(
r'History\s*Brief:?\s*(.*?)(?:]+>', '', history_section.group(1))
hist = re.sub(r' ', ' ', hist)
# Normalize all whitespace including newlines to single spaces
hist = re.sub(r'\s+', ' ', hist).strip()
# Fix punctuation spacing
hist = hist.replace(' .', '.').replace(' ,', ',')
```
### 5. Special Designation Extraction
**Goal**: Extract honorary titles (Founder, Hall of Fame Member)
**Source Patterns**:
```html
Founder of TCBA Hall of
Fame
```
**Regex**:
```python
# Multi-tag pattern
founder_section = re.search(
r'Founder of\s*]*>\s*TCBA.*?Hall of\s*\n?\s*Fame',
content,
re.IGNORECASE | re.DOTALL
)
# Simple pattern fallback
if not founder_section:
founder_section = re.search(
r'(Founder of[^<]*TCBA|Member of[^<]*TCBA[^<]*Hall[^<]*Fame)',
content,
re.IGNORECASE
)
```
**Post-processing**:
```python
special = re.sub(r'<[^>]+>', ' ', founder_section.group(0))
special = re.sub(r'\s+', ' ', special).strip()
```
### 6. Franchise Number Extraction
**Goal**: Extract franchise number (1-25, no 13)
**Regex**:
```python
franchise_match = re.search(r'Franchise\s*#?\s*(\d+)', content, re.IGNORECASE)
data['franchise'] = franchise_match.group(1) if franchise_match else ''
```
### 7. Win/Loss Record Extraction
**Goal**: Extract all-time W/L from record table
**Regex**:
```python
wl_section = re.search(r'All-Time Record.*?', content, re.DOTALL | re.IGNORECASE)
if wl_section:
rows = re.findall(r']*>(.*?)', wl_section.group(0), re.DOTALL)
if len(rows) >= 2:
cells = re.findall(r'>\s*(\d+)\s*<', rows[1])
data['wins'] = cells[0] if len(cells) > 0 else ''
data['losses'] = cells[1] if len(cells) > 1 else ''
```
### 8. Stats Links Extraction
**Goal**: Extract batting/pitching stats file links
**Regex**:
```python
# Batting links
batting_html = re.search(
r'Batting.*?href="([^"]+)"[^>]*>\s*HTML',
content,
re.DOTALL | re.IGNORECASE
)
batting_excel = re.search(
r'Batting.*?href="([^"]+)"[^>]*>\s*Excel',
content,
re.DOTALL | re.IGNORECASE
)
# Pitching links (same pattern)
pitching_html = re.search(
r'Pitching.*?href="([^"]+)"[^>]*>\s*HTML',
content,
re.DOTALL | re.IGNORECASE
)
```
### 9. Season Records Extraction
**Goal**: Extract year-by-year stats table
**Detection Strategy**: Find rows where first cell is a 4-digit year (1900-2099)
```python
seasons = []
all_rows = re.findall(r']*>(.*?)', content, re.DOTALL)
for row in all_rows:
cells = re.findall(r']*>(.*?) | ', row, re.DOTALL)
if cells and len(cells) >= 5:
values = []
for cell in cells:
text = re.sub(r'<[^>]+>', '', cell)
text = re.sub(r' ', ' ', text)
text = re.sub(r'\s+', ' ', text).strip()
values.append(text)
# Key check: Is first cell a year?
if values[0] and re.match(r'^(19|20)\d{2}$', values[0]):
values = [v for v in values if v] # Remove empty
if len(values) >= 5: # Year + Won + Lost + Place + Division
seasons.append(values[:7]) # Up to 7 columns
```
---
## Excel File Extraction
### Championship Data (LeagueChamps)
**Goal**: Extract team names from Excel-generated championship table
**Pattern**:
```python
# Find all cells with team names
teams = re.findall(r']*>(.*?)', content, re.DOTALL)
for cell in teams:
team_name = re.sub(r'<[^>]+>', '', cell).strip()
if team_name and len(team_name) > 2:
championship_teams.append(team_name)
```
### Team Abbreviations
**Pattern**:
```python
# Extract from 3-column table: City, Nickname, Abbreviation
rows = re.findall(r']*>(.*?)', content, re.DOTALL)
for row in rows:
cells = re.findall(r']*>(.*?)', row, re.DOTALL)
if len(cells) >= 3:
city = re.sub(r'<[^>]+>', '', cells[0]).strip()
nickname = re.sub(r'<[^>]+>', '', cells[1]).strip()
abbrev = re.sub(r'<[^>]+>', '', cells[2]).strip()
```
---
## Encoding Handling
### Reading Legacy Files
```python
# Always try windows-1252 first for legacy files
try:
content = Path(filepath).read_text(encoding='windows-1252')
except:
try:
content = Path(filepath).read_text(encoding='utf-8')
except:
content = Path(filepath).read_text(encoding='latin-1')
```
### Writing Modern Files
```python
# Always write as UTF-8
Path(filepath).write_text(html, encoding='utf-8')
```
---
## Special Character Handling
### HTML Entities
```python
text = text.replace(' ', ' ')
text = text.replace('&', '&')
text = text.replace('<', '<')
text = text.replace('>', '>')
text = text.replace('"', '"')
```
### Unicode Dash Normalization
```python
text = text.replace('–', '-') # EN DASH
text = text.replace('—', '-') # EM DASH
text = text.replace('‒', '-') # FIGURE DASH
```
### Curly Quote Handling
```python
text = text.replace(''', "'") # RIGHT SINGLE QUOTATION
text = text.replace(''', "'") # LEFT SINGLE QUOTATION
text = text.replace('"', '"') # LEFT DOUBLE QUOTATION
text = text.replace('"', '"') # RIGHT DOUBLE QUOTATION
```
---
## Validation Checklist
After extraction, verify:
- [ ] Team name is clean (no trailing dashes, no HTML tags)
- [ ] Years format is consistent (YYYY-YYYY or YYYY-Present)
- [ ] GM name has no line breaks or extra whitespace
- [ ] History text is complete (not truncated)
- [ ] Season records have at least 5 columns per row
- [ ] All file links are valid (no broken URLs)
- [ ] Franchise number is in range 1-25 (excluding 13)
---
## Debugging Tips
### Print Extracted Data
```python
from extract_team import extract_team_data
data = extract_team_data('ProblemFile.htm')
for key, value in data.items():
if key != 'seasons':
print(f'{key}: {repr(value)}') # Use repr to see hidden chars
```
### Check for Encoding Issues
```python
import chardet
with open('file.htm', 'rb') as f:
raw = f.read()
result = chardet.detect(raw)
print(f"Detected encoding: {result['encoding']}")
```
### Test Regex Patterns
```python
import re
content = Path('file.htm').read_text(encoding='windows-1252')
# Test pattern
pattern = r'your_pattern_here'
matches = re.findall(pattern, content, re.DOTALL)
print(f"Found {len(matches)} matches:")
for m in matches[:5]:
print(repr(m[:100])) # First 100 chars of each match
```