main.py

import os
import sqlite3
from dbfread import DBF
from datetime import datetime

def convert_value(value):
    # Check if the value is a date and format it
    if isinstance(value, datetime):
        return value.strftime('%Y-%m-%d')  # Or any other format you prefer
    return value

def create_db_from_dbf(dbf_path, db_path):
    # Open the DBF file
    table = DBF(dbf_path)

    # Connect to SQLite database (it will create if it doesn't exist)
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # Create table in SQLite with the same columns as the DBF file
    column_defs = []
    for field in table.field_names:
        column_defs.append(f'"{field}" TEXT')  # Assuming all columns are TEXT for simplicity
    create_table_query = f'CREATE TABLE IF NOT EXISTS data ({", ".join(column_defs)});'
    cursor.execute(create_table_query)

    # Insert data into SQLite table
    insert_query = f'INSERT INTO data ({", ".join(table.field_names)}) VALUES ({", ".join(["?"] * len(table.field_names))})'
    for record in table:
        # Convert any date values to string format before inserting
        values = [convert_value(record[field]) for field in table.field_names]
        cursor.execute(insert_query, values)

    # Commit and close
    conn.commit()
    conn.close()
    print(f"Database {db_path} created successfully!")

def process_folder(input_folder, output_folder):
    for filename in os.listdir(input_folder):
        if filename.lower().endswith('.dbf'):
            dbf_path = os.path.join(input_folder, filename)
            db_name = os.path.splitext(filename)[0] + '.db'
            db_path = os.path.join(output_folder, db_name)
            create_db_from_dbf(dbf_path, db_path)

if __name__ == "__main__":
    # Set the input folder where .dbf files are located and output folder for .db files
    input_folder = r'C:\path\to\your\dbf\files'
    output_folder = r'C:\path\to\your\output\folder'
    # Make sure the output folder exists
    os.makedirs(output_folder, exist_ok=True)

    # Process all DBF files in the input folder
    process_folder(input_folder, output_folder)