[Python] ETL from excel file to oracle database with same defined table

I’m working on a project where I need to automatically extract data from an Excel file and load it into an Oracle database. I’m using Python, TOAD, Oracle Client, and VS Code. The goal is to trigger the upload as soon as a new Excel file is added to a specific folder. Please assist with a code for this.

import os import pandas as pd import cx_Oracle from time import sleep

oracle_connection_string = 'c##chbz/excelpass@localhost:1521/XE'

#create a connection to the folder where the daily excel report will be populated. folder_path = r'C:\Users\USERR\Desktop\FolderDailyExcels'

#connection between python script and oracle database def connect_to_oracle(): try: connection = cx_Oracle.connect(oracle_connection_string) print("Successfully connected:") return connection except cx_Oracle.DatabaseError as e: print("There was an error connecting to the database:", e) return None connect_to_oracle()

#create a connection to the excel sheet def read_excel(file_path): try: data = pd.read_excel(file_path) return data except Exception as e: print(f"Error reading Excel file: {e}") return None

Now call the function like this:


file_path = r'C:\Users\USERR\Desktop\FolderDailyExcels\Excel1.xlsx' read_excel(file_path)

#Upload Data to Oracle Database def upload_to_oracle(data, Project_table): connection = connect_to_oracle() if connection is None: return

cursor = connection.cursor()

for index, row in data.iterrows():
try:
sql = f"INSERT INTO {Project_table} (FULLNAME, NOOFATTACK, DATEOFATTACK) VALUES 1, :2, :3)"
cursor.execute(sql, (row['column1'], row['column2'], row['column3']))
connection.commit()
except Exception as e:
print(f"Error uploading row {index}: {e}")

cursor.close()
connection.close()


#Monitor folder and automate ETL def monitor_folder(): while True: files = os.listdir(folder_path) for file in files: if file.endswith(".xlsx"): file_path = os.path.join(folder_path, file) print(f"Found new file: {file_path}") data = read_excel(file_path) if data is not None: upload_to_oracle(data, 'Project_table') # Change 'Project_table' to your table name sleep(60) # Check the folder every minute

Continue reading...