XLS to SQL Converter

A powerful, intelligent, and highly optimized Python tool to seamlessly convert .xlsx, .xls, and .csv files into robust, database-ready SQL scripts.

Powered by the Groq LLM API (Llama 3.3 70B) and Jinja2, this tool automatically reads your messy spreadsheets, infers the relational schema (data types, primary keys, and foreign keys), and generates a fully compliant initialization SQL script containing DDL, DML, and DCL statements.

🚀 Key Features

• Automated Star Schema Normalization: Intelligently identifies categorical data and automatically extracts it into Dimension tables, replacing strings with integer Foreign Keys in the main Fact table.
• Generative AI Schema Inference: Leverages Groq's blazing-fast LLM to analyze data samples and map out the entire relational architecture, confirming primary keys and foreign keys.
• Database-Agnostic Engine: Supports structural mapping and SQL generation for PostgreSQL, MySQL, and SQLite.
• High-Performance Bulk Inserts: Automatically groups DataFrame records into batched INSERT INTO statements (e.g., 1000 rows at a time), ensuring that huge spreadsheets (like 60k+ rows) are processed in seconds.
• Strict Topological Sorting: Dependency graphs ensure that Dimension tables are generated and populated first, guaranteeing Foreign Key constraint integrity.
• Fuzzy Matching & Auto-Sanitization: Normalizes dirty sheet names and intelligently maps the LLM's response back to the original DataFrames even if the AI slightly renames the tables.
• Smart Data Formatting: Safely escapes string characters (e.g., D'Artagnan -> 'D''Artagnan'), correctly parses datetime objects, and translates Pandas NaN/NaT directly into SQL NULL.

📁 Project Structure

xls-to-sql/
├── inputs/                  # Drop your .csv and .xlsx files here
├── output_sql/              # Where the final split SQL scripts will be generated
├── src/
│   ├── dialect_mapper.py    # Maps Pandas native types to SQL Dialect types
│   ├── data_normalizer.py   # Extracts dimensions from flat files to build a Star Schema
│   ├── file_reader.py       # Loads files to memory, sanitizes names, ignores metadata sheets
│   ├── schema_inferencer.py # Prompts the Groq LLM to analyze and infer relationships
│   ├── sql_generator.py     # Jinja2 rendering logic (DDL, DML, DCL)
│   └── templates/           # SQL logic blocks (.sql)
├── main.py                  # Entry point with an interactive CLI menu
├── requirements.txt         # Project dependencies
└── .env.example             # Template for your environment variables

⚙️ Installation

1. Clone the repository:

git clone https://github.com/barizonlucas/xls-to-sql.git
cd xls-to-sql

2. Set up a virtual environment (Recommended):

python3 -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate

3. Install dependencies:

pip install -r requirements.txt

4. Configure your API Key: Create a .env file in the root directory (you can copy .env.example) and add your Groq API key:

GROQ_API_KEY=your_groq_api_key_here

💻 Usage

1. Drop your data files (.csv or .xlsx) inside the inputs/ folder.
2. Run the main script:

python main.py

3. An interactive menu will prompt you to select your target Database dialect:
   • 1 - PostgreSQL / Neon
   • 2 - MySQL
   • 3 - SQLite
4. Watch the magic happen! The script will normalize your data into a Star Schema, request the structural blueprint from Groq, and save the resulting architecture into 4 sequentially numbered SQL scripts inside the output_sql/ directory:
   • 01_ddl_schema.sql: Table creations and Foreign Keys.
   • 02_dml_dimensions.sql: Bulk inserts for dimension tables.
   • 03_dml_facts.sql: Bulk inserts for the main fact table.
   • 04_dcl_permissions.sql: User grants (if applicable).

🛠 Technologies Used

• Python 3.12+
• Pandas & Openpyxl: For robust data extraction and chunking.
• Groq API: For near-instant, high-quality Generative AI schema inferences.
• Jinja2: For dynamic and clean SQL rendering templates.
• Python-Dotenv: For environment variable security.