Load CSV to MSSQL

A production-ready Python application for loading CSV files into Microsoft SQL Server with dynamic metadata removal, flexible configuration, and filename sanitization.

**Author**: Quentin Casares

Overview

This skill helps you work with the `load2mssql` application, which features:

Architecture

Core Components

1. **Main Loader** (`load_csv_to_mssql.py`)

- Entry point: `CSVToMSSQLLoader` class

- Dataclass-based configuration parsing

- Workflow: Configuration → File Discovery → CSV Processing → Bulk Loading

- Metadata handling via `read_csv_with_metadata_removal()`

2. **Filename Sanitizer** (`filename_sanitizer.py`)

- Standalone, reusable module

- `FilenameSanitizer` class with `SanitizationRules`

- Removes timestamps, dates, special characters

- Converts to PascalCase

- SQL Server compliance validation

3. **Configuration** (`config.yaml`)

- Sections: database, csv_processing, file_selection, table_loading, filename_sanitization, logging

- Supports both Windows and SQL Server authentication

Development Workflow

Setup

When setting up the development environment:

1. Create and activate virtual environment:

```bash

python -m venv venv

source venv/bin/activate # Mac/Linux

venv\Scripts\activate # Windows

```

2. Install dependencies:

```bash

pip install -r requirements.txt

```

Running the Application

Execute with default or custom configuration:

```bash

Default config.yaml

python load_csv_to_mssql.py

Custom configuration

python load_csv_to_mssql.py --config my_config.yaml

```

Testing

Run tests for sanitization logic:

```bash

Test filename sanitization

python test_sanitization.py

Run sanitizer module tests

python filename_sanitizer.py

```

Configuration Guide

Database Authentication

**Windows Authentication (Recommended)**:

```yaml

database:

server: "localhost"

port: 1433 # Optional, defaults to 1433

auth_mode: "trusted"

```

**SQL Server Authentication**:

```yaml

database:

server: "localhost"

port: 1433 # Specify custom port if needed

auth_mode: "sql"

username: "your_username"

password: "your_password"

```

Table Prefix

Add consistent prefixes to all table names:

```yaml

table_loading:

table_prefix: "tbl_" # All tables prefixed with "tbl_"

```

Table Loading Strategies

```yaml

table_loading:

if_exists: "fail" # Error if table exists (safe default)

if_exists: "replace" # Drop and recreate table

if_exists: "append" # Add rows to existing table

```

Filename Sanitization

Enabled by default. Transforms:

Customize via:

```yaml

filename_sanitization:

use_pascal_case: true

custom_patterns: ["_production$"] # Additional regex patterns

custom_replacements:

"cust": "Customer"

```

Common Tasks

Adding Custom Sanitization Patterns

1. Edit `config.yaml` under `filename_sanitization.custom_patterns`

2. Add regex pattern (e.g., `"_production$"`)

3. Test with `python test_sanitization.py`

Creating Indexes Post-Load

```yaml

table_loading:

table_prefix: "tbl_" # If using prefix

create_indexes:

# Use base table names (without prefix)

CustomerAccount: ["CustomerID", "AccountNumber"]

OrderHistory: ["OrderID"]

```

**Index Naming**: Format is `idx_{table}_{column}` (includes prefix if configured)

Handling Schema Changes

Force specific SQL Server data types:

```yaml

table_loading:

dtype_overrides:

Sales:

OrderDate: "DATETIME"

Amount: "DECIMAL(18,2)"

```

Performance Optimization

**For Large Files (>100MB)**:

```yaml

csv_processing:

chunk_size: 50000 # Larger chunks

database:

fast_executemany: true # Bulk insert optimization

```

**For Memory-Constrained Environments**:

```yaml

csv_processing:

chunk_size: 5000 # Smaller chunks

```

Module Reusability

The `filename_sanitizer.py` module can be used standalone:

```python

from filename_sanitizer import FilenameSanitizer, SanitizationRules

Quick usage

sanitizer = FilenameSanitizer()

table_name = sanitizer.sanitize("file_20251114.csv")

Custom rules

rules = SanitizationRules(

use_pascal_case=False,

custom_replacements={"acct": "Account"}

)

sanitizer = FilenameSanitizer(rules)

```

Troubleshooting

Connection Errors

CSV Parsing Errors

Table Exists

Invalid Table Names

Security Notes

Requirements

Key Design Patterns