DavidROliverBA/.claude/skills/csv-to-sql

/csv-to-sql

Convert CSV files into SQLite databases stored in .data/ for efficient querying by Claude Code.

Why SQLite? Large CSV data converted to markdown tables creates files too large for Claude Code to read efficiently. SQLite enables:

• Direct SQL queries via sqlite3 CLI
• Aggregations (AVG, COUNT, SUM, GROUP BY)
• Full-text search on long content
• Filtered retrieval (WHERE clauses)
• JSON or markdown output formats

Usage

/csv-to-sql <csv-path>
/csv-to-sql Inbox/data.csv
/csv-to-sql Inbox/rfi.csv --start-row 14 --fts
/csv-to-sql Inbox/survey.csv --db-name survey-2026.db --table responses

Instructions

Phase 1: CSV Analysis

1. Verify the CSV file exists at the specified path
2. Run with --dry-run to preview schema:

   node scripts/csv-to-sqlite.js "<csv-path>" --dry-run --verbose
   

3. Review the output to identify:
   • Column names and inferred types
   • Which columns will be indexed
   • If --start-row is needed to skip metadata

Phase 2: Database Creation

Run the conversion:

node scripts/csv-to-sqlite.js "<csv-path>" --start-row <n> --fts --verbose

Script Options:

| Option | Description | | ----------------- | ------------------------------------------------- | | --db-name <n> | Custom database filename (default: from CSV name) | | --table <name> | Table name (default: 'data') | | --start-row <n> | Skip first N rows (0-indexed, for metadata) | | --fts | Create full-text search index on long text cols | | --dry-run | Preview schema without creating database | | --verbose | Show detailed progress and schema |

Phase 3: Verify and Query

After creation, test the database:

# Show table structure
sqlite3 .data/<db-name>.db ".schema"

# Preview data
sqlite3 .data/<db-name>.db -markdown -header "SELECT * FROM data LIMIT 5;"

# Count rows
sqlite3 .data/<db-name>.db "SELECT COUNT(*) FROM data;"

Phase 4: Summary Report

Provide user with:

## CSV to SQLite Complete

**Database**: `.data/<db-name>.db`
**Table**: `<table-name>`

**Statistics**:

- Rows: <count>
- Columns: <count>
- Indexes: <list>
- FTS enabled: Yes/No

**Quick Queries**:

```bash
# List all records
sqlite3 ".data/<db>.db" -markdown -header "SELECT * FROM <table>;"

# Aggregate by category
sqlite3 ".data/<db>.db" -markdown -header "SELECT category, COUNT(*) FROM <table> GROUP BY category;"

# Search text (if FTS enabled)
sqlite3 ".data/<db>.db" "SELECT * FROM <table>_fts WHERE <table>_fts MATCH 'keyword';"
```

## Common Query Patterns

### For RFI/Survey Data

```sql
-- Average score by category
SELECT category, ROUND(AVG(score), 2) as avg_score, COUNT(*) as questions
FROM data GROUP BY category ORDER BY avg_score DESC;

-- Questions with lowest scores
SELECT id, question, category, score
FROM data WHERE score < 6 ORDER BY score ASC;

-- Score distribution
SELECT score, COUNT(*) as count
FROM data GROUP BY score ORDER BY score;

-- Search responses for keyword
SELECT id, question, response
FROM data_fts WHERE data_fts MATCH 'integration pattern';

For General Data

-- Distinct values in a column
SELECT DISTINCT category FROM data;

-- Records matching criteria
SELECT * FROM data WHERE status = 'active' AND priority = 'high';

-- Export to JSON
-- Use: sqlite3 .data/db.db -json "SELECT * FROM data;"

Output Formats

The sqlite3 CLI supports multiple output formats:

| Format | Flag | Use Case | | -------- | ------------------- | --------------------------- | | Markdown | -markdown -header | Display in Obsidian/Claude | | JSON | -json | Parsing, further processing | | Table | -table -header | Human-readable terminal | | CSV | -csv -header | Export/import workflows | | Plain | (default) | Simple output |

Database Location

All databases are stored in .data/ directory:

YourVault/
├── .data/
│   ├── vendor-rfi-scoring.db
│   ├── vendor-evaluation.db
│   └── survey-results.db
└── Inbox/
    └── source-data.csv

Schema Inference

The script automatically detects column types:

| CSV Content | SQLite Type | Notes | | ----------------------- | ----------- | ------------------------- | | Integers (1, 42, -5) | INTEGER | Enables numeric ops | | Decimals (3.14, 99.9) | REAL | For scores, percentages | | Currency (£100, $50.00) | REAL | Symbols stripped | | Text, long responses | TEXT | FTS-indexed if --fts used | | Empty cells | NULL | Properly handled |

Automatic Indexing

Indexes are created on columns containing:

• id - Primary key lookups
• category - Common filter criteria
• type - Classification queries
• status - Workflow filtering

Full-Text Search (FTS)

When --fts is used, a virtual FTS5 table is created for text columns >200 characters:

-- Search in any text column
SELECT * FROM data_fts WHERE data_fts MATCH 'middleware integration';

-- Search with ranking
SELECT *, rank FROM data_fts WHERE data_fts MATCH 'API design' ORDER BY rank;

-- Phrase search
SELECT * FROM data_fts WHERE data_fts MATCH '"backward compatibility"';

Integration with Claude Code

Once the database exists, query it directly:

# From Claude Code, run queries via Bash tool
sqlite3 .data/vendor-rfi-scoring.db -markdown -header "
SELECT category, COUNT(*) as questions, ROUND(AVG(score),1) as avg
FROM data
GROUP BY category
ORDER BY avg DESC;"

Tip: For complex analysis, create a query, run it, then iterate based on results.

Example Workflow

User: /csv-to-sql Inbox/vendor-rfi-responses.csv

Claude:
1. Runs: node scripts/csv-to-sqlite.js "..." --dry-run --verbose
2. Identifies start-row needed (14)
3. Runs: node scripts/csv-to-sqlite.js "..." --start-row 14 --fts --verbose
4. Verifies: sqlite3 .data/... -markdown -header "SELECT * FROM data LIMIT 3;"
5. Reports summary with example queries