#313 - Python script to copy an excel file, open and extract some information, and add the data to a database

- Python script to handle excel data and populate a database, which is simpler, easier to troubleshoot and maintain and does not fill the drive with error dumps:

import os
import shutil
import urllib.parse
import pandas as pd
from sqlalchemy import create_engine, text

NETWORK_FILE = r"\\remote IP\remote path\excel_file.xlsx"
LOCAL_FILE = r"D:\local path\local_excel_file.xlsx"

DB_SERVER = "DB IP,DB port"
DB_NAME = "DB name"
TABLE_NAME = "DB table"
SCHEMA_NAME = "dbo"
DB_USER = "User"
DB_PASS = "Password"

# These are the metrics as displayed in the excel file
TARGET_METRICS = [
    'Metric 1', 
    'Metric 2', 
    'Metric 3', 
    'Metric 4'
]

def run_import():
    print("Copying remote excel file locally")
    shutil.copyfile(NETWORK_FILE, LOCAL_FILE)

    print("Reading local excel file")
    df = pd.read_excel(LOCAL_FILE, sheet_name=0, usecols="C:D", header=None) #in this case use only information on cols C and D
    df.columns = ["F1","F2"] #these are the names of the cols
    df["F1"] = df["F1"].astype(str).str.strip()
    df_filtered = df[df["F1"].isin(TARGET_METRICS)].dropna()
    
    
    print("Connecting to SQL server")
    encoded_pass = urllib.parse.quote_plus(DB_PASS)
    conn_str = (
    f"Driver={{ODBC Driver 17 for SQL Server}};"
    f"Server={DB_SERVER};"
    f"Database={DB_NAME};"
    f"UID={DB_USER};"
    f"PWD={encoded_pass};"
    f"TrustServerCertificate=yes;"
    )
    params = urllib.parse.quote_plus(conn_str)
    engine = create_engine(f"mssql+pyodbc:///?odbc_connect={params}", fast_executemany=True)
    
    with engine.begin() as conn:
        conn.execute(text(f"TRUNCATE TABLE [{SCHEMA_NAME}].[{TABLE_NAME}]"))    
        print(" Cleared existing table rows ") 
    
    df_filtered.to_sql(
        name=TABLE_NAME,
        con=engine,
        schema=SCHEMA_NAME,
        if_exists="append",
        index=False,)
        
    print("Import completed")
    
if __name__ == "__main__":
        try:
            run_import()
        except Exception as e:
            print(f"Error during import {str(e)}")
            exit(1)

- To continuously execute this script, it is possible to add a task on the task scheduler. Settings: 

- tick the option for "Run with highest privileges", configure for the correct server version, "Run whether user is logged on or not".

- Trigger: daily, on a schedule, repeat every 5mins, indefinitely, enabled.

- Actions: start a program: Script: D:\path\env\Scripts\pythonw.exe

Arguments: D:\path\my_script.py

Monitor for the last run status: 0x0 means success.

- Note that a virtual environment was created on D: drive, which is created with this command: python -m venv D:\path\env

- After creating a virtual environment, test it works, e.g., D:\path\env\Scripts\python.exe D:\path\my_script.py

- Check the SQL driver (e.g., ODBC Driver 17 for SQL Server) with this powershell command Get-OdbcDriver