🕒 ZKTime Parser

A lightweight, modern Python desktop application designed to convert raw ZKTeco biometric attendance device exports (.xls, .xlsx, .html) into structured, clean daily summary spreadsheets, with optional MySQL database integration.

---

Sample UI

✨ Key Features

• Multi-Format Support: Automatically parses standard Excel files or HTML-disguised exports commonly generated by ZKTeco biometric machines.
• Daily Summary Generation: Fills in missing days and dates to maintain a complete daily record for each employee.
• Smart Late & Shift Tracking:
  • Automatically calculates late minutes based on an 8:00 AM standard arrival time (hides lateness exceeding 1 hour).
  • Automatically deducts a 1-hour lunch break for shifts totaling 5 or more hours.
• Interactive Data Preview: View, search, and filter processed records directly within the app before saving.
• Export Ready: Outputs structured reports directly to clean Excel (.xlsx) files or pushes records straight to a MySQL database.
• Modern GUI: Built with Python's Tkinter with a clean, responsive layout and custom hover styling.

---

🚀 Built With

• Python — Core programming language
• Pandas — Data parsing, transformation, and manipulation
• Openpyxl & Xlrd — Excel spreadsheet reading and writing engines
• PyMySQL — MySQL database connector
• Tkinter & TTK — Graphical User Interface (GUI)

---

📥 Installation & Setup

If you want to run or build the source code locally, follow these steps:

1. Clone the repository:

   git clone [https://github.com/your-username/ZKTime-Parser.git](https://github.com/your-username/ZKTime-Parser.git)
   cd ZKTime-Parser

---

Install dependencies:

Make sure you have Python installed, then run:

  pip install pandas openpyxl xlrd pymysql pyinstaller

Running the Application

To launch the application from the source code, run:

  python main.py

Building the Executable (.exe)

To compile the script into a standalone Windows executable using the provided main.spec configuration:

1. Open your terminal or command prompt in the project folder.
2. Run PyInstaller with the spec file:

  pyinstaller main.spec

Alternatively, you can build it directly using the command line:

pyinstaller --noconsole --onefile --name="ZKTime Parser" --icon=icon/calendar.ico --hidden-import=pymysql --exclude-module matplotlib --exclude-module scipy --exclude-module notebook --exclude-module IPython --exclude-module pytest main.py

3. Find your standalone application inside the newly created dist/ folder.

📖 How to Use

1, Launch ZKTime Parser. 2, Click Browse... to select your raw biometric attendance export file (.xls, .xlsx, or .html). 3, Click Preview Data to inspect, search, and verify the summary table. 4, Click Process & Save to export the structured attendance summary to an Excel file.

---

MySQL Connection & Schema Guide

The application connects to a MySQL database (such as XAMPP) to fetch and merge employee metadata (employees, sections, and positions) with your raw attendance logs.

1. Database Setup Make sure you have a running MySQL server (such as XAMPP, WampServer, or a cloud instance) and create a database:

   CREATE DATABASE zkteco_db;
   USE zkteco_db;

2. Required Table Schema To allow the application to correctly query and link employee records, your database must include the following relational tables::

   -- Sections Table
   CREATE TABLE sections (
       id INT AUTO_INCREMENT PRIMARY KEY,
       section_name VARCHAR(100) NOT NULL
   );

-- Positions Table
   CREATE TABLE positions (
       id INT AUTO_INCREMENT PRIMARY KEY,
       position_name VARCHAR(100) NOT NULL
   );

-- Employees Table (Linked to Sections and Positions)
   CREATE TABLE employees (
       id INT AUTO_INCREMENT PRIMARY KEY,
       user_id VARCHAR(50) NOT NULL,       -- Matches the Biometric Machine Emp ID
       employee_id VARCHAR(50) NOT NULL,
       employee_name VARCHAR(150) NOT NULL,
       section_id INT,
       position_id INT,
       FOREIGN KEY (section_id) REFERENCES sections(id),
       FOREIGN KEY (position_id) REFERENCES positions(id)
   );

3. Connecting via the App GUI

Instead of hardcoding credentials, the application includes a built-in DB Settings modal:

• Click the ⚙️ DB Settings button in the top-right corner of the application window.
• Enter your connection details:
  • Host: localhost (default)
  • Port: 3306 (default)
  • Username: root (default for XAMPP)
  • Password: (leave blank if default XAMPP)
• Click Test & Save Settings. The app will verify the connection and securely save an encrypted configuration file (db_config.json) locally.

Sample UI