Magic Command

Usage

Auto-Pilot mode analyzes your Excel files and automatically generates configuration files with detected patterns, data types, and quality recommendations. Perfect for quick prototyping or exploring new datasets.

excel-to-sql magic --data PATH

Options

Customize how Auto-Pilot analyzes your data and where it saves the generated configuration.

-d, --data TEXT       Directory containing Excel files (default: current directory)
-o, --output TEXT     Output directory for config (default: .excel-to-sql)
--interactive, -i     Interactive guided configuration
--dry-run             Analyze without generating configuration

Examples

Basic Auto-Pilot

Analyze all Excel files in the current directory and generate configuration automatically.

cd /path/to/excel/files
excel-to-sql magic --data .

Interactive Mode

Step through the analysis process and confirm each detection before adding it to the configuration.

excel-to-sql magic --data ./excels --interactive

Dry Run Analysis

Preview what Auto-Pilot would detect without creating any files. Useful for understanding your data before committing.

excel-to-sql magic --data . --dry-run

Custom Output Directory

Save the generated configuration to a specific directory instead of the default .excel-to-sql folder.

excel-to-sql magic --data . --output my-config

What Auto-Pilot Detects

1. Primary Keys

Automatically identifies columns with unique values that can serve as row identifiers.

# Detected: ID column as primary key
primary_key: ["id"]

2. Foreign Keys

Detects relationships between tables by analyzing column names and value distributions.

# Detected: customer_id references customers.id
foreign_keys: [{
  "column": "customer_id",
  "references": "customers.id"
}]

3. Value Mappings

Finds code columns containing abbreviated values that should be translated to human-readable text.

# Detected: Status column with codes 0/1
value_mappings: [{
  "column": "status",
  "mappings": {"0": "Inactive", "1": "Active"}
}]

4. Data Types

Analyzes actual data values to determine the most appropriate SQL data types for each column.

# Detected types from data
column_mappings: {
  "id": {"type": "integer"},
  "price": {"type": "float"},
  "name": {"type": "string"},
  "created_at": {"type": "date"}
}

5. Quality Issues

Scans your data for common quality problems that could affect analysis or reporting accuracy.

Identifies data problems:

• Null values
• Duplicates
• Type mismatches
• Outliers

Interactive Mode

Interactive mode gives you control over the configuration process by letting you review and approve each detection.

Step-by-step guided configuration:

excel-to-sql magic --data . --interactive

You'll be prompted to:

1. Review detected primary keys
2. Confirm value mappings
3. Address quality issues
4. Validate configuration
5. Save final configuration

Output Files

Auto-Pilot creates a comprehensive output directory with analysis results and generated configurations.

Auto-Pilot creates .excel-to-sql/ directory:

.excel-to-sql/
├── mappings.json          # Generated type configurations
├── analysis.json          # Pattern detection results
├── quality_report.json    # Data quality analysis
└── backups/              # Automatic backups

Quality Scoring

Auto-Pilot evaluates data quality across four key dimensions and assigns an overall score from 0 to 100.

Auto-Pilot analyzes data quality across dimensions:

Quality Grades:

• A (90-100): Excellent
• B (75-89): Good with minor issues
• C (60-74): Acceptable with notable issues
• D (0-59): Poor quality

Recommendations

Auto-Prioritizes issues based on their impact on data quality and provides actionable fix suggestions.

Auto-Pilot generates prioritized recommendations:

• HIGH: Critical issues affecting data integrity
• MEDIUM: Quality improvements
• LOW: Nice-to-have optimizations

Auto-Fix Capabilities

Automatically corrects common data issues without manual intervention.

Auto-Pilot can automatically fix:

• French Codes: ENTRÉE→inbound, SORTIE→outbound
• Null Values: Fill with defaults
• Split Fields: Combine redundant columns

Dry Run Mode

Test the analysis without creating any files to understand what Auto-Pilot would detect.

Analyze without making changes:

excel-to-sql magic --data . --dry-run

Shows:

• Detected patterns
• Quality scores
• Recommendations
• (No files created)

Use Cases

Auto-Pilot excels at rapid prototyping and exploration but may not be suitable for production environments with specialized requirements.

Perfect For

• Quick prototyping
• Ad-hoc data imports
• Exploring new datasets
• Learning the tool
• Small to medium datasets

Not Ideal For

• Production deployments
• Complex custom transformations
• Highly specialized business logic
• Performance-critical operations

Next Steps

After Auto-Pilot generates configuration:

1. Review .excel-to-sql/mappings.json
2. Adjust if needed
3. Copy to config/mappings.json
4. Import with: excel-to-sql import --file file.xlsx --type type_name

See Also

• Auto-Pilot documentation - Complete Auto-Pilot guide
• Pattern Detection - Detection algorithms
• Quality Scoring - Quality analysis
• Recommendations - Auto-fix capabilities