Ok let's add one simple need the "PUT" column to finding the files extension $(.$txt$).$ The data starts with $"\%$AIRFTP" $$put $$lines$.$ here are "PUT": below

PUT $\%$AIRFTP,ESDS,F$,34$ AFF$1.$TXT

PUT $\%$AIRFTP,ESDS,F$,56$ INVHIS$1.$TXT

PUT $\%$AIRFTP,ESDS,F$,49$ UOM$1.$TXT

PUT $\%$AIRFTP,ESDS,F$,71$ ITEM$1.$TXT

PUT $\%$AIRFTP,ESDS,F$,80$ REPORT$1.$TXT

PUT $\%$AIRFTP,ESDS,F$,114$ WARRISS$1.$TXT

PUT $\%$AIRFTP,ESDS,F$,129$ AIRGLD$1.$TXT

PUT $\%$FTPOUT,SAM,FB$,228,228$ DRABFTP.TXT

PUT $\%$DSMFILE,SAM,FB$,90,1440$ FTPALL$1.$DAT

location of file

CD $/$FTP$/$PUBLIC$/$ETAX$/$REFUNDS

PUT $\%$FTPFILE,SAM,FB$,57,5700$ REFUNDS.TXT

CD $/$FTP$/$PUBLIC$/$SECURE$32$

PUT $\%$FTPFILE,SAM,FB$,301,3010$ ETAXREF.TXT

CD $/$FTP$/$PUBLIC$/$SECURE$32$

PUT $\%$FTPFILE,SAM,FB$,301,3010$ ETAXREF.TXT

CD $/$FTP$/$PUBLIC$/$ETAX$/$POSPAY

PUT $\%$FTPFILE,SAM,FB$,133,1330$ STL$\_$ETAX$\_2.$TXT

import re

import pandas as pd

import subprocess

import sys

# Check if tabulate is installed

try:

import tabulate

except ImportError:

# Install tabulate if not found

subprocess.check$\_$call$([$sys$.$executable, $"-$m$",$ "pip", "install", "tabulate"$])$

import tabulate

# Check if openpyxl is installed

try:

import openpyxl

except ImportError:

# Install openpyxl if not found

subprocess.check$\_$call$([$sys$.$executable, $"-$m$",$ "pip", "install", "openpyxl"$])$

import openpyxl

# Define the file path $($using forward slashes$)$

file$\_$path $=\text{'}$C:$/$Users$/$collbb$/$Downloads$/$PANVALET$.$txt$\text{'}$

output$\_$file$\_$path $=\text{'}$C:$/$Users$/$collbb$/$Downloads$/$output$\_$PANVALET.xlsx$\text{'}$

# Read the data from the file

with open$($file$\_$path, $"$r$")$ as file:

data $=$ file.read$()$

# Split the data into lines

lines $=$ data.splitlines$()$

# Initialize an empty list to store the processed data

processed$\_$data $=[]$

# Regular expressions for extracting data

ip$\_$regex $=$ r$"$IP$=([\backslash$d$.]+)"$

path$\_$regex $=$ r$"$CD$\backslash$s$+/$FTP$/$PUBLIC$/(\backslash$S$+)"$

date$\_$regex $=$ r$"(\backslash$d$\{2\}/\backslash$d$\{3\}/\backslash$d$\{4\})"$ # Adjusted date format

job$\_$regex $=$ r$"^($JC$|$AC$)\backslash$w$*"$ # Matches job names starting with $"$JC$"$

# Iterate through the lines and extract the data

i $=0$

while i $<$ len$($lines$)$:

line $=$ lines$[$i$].$strip$()$

if line.startswith$("//$ EXEC FTPBATCH"$)$:

try: # Add try$-$except for error handling

ip$\_$match $=$ re$.$search$($ip$\_$regex, line$)$

ip $=$ ip$\_$match.group$(1)$ if ip$\_$match else None

except AttributeError:

ip $=$ None

print$($f$"$Warning: Could not extract IP on line $\{$i$+1\}")$

ftp$\_$action $=$ "Upload" # Assuming FTPBATCH implies upload action

# Iterate through the lines to find matching jobs

for line in lines:

match $=$ re$.$match$($job$\_$regex, line.strip$())$

if match:

job$\_$data.append$(\{"$JOB$"$: match.group$(0)\})$

# Look ahead for the path and date

j $=$ i $+1$

path $=$ date $=$ None

while j $<$ len$($lines$)$ and not lines$[$j$].$startswith$("*****")$:

try:

path$\_$match $=$ re$.$search$($path$\_$regex, lines$[$j$])$

if path$\_$match:

path $=$ path$\_$match.group$(1)$

except AttributeError:

path $=$ None

print$($f$"$Warning: Could not extract path on line $\{$j$+1\}")$

try:

date$\_$match $=$ re$.$search$($date$\_$regex, lines$[$j$])$

if date$\_$match:

date $=$ date$\_$match.group$(1)$

except AttributeError:

date $=$ None

print$($f$"$Warning: Could not extract date on line $\{$j$+1\}")$

j $+=1$

# Extract job information $($search within a window of lines$)$

job $=$ None

for k in range$($i $+1,$ min$($i $+5,$ len$($lines$)))$: # Search the next $4$ lines

job$\_$match $=$ re$.$search$($job$\_$regex, lines$[$k$].$strip$())$

if job$\_$match:

job $=$ job$\_$match.group$(0)$

break # Stop searching once a job is found

# Append the extracted data to the list

processed$\_$data.append$(\{$

$"$IP$"$: ip$,$

$"$CD $/$FTP$/$PUBLIC$/"$: path,

"Date": date, # Add back if needed

$"$FTP$\_$Action": ftp$\_$action,

"Status": "Success",

"Job": job

$\})$

i $+=1$

# Create a pandas DataFrame from the processed data

df $=$ pd$.$DataFrame$($processed$\_$data$)$

# Print the first $5$ rows of the DataFrame

print$($df$.$head$().$to$\_$markdown$($index$=$False, numalign$=$"left", stralign$=$"left"$))$

# Create an Excel spreadsheet and save the DataFrame

with pd$.$ExcelWriter$($output$\_$file$\_$path, engine$=$"openpyxl"$)$ as writer:

df$.$to$\_$excel$($writer$,$ sheet$\_$name$="$FTP Data", index$=$False$)$