I am new to Python. I have some coding experirence, though it is quite old.
I am working on a existing script that places a data frame (a row of data at a time based on the last datetime stamp data was written into the workbook) into one worksheet of the .xlsx workbook. The script works fine.
I created a VBA script to provide graphing of data from another worksheet where the data calculations are done and place that new data (based on the latest date/time timestamp of the data) into an existing chart on another worksheet. I tested the VBA script out and it works fine as well.
Then I used the orginal Python script to write new data into the .xlsm workbook.
This works, though if I try to open the workbook after the Pythoin script has completed to run the macro to graph the data, Excel gives an error that the file is corrupt and not formatted correctly.
I can change the exension of the workbook from .xlsm to .xlsx, open the workbook with out issue and see the new data that was added, and of course the macro is not preserved.
I spent some time reseaching this issue, though have not found a solution that does not, from my view, require some change to the orignal script.
Here is the Python script in use:
I am working on a existing script that places a data frame (a row of data at a time based on the last datetime stamp data was written into the workbook) into one worksheet of the .xlsx workbook. The script works fine.
I created a VBA script to provide graphing of data from another worksheet where the data calculations are done and place that new data (based on the latest date/time timestamp of the data) into an existing chart on another worksheet. I tested the VBA script out and it works fine as well.
Then I used the orginal Python script to write new data into the .xlsm workbook.
This works, though if I try to open the workbook after the Pythoin script has completed to run the macro to graph the data, Excel gives an error that the file is corrupt and not formatted correctly.
I can change the exension of the workbook from .xlsm to .xlsx, open the workbook with out issue and see the new data that was added, and of course the macro is not preserved.
I spent some time reseaching this issue, though have not found a solution that does not, from my view, require some change to the orignal script.
Here is the Python script in use:
import os
import pandas as pd
from dateutil import parser
import datetime
import numpy
import sigfig
from openpyxl import Workbook
import numpy as np
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.message import EmailMessage
import FTPPuller
# Setting this variable to True will populate the calculated totals to the middle sheet in the Excel
# NOT advised if keeping the current formlas on that sheet uncomment the line below and set to True to re-write the entire calcualtion worksheet
overwrite_calculated_BL_sheet = False
write_excel = True # leave set to True to allow writing to Excel workbook
send_alert_emails = False # set to True to send emails
pull_from_ftp = False # set to True to pull data from FTP server
# Upper and lower threshold TDR alert
upper_bound = 0.2
lower_bound = -0.2
# CHANGE VARIABLE TO SET EARLIEST DATE ***********************
# YEAR, MONTH, DAY
date_cutoff = pd.Timestamp(2023,6,1)
# root_sensor_folder_name = "Sensor Data" # 17jul25 Mhyatt, added for testing of script, comment out for test version usage, uncomment for live version usage.
root_sensor_folder_name = "Sensor Data - TEST" # 17jul25 Mhyatt, added for testing of script, comment out for live version usage, uncomment for test version fo script.
# For alerts outside of thresholds - uncomment line below to send emails
# email_recipients = [''] #
def GetMidnightRecords(row):
if str(parser.parse(row['TIMESTAMP']).time()) == "00:00:00":
# print("TRUE")
return row
def ParseLatestDate(row):
value = None
try:
value = parser.parse(str(row))
except:
print(f"Cannot parse {row}")
return value
def send_email(to, subject, message):
print("Sending email...")
msg = EmailMessage()
msg['From'] = '[email protected]'
msg['Subject'] = subject
msg['To'] = ", ".join(email_recipients)
msg.set_content(message)
s = smtplib.SMTP(host='', port="587")
s.starttls()
s.login('')
s.send_message(msg)
s.quit()
print("Email sent.")
def ProcessTDRSensor(sensor_root_folder, raw_folder, output_folder, dat_file_name, output_file_name, baseline_file):
raw_file = os.path.join(raw_folder, dat_file_name)
if pull_from_ftp:
FTPPuller.GetBouldinFTPFile(raw_folder, dat_file_name)
output_file = os.path.join(output_folder, output_file_name)
# Baseline file
tdr_bl_df = pd.read_csv(baseline_file, header = [0,1])
# Data file
tdr_df = pd.read_csv(raw_file, skiprows=1, usecols=range(2062), low_memory=False)
# Drop the Units, etc.. rows
tdr_df = tdr_df.drop(index=[0,1], axis=1)
# Drop unneeded columns
if 'TDR2' in raw_file:
print("Dropping columns - TDR2")
tdr_df.drop(["C2_Averaging","C2_PropVelocity","C2_nPoints","C2_Cable_m","C2_Window_m","C2_Probe_m","C2_ProbeoSet","C2_Mult","C2_oSet","C2_NR_Hz","C2_FilterLevel","C2_Laa"], axis=1, inplace=True)
print(f"TDR2 now has {len(tdr_df.columns)} columns.")
# Filter because of data errors...
tdr_df['TIMESTAMP'] = pd.to_datetime(tdr_df['TIMESTAMP'])
tdr_df = tdr_df[tdr_df['TIMESTAMP'] > date_cutoff]
log_file_path = f'{sensor_root_folder}\{dat_file_name.split(".")[0]}_Timestamp_Log.csv'
# Create a .CSV with just the dates from the file as a record.
current_file_dates = pd.to_datetime(tdr_df['TIMESTAMP']).drop_duplicates()
current_file_dates = (current_file_dates[pd.notnull(current_file_dates)])
# If there is not already a log file, simply spew out the current data.
if not os.path.exists(log_file_path):
current_file_dates.to_csv(log_file_path, index=False, header=False)
# This is what will be put in the import folder. If there are no existing dates, just take everything :)
# new_data = tdr_df
# Dummy for variable w/ no dates
existing_dates = pd.DataFrame([np.nan])
else:
existing_dates = pd.read_csv(log_file_path, header=None)
# 0 is what we get for a "header" when header=None
existing_dates[0] = pd.to_datetime(existing_dates[0])
# Exclude any rows that are in the log as having already been imported
tdr_df = tdr_df[~tdr_df["TIMESTAMP"].isin(list(existing_dates[0]))]
# Now, we need to append these new dates to the existing log CSV
new_dates = current_file_dates[~current_file_dates.isin(list(existing_dates[0]))]
# print(new_dates)
new_dates.to_csv(log_file_path, index=False, header=False, mode="a")
# print("Dataframe after filtering...")
# print(tdr_df)
# Threshold check on ALL ROWS -------------------------------------------------------------------
# Store any thresholds/alerts in a list and send all in a single e-mail
thresholds_exceeded = list()
thresholds_were_exceeded = False
print(f"Checking new data for threshold exceedance: {len(tdr_df.index)} rows...")
for n, row in enumerate(tdr_df.iterrows()):
# Limit to not-yet-processed rows
current_row_date = row[1][0].date() # TIMESTAMP column/value
# Report progress every 100 rows
if n % 100 == 0:
print(f"(Threshold Check) - Processing row: {n}")
try:
new_data = row
# Iterate over each column w/ a value...
for i, value in enumerate(row[1]):
# Skip the Timestamp & Record columns
if i < 2:
continue
raw_value = value
baseline_value = tdr_bl_df.loc[0][i]
new_value = float(raw_value) - float(baseline_value)
##### ALERT CHECK ######
if new_value < lower_bound or new_value > upper_bound:
thresholds_were_exceeded = True
# This will log the TIMESTAMP and the Value
threshold_text = f"***** TDR Threshold exceeded: {row[1][0]}\t{tdr_df.columns[i]}\t{new_value} ******"
# print(threshold_text)
thresholds_exceeded.append(threshold_text)
except Exception as argument:
print(f"WARNING: Error processing row {n}, column {i}:\n{value}\n{argument}")
if thresholds_were_exceeded:
print("The following thresholds were exceeded:")
thresholds_text = '\n'.join(str(x) for x in thresholds_exceeded)
print(thresholds_text)
if send_alert_emails:
send_email(email_recipients, 'TDR Threshold Exceeded!', thresholds_text)
# ------------------------------------------------------------------------------------------------
# Only take the midnight readings...
#tdr1_df = tdr1_df[tdr1_df['TIMESTAMP'].str.contains("00:00:00")]
tdr_df = tdr_df[tdr_df['TIMESTAMP'].dt.time == datetime.time(0)]
# The first 5 rows appear to not read numeric, while the rest do...so we'll misting effect this...
numeric_columns = tdr_df.columns.drop(['TIMESTAMP'])
tdr_df[numeric_columns] = tdr_df[numeric_columns].apply(pd.to_numeric)
# baseline_series = tdr_bl_df.loc[0].drop(['TIMESTAMP','Unnamed: 1_level_0'])
# print(baseline_series)
# print(tdr1_bl_df.columns.values[1][1])
# Make a copy of the dataframe to store the calculated values
calculated_df = tdr_df.iloc[:0].copy()
# Get the existing data from the Excel sheet so we know the latest timestmp, etc..
existing_raw = pd.read_excel(output_file, sheet_name="Raw Data")
existing_dates = existing_raw['TIMESTAMP'].apply(lambda x: ParseLatestDate(x))
existing_dates = existing_dates[pd.notnull(existing_dates)]
# latest_date = max(existing_dates).date()
latest_date = max(existing_dates)
print(f"Latest date in current file: {latest_date}")
# Compare each of these rows to baseline
for n, row in enumerate(tdr_df.iterrows()):
# Limit to not-yet-processed rows
current_row_date = row[1][0].date() # TIMESTAMP column/value
if current_row_date <= latest_date:
print(f"Not checking values for row {n}: {current_row_date}, already in Excel sheet...")
continue
print(f"Processing row: {n}")
new_data = row
for i, value in enumerate(row[1]):
# Skip the Timestamp & Record columns
if i < 2:
continue
raw_value = value
baseline_value = tdr_bl_df.loc[0][i]
new_value = float(raw_value) - float(baseline_value)
# Set the value and round!
new_data[1][i] = sigfig.round(new_value, sigfigs = 4)
# convert to list
new_row = list()
for i, value in enumerate(new_data[1]):
new_row.append(value)
# Above results in each value as another row, we want them all to be column values
# Reshape thus...
reshaped_row = numpy.reshape(new_row,(1,2050))
# Make the row a dataframe (concat below must have either dataframes or series')
df_to_append = pd.DataFrame(reshaped_row, columns=tdr_df.columns)
# Add the row to the output dataframe
calculated_df = pd.concat([calculated_df, df_to_append])
# Get the Baseline info without using multi-index, so that we can just insert it as part of a data frame
tdr_bl_final_df = pd.read_csv(baseline_file)
# Get the "FT" header into the raw data...
# tdr_df['TIMESTAMP'] = tdr_df['TIMESTAMP'].astype('string')
tdr_df['RECORD'] = tdr_df['RECORD'].astype('string')
tdr_bl_final_df['TIMESTAMP'] = tdr_bl_final_df['TIMESTAMP'].astype('string')
# print(tdr1_bl_df.dtypes)
# print(tdr1_df.dtypes)
# print(tdr1_bl_final_df.dtypes)
tdr_bl_final_df.columns = tdr_df.columns
tdr_df['RECORD'] = None
# Filters the dataframe for only dates that don't yet exist in the Excel sheet
tdr_df = tdr_df[tdr_df['TIMESTAMP'] > latest_date]
# raw_data = pd.concat([tdr_bl_final_df, tdr_df], ignore_index=True)
raw_data = pd.concat([existing_raw, tdr_df], ignore_index=True)
# print(raw_data)
# EXCEL OUTPUT
if write_excel:
print("Writing to Excel file...")
with pd.ExcelWriter(output_file, mode="a", engine="openpyxl", if_sheet_exists="replace") as writer:
# print(raw_data)
raw_data.to_excel(writer, sheet_name="Raw Data", index=False)
pass
if overwrite_calculated_BL_sheet:
calculated_df.to_excel(writer, sheet_name="Raw Data-BL", index=False)
if __name__ == '__main__':
# ProcessTDRSensor(f'C:\\{root_sensor_folder_name}\\TDR1\\', f'C:\\{root_sensor_folder_name}\\TDR1\\Raw\\', f'C:\\{root_sensor_folder_name}\\TDR1\\Output\\', 'BITDRCable1.Dat', 'BI TDR1 Calcs.xlsx', f'C:\{root_sensor_folder_name}\TDR1\TDR1_Baseline.csv') Commented out MHyatt 5dec23
# changed the TDR file name to be BI TDR2 Calcs.xlsm mhyatt 30sept25
ProcessTDRSensor(f'C:\\{root_sensor_folder_name}\\TDR2\\', f'C:\\{root_sensor_folder_name}\\TDR2\\Raw\\', f'C:\\{root_sensor_folder_name}\\TDR2\\Output\\', 'BITDRCable2.Dat', '[color=#C0392B]BI TDR2 Calcs.xlsm[/color]', f'C:\{root_sensor_folder_name}\TDR2\TDR2_Baseline.csv')
print("Done!")
# input('Press Enter to continue...')The file name in red is where I changed the workbook extension to .xlsm (thinking it was a fairly easy soltuion since everthing else worked.)
Larz60+ write Oct-06-2025, 07:42 PM:
added bbcode tags.
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.
added bbcode tags.
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.
