Transformations

Value Mappings

Value mappings transform cell values during import, converting codes and abbreviations into human-readable text.

Transform cell values during import.

Basic Value Mapping

Convert numeric or text codes to meaningful values for better data readability.

from excel_to_sql import ExcelToSqlite

sdk = ExcelToSqlite()

# Simple mapping
result = sdk.import_excel(
    "products.xlsx",
    "products",
    value_mappings={
        "status": {
            "1": "Active",
            "0": "Inactive"
        }
    }
)

Multiple Value Mappings

You can define value mappings for multiple columns in a single import operation.

value_mappings = {
    "status": {
        "1": "Active",
        "0": "Inactive",
        "P": "Pending"
    },
    "category_code": {
        "E": "Electronics",
        "H": "Hardware",
        "S": "Software"
    }
}

result = sdk.import_excel(
    "products.xlsx",
    "products",
    value_mappings=value_mappings
)

French Code Translation

Auto-Pilot automatically detects and can translate French operational codes to English.

Auto-detected by Auto-Pilot, but can be manual:

value_mappings = {
    "movement_type": {
        "ENTRÉE": "inbound",
        "SORTIE": "outbound",
        "ACTIF": "active",
        "INACTIF": "inactive"
    }
}

Calculated Columns

Calculated columns create new fields based on SQL expressions, letting you derive values without modifying your source data.

Create new columns based on SQL expressions.

Basic Calculated Column

Derive new values from existing columns using SQL expressions.

from excel_to_sql.transformations import CalculatedColumn

result = sdk.import_excel(
    "orders.xlsx",
    "orders",
    calculated_columns=[
        CalculatedColumn("total", "quantity * price")
    ]
)

Multiple Calculated Columns

Chain multiple calculations together where later columns can reference earlier ones.

calculated_columns = [
    CalculatedColumn("subtotal", "quantity * price"),
    CalculatedColumn("tax", "subtotal * 0.1"),
    CalculatedColumn("total", "subtotal + tax")
]

result = sdk.import_excel(
    "orders.xlsx",
    "orders",
    calculated_columns=calculated_columns
)

String Operations

Use SQL string functions to concatenate, format, and transform text values.

calculated_columns = [
    CalculatedColumn("full_name", "first_name || ' ' || last_name"),
    CalculatedColumn("email", "lower(first_name || '.' || last_name || '@company.com')")
]

Date Operations

Perform date calculations and create boolean flags based on date conditions.

calculated_columns = [
    CalculatedColumn("days_old", "julianday('now') - julianday(order_date)"),
    CalculatedColumn("is_recent", "CASE WHEN order_date >= date('now', '-7 days') THEN 1 ELSE 0 END")
]

Configuration File

Transformations can be defined in your configuration file for reusable import workflows.

Set up transformations in config/mappings.json:

{
  "orders": {
    "target_table": "orders",
    "primary_key": ["id"],
    "column_mappings": {
      "Order ID": {"target": "id", "type": "integer"},
      "Product ID": {"target": "product_id", "type": "integer"},
      "Quantity": {"target": "quantity", "type": "integer"},
      "Price": {"target": "price", "type": "float"}
    },
    "value_mappings": [
      {
        "column": "status",
        "mappings": {"1": "Active", "0": "Inactive"}
      }
    ],
    "calculated_columns": [
      {
        "name": "total",
        "expression": "quantity * price"
      },
      {
        "name": "tax",
        "expression": "total * 0.1"
      },
      {
        "name": "grand_total",
        "expression": "total + tax"
      }
    ]
  }
}

Advanced Transformations

Conditional Mapping

Define default values for null or unmapped values using None as the key.

value_mappings = {
    "priority": {
        "1": "High",
        "2": "Medium",
        "3": "Low",
        None: "Unknown"  # Default for null values
    }
}

Complex Calculations

Use CASE statements and mathematical functions for advanced business logic.

calculated_columns = [
    CalculatedColumn(
        "profit_margin",
        "((price - cost) / price) * 100"
    ),
    CalculatedColumn(
        "shipping_estimate",
        "CASE WHEN weight < 1 THEN 5 WHEN weight < 5 THEN 10 ELSE 20 END"
    )
]

Best Practices

Follow these guidelines for maintainable and understandable transformation logic.

1. Use Descriptive Names

# Good
CalculatedColumn("total_amount", "quantity * unit_price")

# Avoid
CalculatedColumn("x", "a * b")

2. Test Expressions

Always test your SQL expressions before using them in production imports.

# Test your calculated column expressions
df = sdk.query("SELECT quantity, price, quantity * price as total FROM orders LIMIT 5")
print(df)

3. Order Matters

When one calculated column depends on another, define the dependency first.

Calculated columns can reference earlier calculated columns:

# Correct: total depends on subtotal and tax
calculated_columns = [
    CalculatedColumn("subtotal", "quantity * price"),
    CalculatedColumn("tax", "subtotal * 0.1"),
    CalculatedColumn("total", "subtotal + tax")
]