Parse Legacy Text-Dump Reports

When to use this skill

Use this when a legacy accounting system exports a “report” that is really a formatted text dump, not a table. You’ll see:

• Account header lines (0123456-10-001234 Cash Operating Fund) followed by indented detail lines that don’t repeat the account number
• Repeated page headers, column headings, and blank lines scattered through the file
• Subtotal and total lines mixed in with real transactions
• Everything landing in one column when you open it in Excel, and Text-to-Columns breaking on inconsistent spacing

The pattern: extract structured fields with regex, forward-fill header values down to the detail rows that belong to them, then filter to keep only real transaction rows. Typical sources: trial balance dumps, GL detail reports, AS400/mainframe spool files.

Inputs it expects

• A plain text file (.txt, .prn, or similar) where each line is one report row
• A recognizable pattern for the account/header identifier (e.g., 1234567-12-123456) — grab one example line and write its regex
• A recognizable pattern for detail rows (usually a date like MM/DD/YYYY or an amount)
• The report’s printed grand total, if it has one, for tie-out

Steps

1. Read every line into one column with pd.read_table(..., header=None) — resist the urge to split on whitespace up front.
2. Extract header fields (account number + description) with .str.extract() — only header lines will match; everything else gets NaN.
3. Extract detail fields (date, amount) with their own patterns — only transaction lines will match.
4. Forward-fill the header columns with .ffill() so every detail row inherits the account it sits under.
5. Filter to detail rows only: keep rows where the date (or amount) extraction succeeded. This automatically discards page headers, blank lines, and column-heading rows.
6. Exclude subtotal/total lines explicitly — they often contain amounts and can sneak past the filter.
7. Tie out: transaction count and amount total against the report footer.

Code

import pandas as pd

# Step 1: read the raw dump -- one line per row, one column
raw = pd.read_table('trial_balance.txt', header=None, names=['line'])
raw['line'] = raw['line'].fillna(' ')
lines_in = len(raw)

# Step 2: extract account header fields (only header lines match)
# Pattern for accounts like 0123456-10-001234
raw[['account_num', 'account_desc']] = raw['line'].str.extract(
    r'(\d{7}-\d{2}-\d{6})\s+(.*)'
)

# Step 3: extract detail fields (only transaction lines match)
raw['txn_date'] = raw['line'].str.extract(r'(\d{2}/\d{2}/\d{4})')
raw['amount_text'] = raw['line'].str.extract(r'([\d,]+\.\d{2}(?:\s*CR)?)\s*$')

# Step 4: forward-fill headers down onto their detail rows
raw['account_num'] = raw['account_num'].ffill()
raw['account_desc'] = raw['account_desc'].ffill()

# Step 5: keep only real transaction rows (a date marks a detail line)
txns = raw[raw['txn_date'].notna()].copy()

# Step 6: drop subtotal/total lines that carry a date or amount
is_total_line = txns['line'].str.contains(r'\b(SUB)?TOTAL\b', case=False, regex=True)
subtotals = txns[is_total_line]
txns = txns[~is_total_line]

# Convert amounts (see clean-credit-debit-amounts skill for full notation handling)
amt = txns['amount_text'].str.strip()
mask_cr = amt.str.upper().str.endswith('CR')
amt.loc[mask_cr] = '-' + amt.loc[mask_cr].str[:-2].str.strip()
txns['amount'] = pd.to_numeric(amt.str.replace(',', ''), errors='coerce')

txns = txns[['account_num', 'account_desc', 'txn_date', 'amount']]

# Control totals
print(f"Lines read: {lines_in}")
print(f"Transactions extracted: {len(txns)}")
print(f"Accounts found: {txns['account_num'].nunique()}")
print(f"Amount total: {txns['amount'].sum():,.2f}")
print(f"Subtotal lines excluded: {len(subtotals)}")

txns.to_excel('trial_balance_clean.xlsx', index=False)

Validation (control totals)

• Line accounting. Lines in = transactions kept + header lines + subtotal lines + blank/page-furniture lines. Categorize every line; a residual bucket of “unexplained” lines means your patterns are missing something.
• Tie the amount total to the report’s printed grand total. The whole point of the exercise — txns['amount'].sum() must equal the footer total on the source report to the penny.
• Tie account subtotals. Spot-check: txns.groupby('account_num')['amount'].sum() for two or three accounts against the subtotal lines you excluded. If they match, your forward-fill assigned rows to the right accounts.
• Account count sanity. txns['account_num'].nunique() should equal the number of header lines matched — no account should appear in the output that never appeared as a header.
• No unconverted amounts. Count NaNs in the amount column after conversion; should be zero or fully explained.

Exceptions to surface

Hand these lines to a reviewer:

• Unclassified lines — anything that isn’t a header, detail, subtotal, or known page furniture. These are either a format variant your regex missed or real transactions being silently dropped.
• Detail rows appearing before the first header — forward-fill has nothing to fill from; these rows would carry NaN account numbers and must be resolved manually.
• Subtotal mismatches — any account where the sum of extracted detail rows doesn’t equal the report’s printed subtotal for that account (indicates dropped or double-captured lines).
• Amounts that failed numeric conversion — usually an unusual notation (see the clean-credit-debit-amounts skill) or a description accidentally captured as an amount.
• Wrapped description lines — long account descriptions that spill onto a second line can be misread as detail rows; flag short lines with no date and no amount that sit between a header and its details.