Excel ⇄ SQL Server Enrichment Automation

Overview

This lightweight desktop application written in Python automates the enrichment of a customer base stored in an Excel spreadsheet and a SQL Server database.

1. Import an .xlsx file that holds the CPF/CNPJ root (column A) and person type (column B).
2. The data is truncated and re‑inserted into a staging table.
3. A stored procedure updates or generates three result tables.
4. The three tables are exported to a new Excel workbook named <input> ‑ Resultado.xlsx.

Everything happens through a minimal GUI built with CustomTkinter.

---

Features

• One‑screen interface: select file -> enrich -> get results.
• Bulk insert powered by pandas + SQLAlchemy (fast_executemany=True).
• Works with Windows Authentication by default (can be switched to SQL Auth).
• Output workbook created with openpyxl – no Excel installation required.

---

Prerequisites

• Python ≥ 3.10
• A reachable SQL Server instance
• ODBC Driver 17 for SQL Server (or adjust the driver name in the code)

---

Installation

# put the four project files in a folder (or clone the repo)
python -m venv venv
source venv/bin/activate      # Windows: venv\Scripts\activate
pip install -r requirements.txt

---

Configuration

Open backend.py and edit the constants in the CONFIGURE HERE block:

Constant	Meaning
SQL_SERVER	SQL Server host or host\instance
SQL_DATABASE	Target database
TABELA_STAGING	Staging table that will be truncated and re‑filled
STORED_PROCEDURE	Procedure that processes the staging data
TABELA 1 / 2 / 3	Result tables exported to Excel

Need SQL authentication?
Replace trusted_connection=yes with UID=<user>;PWD=<password> inside criar_engine().

---

Running the App

python frontend.py

1. Click Select file and choose an Excel workbook.
2. Press Enrich; the button disables to prevent double clicks.
3. Wait for the dialog showing the success (or error) message.
4. Find the output file in the same folder as the input.

---

How It Works

Step	Action
1	GUI passes the selected file path to backend.processar_enriquecimento()
2	The file is read with pandas (row 2 ↓, columns A & B)
3	Data is bulk‑inserted into the staging table
4	Stored procedure runs (EXEC <your_procedure>)
5	SELECT * from the three tables, then exported to Excel

---

Tech Stack

Library	Purpose
customtkinter	Modern Tkinter widgets & dark theme
pandas	Excel I/O & DataFrame manipulation
SQLAlchemy + pyodbc	SQL Server connectivity
openpyxl	Writing the output workbook

---

Troubleshooting

Symptom	Fix
Driver does not exist	Install the matching ODBC driver and adjust SQL_DRIVER.
GUI freezes for long procedures	The app runs on the main thread for simplicity. If your SP takes minutes, wrap the call in threading.Thread or asyncio to keep the UI responsive.
Permission errors	Ensure the login used for the connection can TRUNCATE, INSERT and EXEC the procedure.

---

Author

Leonardo Grupioni

License

MIT — Free to use, modify and distribute. No warranties.