Automating Database Monitoring and Excel Monitoring with Power Automate: A Comprehensive Guide (Part-1)

Introduction

Automation is a crucial component of modern technology, enabling businesses to streamline repetitive tasks, save time, and improve efficiency. In this blog, we explore how you can automate your database health checks using Python and JMeter, along with converting .plain files to Excel format using Pandas and OpenPyXL. This guide will walk you through a powerful Python script designed to perform these tasks automatically during specific operational hours.

🔄 Why This Matters

• ⚠️ Avoids the premium Oracle SQL connector in Power Automate
• 🔄 Enables scheduled data collection, file transformation, and alerting using only standard connectors
• 💡 Delivers an affordable, flexible, and maintainable automation pipeline
• 🚀 Brings real-world improvements in monitoring visibility and operational efficiency

---

Why Automate Database Monitoring and File Conversion?

As businesses grow, managing routine checks on databases and processing data files manually can become tedious and error-prone. By automating these processes, you can:

• Reduce human error and manual effort.
• Ensure timely execution of health checks and data conversions.
• Improve productivity and resource management.

This blog post demonstrates a practical automation solution using Python, which can be scheduled to run tasks like database checks and file conversions at regular intervals.

import os
import schedule
import time
from datetime import datetime
import pandas as pd
from openpyxl import load_workbook
from openpyxl.worksheet.table import Table, TableStyleInfo


# Function to check if the current time is within the operational hours
def within_operational_hours():
    # Get the current time and day of the week
    current_time = datetime.now().time()
    current_day = datetime.now().weekday()
    
    # Check if the current time is between 09:00 and 18:00, and it's a weekday (Mon-Fri)
    return (current_time >= datetime.strptime('09:00', '%H:%M').time() and
            current_time <= datetime.strptime('18:00', '%H:%M').time() and
            current_day < 5)


# First Script: Function to run the JMeter task
def run_jmeter():
    # Only run if within operational hours
    if within_operational_hours():
        # Command to run JMeter with the specified .jmx file
        jmeter_command = r"C:\apache-jmeter-3.2\apache-jmeter-3.2\bin\jmeter.bat -n -t C:\apache-jmeter-3.2\apache-jmeter-3.2\bin\examples\CHECK_DATA_TABLE.jmx"
        
        # Execute the JMeter command
        os.system(jmeter_command)
        
        # Log the execution time to a file
        with open("jmeter_log.txt", "a") as log_file:
            log_file.write(f"JMeter task executed at {datetime.now()}.\n")
        print("JMeter task executed.")


# Second Script: Function to convert .plain files to Excel
def convert_plain_to_excel():
    # Define file paths for the input .plain files and output Excel files
    plain_file_paths = [
        r"C:\Plain file\Table1.plain",
        r"C:\Plain file\Table2.plain",
        r"C:\Plain file\Table3.plain"
    ]

    excel_file_paths = [
        r"C:\Plain to Excel\Table1.xlsx",
        r"C:\Plain to Excel\Table2.xlsx",
        r"C:\Plain to Excel\Table3.xlsx"
    ]

    # Define the name of the worksheet where data will be written
    sheet_name = 'DataTable'

    # Loop through each pair of input and output files
    for plain_file_path, excel_file_path in zip(plain_file_paths, excel_file_paths):
        # Read data from the .plain file into a DataFrame
        df = read_plain_file(plain_file_path)
        
        # Write the DataFrame to an Excel file and format it as a table
        write_to_excel(df, excel_file_path, sheet_name)
        print(f"Data from {plain_file_path} has been written to {excel_file_path} in sheet {sheet_name} and formatted as a table.")


# Function to read data from a .plain file and convert it to a DataFrame
def read_plain_file(file_path):
    # Open the .plain file and read all lines
    with open(file_path, 'r') as file:
        lines = file.readlines()
    
    # Extract headers from the first line
    headers = lines[0].strip().split()
    
    # Extract data from the remaining lines
    data = [line.strip().split() for line in lines[1:]]
    
    # Return a DataFrame with the extracted headers and data
    return pd.DataFrame(data, columns=headers)


# Function to write a DataFrame to an Excel file and format the data as a table
def write_to_excel(df, excel_file_path, sheet_name):
    # Write DataFrame to the specified Excel sheet
    df.to_excel(excel_file_path, sheet_name=sheet_name, index=False)
    
    # Load the workbook and select the sheet
    wb = load_workbook(excel_file_path)
    ws = wb[sheet_name]
    
    # Define the range of the table (e.g., A1:C10 based on the DataFrame size)
    table_range = f"A1:{chr(65 + len(df.columns) - 1)}{len(df) + 1}"
    
    # Create a table with a specific style
    tab = Table(displayName="DataTable", ref=table_range)
    style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                           showLastColumn=False, showRowStripes=True, showColumnStripes=True)
    tab.tableStyleInfo = style
    
    # Add the table to the worksheet
    ws.add_table(tab)
    
    # Save the workbook with the formatted table
    wb.save(excel_file_path)


# Schedule the JMeter task to run every 15 minutes
schedule.every(15).minutes.do(run_jmeter)

# Schedule the conversion of .plain files to Excel to run every 16 minutes
schedule.every(16).minutes.do(convert_plain_to_excel)


# Keep the script running to execute scheduled tasks
while True:
    # Check if any scheduled tasks are due to run
    schedule.run_pending()
    
    # Wait for 30 seconds before checking again
    time.sleep(30)

Step-By-Step Breakdown of the Python Script

1. Checking Operational Hours

To ensure that tasks are only executed during business hours (Monday to Friday, 9 AM to 6 PM), the script uses Python’s datetime module to check the current day and time.

2. Running JMeter for Database Health Checks

Using JMeter, the script executes a .jmx file to check the health of your database. The command is run only during operational hours to avoid unnecessary execution.

3. Converting .plain Files to Excel

The script reads data from .plain files and converts them into Excel format using the Pandas and OpenPyXL libraries. This helps convert raw data into a structured format that can be easily analyzed.

4. Scheduling the Automation

The schedule library is used to automate the execution of these tasks at specific intervals (every 15 minutes for JMeter, every 16 minutes for file conversion).

🛠 My 2-Part Solution:

📘 Part 1 – Python-Based Automation
✔ Connects to Oracle Cloud DB
✔ Runs scheduled health checks (work hours only)
✔ Converts .plain logs into formatted .xlsx
✔ Uploads files to SharePoint or OneDrive
🔗 Read Part 1

📗 Part 2 – Power Automate Monitoring Flow
✔ Triggers when Excel is uploaded
✔ Scans values, applies logic
✔ Sends automated alerts via email
✔ Logs actions & manages file movement
🔗 Read Part 2

Conclusion

In this guide, we’ve shown you how to set up a Python script to automate routine database checks and data conversion tasks. This automation solution saves time, reduces human error, and ensures that your reports are always up to date. One of the most significant advantages of this automation framework is the substantial cost savings it delivers compared to relying on Power Automate’s premium Oracle SQL connector. By handling database querying, file conversion, and scheduling entirely within a Python script, the solution eliminates the need for a $15/user/month or $100/flow/month premium license.

Feel free to modify the script to suit your specific needs, and consider integrating additional applications to further optimize your workflows. Happy automating!

---

I hope you found this guide helpful. Please feel free to share your feedback or ask questions in the comments below!