When Your Data Speaks Two Languages: Text Parsing Adventures

import pandas as pd
import re

# Read the text file
df = pd.read_table('trial_balance.txt', header=None, names=['raw'])

# Extract account number using regex
df['Account_Num'] = df['raw'].str.extract(r'(\d{7}\-\d{2}\-\d{6})')

# Forward fill to propagate account number down to detail rows
df['Account_Num'] = df['Account_Num'].ffill()

# Extract description (everything after account number)
df['Account_Desc'] = df['raw'].str.extract(r'\d{7}\-\d{2}\-\d{6}\s{4,}(.*)')

# Forward fill
df['Account_Desc'] = df['Account_Desc'].ffill()

# Extract dates
df['Transaction_Date'] = df['raw'].str.extract(r'(\d{2}/\d{2}/\d{4})')

# Convert to datetime
df['Transaction_Date'] = pd.to_datetime(df['Transaction_Date'], errors='coerce')

# Extract amounts (with optional CR suffix)
df['Amount'] = df['raw'].str.extract(r'([\d,]+\.\d{2}(?:\s+CR)?)')

# Handle credit notation
mask = df['Amount'].str.contains('CR', na=False)
df.loc[mask, 'Amount'] = '-' + df.loc[mask, 'Amount'].str.replace(' CR', '')

# Remove commas and convert to float
df['Amount'] = df['Amount'].str.replace(',', '').astype(float)

# Keep only rows with transaction dates
detail_lines = df.dropna(subset=['Transaction_Date'])

# Alternatively: Filter by pattern
detail_lines = df[df['raw'].str.contains(r'\d{2}/\d{2}/\d{4}', na=False)]

import pandas as pd
import re

# Read raw text file
df = pd.read_table('trial_balance.txt', header=None, names=['raw'])

# Extract all fields using regex
df['Account_Num'] = df['raw'].str.extract(r'(\d{7}\-\d{2}\-\d{6})')
df['Account_Desc'] = df['raw'].str.extract(r'\d{7}\-\d{2}\-\d{6}\s{4,}(.*)')
df['Transaction_Date'] = df['raw'].str.extract(r'(\d{2}/\d{2}/\d{4})')
df['Amount'] = df['raw'].str.extract(r'([\d,]+\.\d{2}(?:\s+CR)?)')

# Forward fill account info (propagate header down to detail rows)
df['Account_Num'] = df['Account_Num'].ffill()
df['Account_Desc'] = df['Account_Desc'].ffill()

# Handle credit notation
mask = df['Amount'].str.contains('CR', na=False)
df.loc[mask, 'Amount'] = '-' + df.loc[mask, 'Amount'].str.replace(' CR', '')
df['Amount'] = df['Amount'].str.replace(',', '', regex=False)

# Convert data types
df['Transaction_Date'] = pd.to_datetime(df['Transaction_Date'], errors='coerce')
df['Amount'] = pd.to_numeric(df['Amount'], errors='coerce')

# Keep only detail lines (with dates)
detail_lines = df.dropna(subset=['Transaction_Date', 'Amount']).copy()

# Clean up
detail_lines = detail_lines[['Account_Num', 'Account_Desc', 'Transaction_Date', 'Amount']]

# Export
detail_lines.to_excel('trial_balance_clean.xlsx', index=False)

print(f"Extracted {len(detail_lines):,} transactions from {len(df)} lines")

import pandas as pd

# Read report
df = pd.read_table('vendor_payments.txt', header=None, names=['raw'])

# Extract vendor number and name
df['Vendor_Num'] = df['raw'].str.extract(r'Vendor:\s+(\d+)')
df['Vendor_Name'] = df['raw'].str.extract(r'Vendor:\s+\d+\s+\-\s+(.*)')

# Extract invoice details
df['Invoice_Num'] = df['raw'].str.extract(r'Invoice:\s+(\S+)')
df['Invoice_Date'] = df['raw'].str.extract(r'Date:\s+(\d{2}/\d{2}/\d{4})')
df['Invoice_Amount'] = df['raw'].str.extract(r'Amount:\s+\$([\d,]+\.\d{2})')

# Forward fill vendor info
df['Vendor_Num'] = df['Vendor_Num'].ffill()
df['Vendor_Name'] = df['Vendor_Name'].ffill()

# Convert data types
df['Invoice_Date'] = pd.to_datetime(df['Invoice_Date'], errors='coerce')
df['Invoice_Amount'] = df['Invoice_Amount'].str.replace(',', '').astype(float)

# Keep only invoice detail lines
invoices = df.dropna(subset=['Invoice_Num']).copy()
invoices = invoices[['Vendor_Num', 'Vendor_Name', 'Invoice_Num', 'Invoice_Date', 'Invoice_Amount']]

print(invoices)

# Extract account number (fixed format)
df['Account'] = df['text'].str.extract(r'(\d{7}\-\d{2}\-\d{6})')

# Extract dollar amounts
df['Amount'] = df['text'].str.extract(r'\$?([\d,]+\.\d{2})')

# Extract dates (MM/DD/YYYY)
df['Date'] = df['text'].str.extract(r'(\d{2}/\d{2}/\d{4})')

# Extract email addresses
df['Email'] = df['text'].str.extract(r'([\w\.-]+@[\w\.-]+\.\w+)')

# Extract header value (appears once per group)
df['Header'] = df['text'].str.extract(r'HEADER:\s+(.*)')

# Propagate down to all detail rows
df['Header'] = df['Header'].ffill()

# Keep only rows that contain dates
df_dates = df[df['text'].str.contains(r'\d{2}/\d{2}/\d{4}', na=False)]

# Keep only rows that start with a number
df_nums = df[df['text'].str.match(r'^\d', na=False)]

# Exclude header/footer lines
df_clean = df[~df['text'].str.contains(r'PAGE|TOTAL|REPORT', case=False, na=False)]

# Split on multiple spaces (2 or more)
df[['Col1', 'Col2', 'Col3']] = df['text'].str.split(r'\s{2,}', expand=True)

# Split on any delimiter
df[['First', 'Last']] = df['Name'].str.split(',', expand=True)

import re

# Test pattern
pattern = r'(\d{7}\-\d{2}\-\d{6})'
test_strings = [
 '1234567-12-123456 CASH ACCOUNT',
 'No account number here',
 ' Beginning Balance',
 '9876543-01-987654 INVESTMENT ACCOUNT'
]

for s in test_strings:
 match = re.search(pattern, s)
 if match:
 print(f"MATCH: {match.group(1)}")
 else:
 print(f"NO MATCH: {s}")

# BAD: Greedy match
pattern = r'Invoice: (.*) Date:'
# Matches: "INV-001 Date: 01/15/2024 Amount: $1,000 Date:" (too much!)

# GOOD: Non-greedy match
pattern = r'Invoice: (.*?) Date:'
# Matches: "INV-001" (stops at first "Date:")

# BAD: Dot matches ANY character
pattern = r'\d.\d' # Matches "1X2", "1 2", "1.2"

# GOOD: Escaped dot matches literal period
pattern = r'\d\.\d' # Matches only "1.2"

# BAD: Crashes if pattern not found
df['Amount'] = df['text'].str.extract(r'(\$[\d,]+\.\d{2})')
df['Amount'] = df['Amount'].astype(float) # ERROR on NaN values

# GOOD: Handle missing gracefully
df['Amount'] = df['text'].str.extract(r'(\$[\d,]+\.\d{2})')
df['Amount'] = pd.to_numeric(df['Amount'], errors='coerce') # NaN for missing