Bank Report text File to Excel

[admin]

Bank Report text File to Excel

Code: Select all

import pandas as pd

# Define the path to the text file
file_path = '30092020nwsa.txt'

# Read the contents of the file
with open(file_path, 'r') as file:
    lines = file.readlines()

# Data structure to hold parsed data
data = {
    'Code': [],
    'Description': [],
    'Amount': []
}

# Parse lines
for line in lines:
    # Assuming each relevant line follows a consistent format
    # This is a simplified example; adjust the logic according to your file's structure
    if line.strip() and 'CODE' not in line and 'XXXXXX' not in line and line[0].isalpha():
        parts = line.split()
        code = parts[0]
        amount = parts[-1]
        description = " ".join(parts[1:-2])
        
        # Add parsed data to the data structure
        data['Code'].append(code)
        data['Description'].append(description)
        data['Amount'].append(amount)

# Convert to DataFrame
df = pd.DataFrame(data)

# Specify the filename for the Excel file
excel_filename = 'parsed_data.xlsx'

# Write the DataFrame to an Excel file
with pd.ExcelWriter(excel_filename, engine='xlsxwriter') as writer:
    df.to_excel(writer, index=False)

# Provide the path to the saved Excel file
excel_filename