.dat file to xlsxl

[buran]

I bit different from what I initially suggested but in my opinion more robust solution

import pandas as pd
from datetime import datetime
import time


def read_data(dat_file, last_update):
    df = pd.read_table(dat_file, delimiter=',', parse_dates=[1,], low_memory=False, skiprows=1)
    df.drop("RECNBR", axis=1, inplace=True)
    df["TMSTAMP"] = pd.to_datetime(df["TMSTAMP"])
    cols = df.columns.drop(labels=["TMSTAMP"])
    df[cols] = df[cols].apply(pd.to_numeric, errors='coerce')
    return df[df['TMSTAMP'] > last_update]

def write_xlsx(xlsx_file, start_row, df):
    df.to_excel(xlsx_file, startrow=start_row, index=False)


dat_file = 'foo.dat'
xlsx_file = 'foo.xlsx'
start_row = 0 # change this to actual start_row when run for first time, e.g. if you have 30 recors in the xlsx file this will be 30 (first row is at index 0)
last_update = datetime.strptime('2020-03-25 21:11:00', '%Y-%m-%d %H:%M:%S') # change to last dateime already in xlsx file
while True:
    df = read_data(dat_file=dat_file, last_update=last_update)
    num_rows = df.shape[0] # get number of rows in dataframe
    if num_rows:
        print(f'Writing {num_rows} records to {xlsx_file}')
        write_xlsx(xlsx_file=xlsx_file, start_row=start_row, df=df)
        start_row += num_rows 
        last_update = df.TMSTAMP.iat[-1]
        print(f'Last update in xlsx file is {last_update}, start_row is {start_row}')
    else:
        print('No new data id dat file.')
    time.sleep(60) # wait 1 minute