Bank Report Text file to Excel Model - 2

[admin]

Code: Select all

# Parsing and converting the data from the provided text file to an Excel file


import pandas as pd

# Defining the column headers based on the sample provided
columns = [
    "REPT-NO", "REPT-HEAD", "HEAD DESC", "CGL", "CGL-DESCRIPTION",
    "CURRENCY", "BALANCE", "CURR RATE", "EQUI-INR-BALANCE"
]

# Open the file and read its content
file_path = 'C:\\Users\\Ram Workstation\\Downloads\\BankAudit\\SampleData\\nwsatrbal.txt'

# Initialize a list to hold the parsed data rows
data_rows = []

# Read the file and extract data
with open(file_path, 'r') as file:
    # Skip initial lines until the data start
    for line in file:
        if line.startswith("NWSA01"):
            # Extract and clean the data values from the line
            row = line.split('|')
            # Remove whitespace and newline characters
            row = [item.strip() for item in row]
            data_rows.append(row)

# Convert the list of data rows into a DataFrame
df = pd.DataFrame(data_rows, columns=columns)

# Writing the DataFrame to an Excel file
excel_output_path = "converted_trial_balance.xlsx"
with pd.ExcelWriter(excel_output_path, engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='Trial Balance', index=False)

excel_output_path