Dataextract.Template

# /{{PROJECT_NAME}}extract - AI-Powered Data Extraction

**Purpose:** Extract {{DATA_TYPE}} data from {{DOCUMENT_TYPE}} using AI analysis, validate against {{STANDARDS_NAME}} standards, and export to multiple formats

**Key Innovation:** Iterative AI-guided extraction with PDF analysis + AI structure understanding + validation loop

---

## ✅ SCHEMA: {{PROJECT_NAME}} Database Schema

**Database Schema:** {{PROJECT_NAME}} schema
- **Tables:** {{#DB_TABLES}}{{TABLE_NAME}}{{/DB_TABLES}}
- **Fields:** {{NAMING_CONVENTION}} naming (e.g., {{EXAMPLE_FIELDS}})
- **Primary Keys:** {{PK_STRATEGY}}
- **Foreign Keys:** {{FK_STRATEGY}}

**Extraction Output:** This command outputs {{OUTPUT_FORMAT}} matching {{PROJECT_NAME}} schema

---

## Workflow Overview

1. Read {{INDEX_FILENAME}} → Get table locations, page numbers, supplementary URLs
2. AI structure analysis → Understand headers & data patterns
3. Create extraction plan → Bespoke extraction strategy
4. Extract to multiple formats → CSV + JSON + SQL
5. Validate extraction → Check completeness, data ranges
6. Retry loop → Delete & retry until perfect (MAX 3 attempts per table)

---

## 🚨 CRITICAL EXTRACTION RULES

**BEFORE starting extraction, understand these NON-NEGOTIABLE requirements:**

### 1. Extract ALL Tables (No Exceptions)

**Rule:** EVERY table marked "extractable: YES" in {{INDEX_FILENAME}} MUST be extracted

- ❌ **WRONG:** "Extract main tables, skip supplementary for later"
- ✅ **CORRECT:** "Extract ALL tables before marking extraction complete"

**Why:** {{SUPPLEMENTARY_IMPORTANCE_EXPLANATION}}

**Priority** determines extraction ORDER, NOT whether to extract

### 2. Extract ALL Columns (No Partial Extraction)

**Rule:** EVERY column in the source table MUST appear in the extracted output

- ❌ **WRONG:** Extract 12/24 columns and mark table "complete"
- ✅ **CORRECT:** Extract ≥90% of columns, validate, retry if <90%

**Validation enforces this:** Fails extraction if column count <90% of source

### 3. Validate Before Proceeding (Mandatory)

**Rule:** NO table is "complete" until ALL validation checks pass

- ❌ **WRONG:** Extract → move to next table
- ✅ **CORRECT:** Extract → validate → retry if fails → THEN next table

**Auto-retry:** If validation fails, automatically retries up to 3 times

---

## Step 1: Load Paper Analysis

**Task:** Read the {{INDEX_FILENAME}} to identify tables and their exact page numbers

**File to read:**
```bash
build-data/learning/{{PAPERS_DIR}}/PAPER_NAME/{{INDEX_FILENAME}}

Extract from "📊 Data Tables" section:

Field	What to Extract	Example
Table identifiers	Table numbers/names	{{TABLE_ID_EXAMPLES}}
Page numbers	Exact page(s) where table appears	Page 9, Pages 10-11 (spans 2 pages)
Data type	{{DATA_TYPE_EXAMPLES}}	{{DATA_TYPE_EXAMPLE_VALUES}}
Description	Brief content summary	{{CONTENT_DESCRIPTION_EXAMPLES}}

CRITICAL: Extract ALL tables marked as "extractable: YES" - NO tables are optional!

---

Step 2: AI Structure Analysis

Task: Use AI to analyze table structure from PDF pages

Process:

1. Load table PDF from images/tables/
2. Extract structure information
3. Ask AI to analyze

AI Analysis Prompt:

You are analyzing a {{DATA_TYPE}} data table extracted from a {{DOCUMENT_TYPE}}.

Here is the table from pages {{PAGE_RANGE}}:
[TABLE PDF CONTENT]

Analyze the structure and answer:
1. What are the column headers? (exact text)
2. What delimiter separates columns?
3. Are headers on one line or multiple lines?
4. What pattern do {{ENTITY_NAME}} IDs follow? (regex)
5. How many columns are there?
6. Are there any footnote symbols or special characters?
7. Are there any merged cells or spanning columns?
8. What numeric format is used? (decimal: . or ,)
9. How are uncertainties represented? (±, separate column, parentheses?)
10. Are there any non-data rows? (subtotals, averages, blank rows?)

Provide a concise structural summary.

AI Output Example:

Table Structure Analysis:
- Headers: Single line, space-separated
- Columns: {{EXAMPLE_COLUMN_COUNT}} columns detected
- {{ENTITY_NAME}} ID: Pattern "{{EXAMPLE_ID_PATTERN}}"
- Delimiter: Multiple spaces (aligned columns)
- Uncertainties: ± format in same cell
- Special notes:
  * {{SPECIAL_NOTE_EXAMPLES}}

---

Step 3: Create Extraction Plan

Task: Based on AI structure analysis, create extraction strategy

Extraction Methods:

1. Text extraction (primary) - Fast, from plain-text.txt
2. PDF extraction (validation) - From PDF pages, better structure
3. AI extraction (fallback) - Direct AI reading of PDF

Output formats:

• CSV: Clean tabular data
• JSON: Structured data with metadata
• SQL: INSERT statements for direct database import

---

Step 4: Extract to Multiple Formats

4.1: Load Table Index

import json
from pathlib import Path

paper_dir = Path('build-data/learning/{{PAPERS_DIR}}/PAPER_NAME')
table_index_file = paper_dir / 'table-index.json'

with open(table_index_file, 'r') as f:
    table_index = json.load(f)

print(f'✅ Loaded table index with {len(table_index["tables"])} tables')

4.2: Extract from PDF Pages

# Select table to extract
table_name = '{{EXAMPLE_TABLE_NAME}}'
table_info = next(t for t in table_index['tables'] if t['name'] == table_name)

# Load table PDF
pdf_file = paper_dir / table_info['pdf_pages']['file']
pages = table_info['pdf_pages']['pages']

print(f'📄 Extracting {table_name}')
print(f'   PDF: {pdf_file.name}')
print(f'   Pages: {pages[0]}-{pages[-1]} ({len(pages)} pages)')

# Use AI to parse table structure and extract data
# [AI analyzes PDF and generates structured output]

4.3: Generate Multiple Formats

import pandas as pd
import json

# Create DataFrame from extracted data
df = pd.DataFrame(extracted_data)

# 1. Export to CSV
csv_file = paper_dir / 'extracted' / f'{table_name.lower().replace