Ok let's make this one simple need the "JOB" column to print data. The data starts with $"$JC$"$ or $"$AC$"$ lines

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$/$test$1.$txt$\text{'}$

output$\_$file$\_$path $=\text{'}$C:$/$Users$/$collbb$/$Downloads$/$output$\_$test.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$\backslash$S$+)"$ # 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

# 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$)$