excel_to_sqlite.py

import tkinter as tk
from tkinter import ttk, filedialog, messagebox
import threading
import sqlite3
import os
import re
import sys

try:
    import openpyxl
except ImportError:
    import subprocess
    subprocess.check_call([sys.executable, "-m", "pip", "install", "openpyxl"])
    import openpyxl


# ---------------------------------------------------------------------------
# Performance presets
# ---------------------------------------------------------------------------
PRESETS = {
    "Low  (2GB RAM / Old CPU)": {
        "batch_size":  500,
        "cache_mb":    16,
        "description": "Safe and stable. Slower but won't crash weak machines."
    },
    "Medium  (4-8GB RAM)": {
        "batch_size":  2000,
        "cache_mb":    32,
        "description": "Balanced. Good for average office PCs."
    },
    "High  (16GB RAM / i5-i7)": {
        "batch_size":  5000,
        "cache_mb":    64,
        "description": "Fast. Recommended for i5 10th Gen / 16GB+."
    },
    "Ultra  (32GB RAM / i7-i9)": {
        "batch_size":  10000,
        "cache_mb":    128,
        "description": "Maximum speed. Best for high-end machines with 32GB+ RAM."
    },
}

DEFAULT_PRESET = "High  (16GB RAM / i5-i7)"
TARGET_TABLE   = "Data"   # All sheets are always imported into this single table


class ExcelToSQLiteApp:
    def __init__(self, root):
        self.root       = root
        self.root.title("Excel to SQLite Importer")
        self.root.geometry("680x660")
        self.root.resizable(False, False)
        self.root.configure(bg="#f0f2f5")

        self.root.protocol("WM_DELETE_WINDOW", self._on_close)

        self.excel_path  = tk.StringVar()
        self.db_save_path = tk.StringVar()   # Full path: folder + filename + .db
        self.preset_var  = tk.StringVar(value=DEFAULT_PRESET)
        self.stop_flag   = False
        self.running     = False

        self._build_ui()

    # -------------------------------------------------------------------------
    # UI
    # -------------------------------------------------------------------------
    def _build_ui(self):
        style = ttk.Style()
        style.theme_use("clam")
        style.configure("TProgressbar", troughcolor="#ddd", background="#28a745", thickness=22)
        style.configure("TCombobox", font=("Segoe UI", 10))

        # Title
        tk.Label(self.root, text="Excel  ->  SQLite Importer",
                 font=("Segoe UI", 16, "bold"), bg="#f0f2f5", fg="#222").pack(pady=(16, 2))
        tk.Label(self.root,
                 text=f'All sheets are merged into a single table called  "{TARGET_TABLE}"',
                 font=("Segoe UI", 9), bg="#f0f2f5", fg="#28a745").pack()

        # --- Input card ---
        card = tk.Frame(self.root, bg="white", highlightbackground="#dde", highlightthickness=1)
        card.pack(fill="x", padx=28, pady=12)

        # Excel file row
        tk.Label(card, text="Excel File", font=("Segoe UI", 10, "bold"),
                 bg="white").grid(row=0, column=0, sticky="w", padx=16, pady=(14, 2))
        ef_row = tk.Frame(card, bg="white")
        ef_row.grid(row=1, column=0, sticky="ew", padx=16, pady=(0, 10))
        tk.Entry(ef_row, textvariable=self.excel_path, width=48,
                 font=("Segoe UI", 10), relief="solid", bd=1).pack(side="left", ipady=5)
        tk.Button(ef_row, text="Browse", font=("Segoe UI", 9, "bold"),
                  bg="#0d6efd", fg="white", relief="flat", cursor="hand2",
                  padx=10, command=self._browse_excel).pack(side="left", padx=(8, 0), ipady=5)

        # --- Save location row ---
        tk.Label(card, text="Save Database To", font=("Segoe UI", 10, "bold"),
                 bg="white").grid(row=2, column=0, sticky="w", padx=16, pady=(0, 2))
        tk.Label(card, text="Choose folder and filename for the output .db file",
                 font=("Segoe UI", 8), bg="white", fg="#999").grid(row=3, column=0, sticky="w", padx=16)

        db_row = tk.Frame(card, bg="white")
        db_row.grid(row=4, column=0, sticky="ew", padx=16, pady=(4, 14))

        self.db_path_entry = tk.Entry(db_row, textvariable=self.db_save_path, width=48,
                                       font=("Segoe UI", 10), relief="solid", bd=1,
                                       state="readonly", readonlybackground="#f8f8f8")
        self.db_path_entry.pack(side="left", ipady=5)
        tk.Button(db_row, text="Save As...", font=("Segoe UI", 9, "bold"),
                  bg="#6c757d", fg="white", relief="flat", cursor="hand2",
                  padx=8, command=self._browse_save_location).pack(side="left", padx=(8, 0), ipady=5)

        card.columnconfigure(0, weight=1)

        # --- Performance preset card ---
        perf_card = tk.Frame(self.root, bg="white", highlightbackground="#dde", highlightthickness=1)
        perf_card.pack(fill="x", padx=28, pady=(0, 10))

        tk.Label(perf_card, text="Performance Preset", font=("Segoe UI", 10, "bold"),
                 bg="white").grid(row=0, column=0, sticky="w", padx=16, pady=(12, 2))
        tk.Label(perf_card,
                 text="Choose based on your PC specs. Higher = faster but uses more RAM.",
                 font=("Segoe UI", 8), bg="white", fg="#888").grid(row=1, column=0, sticky="w", padx=16)

        combo_row = tk.Frame(perf_card, bg="white")
        combo_row.grid(row=2, column=0, sticky="ew", padx=16, pady=(6, 4))
        self.preset_combo = ttk.Combobox(
            combo_row, textvariable=self.preset_var,
            values=list(PRESETS.keys()), state="readonly", width=36, font=("Segoe UI", 10)
        )
        self.preset_combo.pack(side="left")
        self.preset_combo.bind("<<ComboboxSelected>>", self._on_preset_change)

        self.preset_desc = tk.Label(combo_row,
                                     text=PRESETS[DEFAULT_PRESET]["description"],
                                     font=("Segoe UI", 9, "italic"), bg="white", fg="#28a745")
        self.preset_desc.pack(side="left", padx=(12, 0))

        self.preset_info = tk.Label(perf_card,
                                     text=self._preset_info_text(DEFAULT_PRESET),
                                     font=("Consolas", 9), bg="white", fg="#555")
        self.preset_info.grid(row=3, column=0, sticky="w", padx=16, pady=(2, 12))

        perf_card.columnconfigure(0, weight=1)

        # --- Buttons ---
        btn_frame = tk.Frame(self.root, bg="#f0f2f5")
        btn_frame.pack(pady=6)
        self.start_btn = tk.Button(btn_frame, text="  START  ", font=("Segoe UI", 11, "bold"),
                                    bg="#28a745", fg="white", relief="flat", cursor="hand2",
                                    padx=22, pady=8, command=self._start)
        self.start_btn.pack(side="left", padx=8)
        self.stop_btn = tk.Button(btn_frame, text="  STOP  ", font=("Segoe UI", 11, "bold"),
                                   bg="#dc3545", fg="white", relief="flat", cursor="hand2",
                                   padx=22, pady=8, command=self._stop, state="disabled")
        self.stop_btn.pack(side="left", padx=8)

        # --- Progress card ---
        prog_card = tk.Frame(self.root, bg="white", highlightbackground="#dde", highlightthickness=1)
        prog_card.pack(fill="x", padx=28, pady=8)

        top_row = tk.Frame(prog_card, bg="white")
        top_row.pack(fill="x", padx=14, pady=(10, 2))
        self.sheet_label = tk.Label(top_row, text="Sheet: -", font=("Segoe UI", 9),
                                     bg="white", fg="#555")
        self.sheet_label.pack(side="left")
        self.pct_label = tk.Label(top_row, text="0%", font=("Segoe UI", 9, "bold"),
                                   bg="white", fg="#28a745")
        self.pct_label.pack(side="right")

        self.progress = ttk.Progressbar(prog_card, length=614, mode="determinate")
        self.progress.pack(padx=14, pady=(2, 4))

        self.row_label = tk.Label(prog_card, text="Rows: 0 / 0",
                                   font=("Segoe UI", 9), bg="white", fg="#555")
        self.row_label.pack(anchor="w", padx=14, pady=(0, 8))

        # --- Log console ---
        self.log_text = tk.Text(self.root, height=7, font=("Consolas", 9),
                                 bg="#1e1e1e", fg="#d4d4d4", relief="flat",
                                 state="disabled", wrap="word")
        self.log_text.pack(fill="x", padx=28, pady=(0, 14))

    # -------------------------------------------------------------------------
    # Preset helpers
    # -------------------------------------------------------------------------
    def _preset_info_text(self, key):
        p = PRESETS[key]
        return f"Batch size: {p['batch_size']:,} rows/tx    |    SQLite cache: {p['cache_mb']} MB"

    def _on_preset_change(self, _event=None):
        key = self.preset_var.get()
        self.preset_desc.config(text=PRESETS[key]["description"])
        self.preset_info.config(text=self._preset_info_text(key))

    # -------------------------------------------------------------------------
    # Window close
    # -------------------------------------------------------------------------
    def _on_close(self):
        if self.running:
            self.stop_flag = True
        self.root.after(200, self.root.destroy)

    # -------------------------------------------------------------------------
    # Browse handlers
    # -------------------------------------------------------------------------
    def _browse_excel(self):
        path = filedialog.askopenfilename(filetypes=[("Excel Files", "*.xlsx *.xls")])
        if path:
            self.excel_path.set(path)
            # suggest save path next to the Excel file if not already set
            if not self.db_save_path.get():
                folder      = os.path.dirname(path)
                base_name   = os.path.splitext(os.path.basename(path))[0]
                suggested   = os.path.join(folder, base_name + ".db")
                self.db_save_path.set(suggested)

    def _browse_save_location(self):
        """Open a Save As dialog so the user picks folder + filename for the .db file."""
        # Suggest current value or desktop as starting directory
        current = self.db_save_path.get()
        init_dir  = os.path.dirname(current) if current else os.path.expanduser("~\\Desktop")
        init_file = os.path.basename(current) if current else "database.db"

        path = filedialog.asksaveasfilename(
            title="Save SQLite Database As",
            initialdir=init_dir,
            initialfile=init_file,
            defaultextension=".db",
            filetypes=[("SQLite Database", "*.db"), ("All Files", "*.*")]
        )
        if path:
            # Ensure .db extension
            if not path.lower().endswith(".db"):
                path += ".db"
            self.db_save_path.set(path)

    # -------------------------------------------------------------------------
    # Start / Stop
    # -------------------------------------------------------------------------
    def _start(self):
        excel   = self.excel_path.get().strip()
        db_path = self.db_save_path.get().strip()

        if not excel or not os.path.exists(excel):
            messagebox.showerror("Error", "Please select a valid Excel file.")
            return
        if not db_path:
            messagebox.showerror("Error", "Please choose a save location for the database.")
            return

        preset     = PRESETS[self.preset_var.get()]
        batch_size = preset["batch_size"]
        cache_kb   = preset["cache_mb"] * 1024

        self.stop_flag = False
        self.running   = True
        self.start_btn.config(state="disabled")
        self.stop_btn.config(state="normal")
        self.preset_combo.config(state="disabled")
        self.progress["value"] = 0
        self.log_text.configure(state="normal")
        self.log_text.delete("1.0", "end")
        self.log_text.configure(state="disabled")

        self._log(f"Preset  : {self.preset_var.get()}")
        self._log(f"Batch   : {batch_size:,} rows/tx  |  Cache: {preset['cache_mb']} MB")
        self._log(f"Output  : {db_path}")
        self._log(f"Target  : table '{TARGET_TABLE}'")
        self._log("-" * 60)

        threading.Thread(
            target=self._import,
            args=(excel, db_path, batch_size, cache_kb),
            daemon=True
        ).start()

    def _stop(self):
        self.stop_flag = True
        self._log("Stop requested - finishing current batch...")

    # -------------------------------------------------------------------------
    # Helpers
    # -------------------------------------------------------------------------
    def _log(self, msg):
        def _append():
            self.log_text.configure(state="normal")
            self.log_text.insert("end", msg + "\n")
            self.log_text.see("end")
            self.log_text.configure(state="disabled")
        self.root.after(0, _append)

    def _sanitize(self, name):
        name = re.sub(r"[^a-zA-Z0-9_]", "_", str(name).strip())
        if name and name[0].isdigit():
            name = "_" + name
        return name or "col"

    def _update_progress(self, done, total, sheet_done, sheet_total, pct):
        self.progress["value"] = pct
        self.row_label.config(
            text=f"Rows: {done:,} / {total:,}    |    Current sheet: {sheet_done:,} / {sheet_total:,}"
        )
        self.pct_label.config(text=f"{pct}%")

    # -------------------------------------------------------------------------
    # Core import  (background thread)
    # -------------------------------------------------------------------------
    def _import(self, excel_path, db_path, batch_size, cache_kb):
        try:
            self._log(f"Loading: {os.path.basename(excel_path)}")

            wb     = openpyxl.load_workbook(excel_path, read_only=True, data_only=True)
            sheets = wb.sheetnames
            self._log(f"Sheets  : {', '.join(sheets)}")

            self._log("Counting rows...")
            row_counts = {}
            for sh in sheets:
                row_counts[sh] = max(0, (wb[sh].max_row or 1) - 1)
            total_rows = sum(row_counts.values())
            self._log(f"Total rows: {total_rows:,}")
            self._log("-" * 60)

            conn = sqlite3.connect(db_path, isolation_level=None)
            conn.execute("PRAGMA journal_mode = WAL")
            conn.execute("PRAGMA synchronous  = OFF")
            conn.execute(f"PRAGMA cache_size  = -{cache_kb}")
            conn.execute("PRAGMA temp_store   = MEMORY")
            conn.execute("PRAGMA locking_mode = EXCLUSIVE")

            table_created = False
            insert_sql    = None
            done_rows     = 0

            for sheet_name in sheets:
                if self.stop_flag:
                    break

                self.root.after(0, self.sheet_label.config,
                                {"text": f"Sheet: {sheet_name}  ->  table: {TARGET_TABLE}"})
                sheet_total = row_counts[sheet_name]
                self._log(f">> Sheet: '{sheet_name}'  ({sheet_total:,} rows)")

                ws        = wb[sheet_name]
                iter_rows = ws.iter_rows(values_only=True)

                header = next(iter_rows, None)
                if header is None:
                    self._log(f"   '{sheet_name}' is empty - skipped.")
                    continue

                columns = [self._sanitize(c) if c is not None else f"col{i}"
                           for i, c in enumerate(header)]

                if not table_created:
                    col_defs = ", ".join(f'"{c}" NVARCHAR' for c in columns)
                    conn.execute(
                        f'CREATE TABLE IF NOT EXISTS "{TARGET_TABLE}" '
                        f'(id INTEGER PRIMARY KEY AUTOINCREMENT, {col_defs})'
                    )
                    table_created = True
                    self._log(f"   Table '{TARGET_TABLE}' created with {len(columns)} columns.")

                insert_sql = (
                    f'INSERT INTO "{TARGET_TABLE}" '
                    f'({", ".join(chr(34)+c+chr(34) for c in columns)}) '
                    f'VALUES ({", ".join("?" for _ in columns)})'
                )

                batch      = []
                sheet_done = 0

                conn.execute("BEGIN")

                for row in iter_rows:
                    if self.stop_flag:
                        break

                    batch.append(tuple(str(v) if v is not None else None for v in row))

                    if len(batch) >= batch_size:
                        conn.executemany(insert_sql, batch)
                        sheet_done += len(batch)
                        done_rows  += len(batch)
                        batch       = []
                        conn.execute("COMMIT")
                        conn.execute("BEGIN")

                        pct = int(done_rows / total_rows * 100) if total_rows else 0
                        self.root.after(0, self._update_progress,
                                        done_rows, total_rows, sheet_done, sheet_total, pct)

                if batch:
                    conn.executemany(insert_sql, batch)
                    sheet_done += len(batch)
                    done_rows  += len(batch)

                conn.execute("COMMIT")

                pct = int(done_rows / total_rows * 100) if total_rows else 0
                self.root.after(0, self._update_progress,
                                done_rows, total_rows, sheet_done, sheet_total, pct)
                self._log(f"   Done - {sheet_done:,} rows inserted.")

            conn.close()
            wb.close()

            if self.stop_flag:
                self._log("\nImport stopped by user.")
                self.root.after(0, messagebox.showwarning, "Stopped",
                                "Import was stopped by the user.")
            else:
                self._log(f"\nImport complete!")
                self._log(f"Saved to : {db_path}")
                self._log(f"Table    : {TARGET_TABLE}  |  Total rows: {done_rows:,}")
                self.root.after(0, messagebox.showinfo, "Done",
                                f"Import successful!\n\nSaved to:\n{db_path}\n\nTable: {TARGET_TABLE}\nTotal rows: {done_rows:,}")

        except Exception as exc:
            self._log(f"\nERROR: {exc}")
            self.root.after(0, messagebox.showerror, "Error", str(exc))

        finally:
            self.running = False
            self.root.after(0, self.start_btn.config,    {"state": "normal"})
            self.root.after(0, self.stop_btn.config,     {"state": "disabled"})
            self.root.after(0, self.preset_combo.config, {"state": "readonly"})


# ---------------------------------------------------------------------------
# Entry point
# ---------------------------------------------------------------------------
if __name__ == "__main__":
    root = tk.Tk()
    app  = ExcelToSQLiteApp(root)
    root.mainloop()