Examples

E-commerce Product Catalog

Import product data with value mappings to transform status codes and calculated columns for inventory values.

Import and manage product data:

from excel_to_sql import ExcelToSqlite
from excel_to_sql.transformations import CalculatedColumn

sdk = ExcelToSqlite()

# Import products with value mapping
result = sdk.import_excel(
    "products.xlsx",
    "products",
    value_mappings={
        "status": {"1": "In Stock", "0": "Out of Stock"}
    },
    calculated_columns=[
        CalculatedColumn("inventory_value", "quantity * unit_cost")
    ]
)

print(f"✓ Imported {result['rows_imported']} products")

# Export products report
sdk.export_to_excel(
    "inventory_report.xlsx",
    {
        "All Products": "SELECT * FROM products",
        "Low Stock": "SELECT * FROM products WHERE quantity < 10"
    }
)

Customer Data Migration

Migrate customer data with validation rules to ensure email uniqueness and proper format.

Migrate customer data with validation:

from excel_to_sql import ExcelToSqlite
from excel_to_sql.validators import ValidationRule

sdk = ExcelToSqlite()

# Import with validation
result = sdk.import_excel(
    "customers.xlsx",
    "customers",
    validation_rules=[
        ValidationRule("email", "unique"),
        ValidationRule("email", "regex", pattern=r"^[^@]+@[^@]+\.[^@]+$"),
        ValidationRule("email", "not_null"),
        ValidationRule("age", "range", min_value=18, max_value=120)
    ]
)

# Check for validation errors
if result['validation_errors']:
    print(f"⚠️  {len(result['validation_errors'])} validation errors")
    for error in result['validation_errors'][:5]:
        print(f"  Row {error['row']}: {error['message']}")

Order Processing

Process orders with calculated fields for subtotal, tax, and shipping costs based on order weight.

Process orders with calculated fields:

from excel_to_sql import ExcelToSqlite
from excel_to_sql.transformations import CalculatedColumn

sdk = ExcelToSqlite()

# Import orders with calculations
result = sdk.import_excel(
    "orders.xlsx",
    "orders",
    calculated_columns=[
        CalculatedColumn("subtotal", "quantity * unit_price"),
        CalculatedColumn("tax", "subtotal * 0.1"),
        CalculatedColumn("total", "subtotal + tax"),
        CalculatedColumn("shipping", "CASE WHEN weight > 10 THEN 20 ELSE 5 END")
    ]
)

# Generate order report
sdk.export_to_excel(
    "orders_report.xlsx",
    {
        "All Orders": "SELECT * FROM orders ORDER BY order_date DESC",
        "High Value": "SELECT * FROM orders WHERE total > 1000",
        "Recent": "SELECT * FROM orders WHERE order_date >= date('now', '-30 days')"
    }
)

Quality Profiling

Analyze data quality with comprehensive scoring across completeness, uniqueness, validity, and consistency dimensions.

Profile and analyze data quality:

from excel_to_sql import ExcelToSqlite

sdk = ExcelToSqlite()

# Import data
sdk.import_excel("products.xlsx", "products")

# Generate quality report
profile = sdk.profile_table("products")

# Print summary
print(f"Quality Score: {profile['summary']['quality_score']}/100")
print(f"Grade: {profile['summary']['grade']}")

# Show issues
print("\nIssues found:")
for issue in profile['issues'][:10]:
    print(f"  [{issue['severity']}] {issue['column']}: {issue['message']}")

# Export HTML report
sdk.generate_quality_report("products", output="quality_report.html")

Batch Processing

Process multiple Excel files in a loop with error handling for each file.

Process multiple files:

import os
from excel_to_sql import ExcelToSqlite

sdk = ExcelToSqlite()

# Process all Excel files
excel_files = [f for f in os.listdir("data/") if f.endswith(".xlsx")]

for filename in excel_files:
    table_name = filename.split(".")[0]
    try:
        result = sdk.import_excel(f"data/{filename}", table_name)
        print(f"✓ {table_name}: {result['rows_imported']} rows")
    except Exception as e:
        print(f"✗ {table_name}: {e}")

Data Validation Pipeline

Build a complete validation pipeline that combines import validation with quality profiling to ensure data integrity.

Build a validation pipeline:

from excel_to_sql import ExcelToSqlite
from excel_to_sql.validators import ValidationRule
from excel_to_sql.profiling import DataProfiler

sdk = ExcelToSqlite()

# Import with validation
result = sdk.import_excel(
    "data.xlsx",
    "mytype",
    validation_rules=[
        ValidationRule("id", "unique"),
        ValidationRule("id", "not_null"),
        ValidationRule("email", "regex", pattern=r"^[^@]+@[^@]+\.[^@]+$")
    ]
)

# Profile quality
profile = sdk.profile_table("mytype")

# If quality score < 80, generate report
if profile['summary']['quality_score'] < 80:
    print("⚠️  Low quality score detected")
    sdk.generate_quality_report("mytype", output="quality_alert.html")
else:
    print("✓ Quality acceptable")