The Great Spreadsheet Migration: Moving 20 Years of Data

import os
import pandas as pd
from pathlib import Path

# Find all Excel files recursively
base_path = Path('/data/historical')
excel_files = []

for ext in ['*.xls', '*.xlsx', '*.xlsb']:
 excel_files.extend(base_path.rglob(ext))

print(f"Found {len(excel_files)} Excel files")

# Create inventory
inventory = pd.DataFrame({
 'File_Path': [str(f) for f in excel_files],
 'File_Name': [f.name for f in excel_files],
 'File_Size_MB': [f.stat().st_size / 1024 / 1024 for f in excel_files],
 'Modified_Date': [pd.Timestamp.fromtimestamp(f.stat().st_mtime) for f in excel_files]
})

# Detect file type from name
inventory['File_Type'] = inventory['File_Name'].apply(lambda x: 
 'Trial_Balance' if 'TB' in x.upper() or 'TRIAL' in x.upper() else
 'Investments' if 'INV' in x.upper() else
 'Contributions' if 'CONTRIB' in x.upper() else
 'Benefits' if 'BEN' in x.upper() else
 'Unknown'
)

inventory.to_excel('file_inventory.xlsx', index=False)

print(inventory['File_Type'].value_counts())

import openpyxl

# Sample 100 files to detect sheet name patterns
sample_files = inventory.sample(100)

sheet_names = []
for file_path in sample_files['File_Path']:
 try:
 wb = openpyxl.load_workbook(file_path, read_only=True, data_only=True)
 sheet_names.extend(wb.sheetnames)
 wb.close()
 except:
 pass

# Count most common sheet names
from collections import Counter
sheet_counts = Counter(sheet_names)

print("Most common sheet names:")
for name, count in sheet_counts.most_common(20):
 print(f" {name}: {count}")

import pandas as pd
import warnings
warnings.filterwarnings('ignore')

def read_excel_robust(file_path):
 """
 Reads an Excel file, trying multiple strategies
 Returns: DataFrame or None
 """
 # Strategy 1: Try common sheet names
 common_sheets = ['Data', 'Trial Balance', 'TB', 'Detail', 'Sheet1']
 
 for sheet in common_sheets:
 try:
 df = pd.read_excel(file_path, sheet_name=sheet)
 if len(df) > 0: # Not empty
 return df, sheet
 except:
 pass
 
 # Strategy 2: Try first sheet
 try:
 df = pd.read_excel(file_path, sheet_name=0)
 if len(df) > 0:
 return df, 'First_Sheet'
 except:
 pass
 
 # Strategy 3: Try reading as CSV (for .csv mislabeled as .xls)
 try:
 df = pd.read_csv(file_path, encoding='latin-1')
 if len(df) > 0:
 return df, 'CSV'
 except:
 pass
 
 return None, None

# Test on sample
results = []
for file_path in inventory['File_Path'].head(100):
 df, sheet = read_excel_robust(file_path)
 results.append({
 'File': file_path,
 'Success': df is not None,
 'Sheet': sheet,
 'Rows': len(df) if df is not None else 0,
 'Columns': len(df.columns) if df is not None else 0
 })

results_df = pd.DataFrame(results)
print(f"Success rate: {results_df['Success'].mean()*100:.1f}%")

from fuzzywuzzy import fuzz

# Standard column names
standard_columns = {
 'Account_Number': ['account', 'acct', 'gl_account', 'account_num', 'account number'],
 'Description': ['desc', 'description', 'account_desc', 'account description', 'name'],
 'Debit': ['debit', 'dr', 'debit_amt', 'debit amount', 'debit_amount'],
 'Credit': ['credit', 'cr', 'credit_amt', 'credit amount', 'credit_amount'],
 'Date': ['date', 'transaction_date', 'trans_date', 'posting_date'],
 'Amount': ['amount', 'amt', 'balance', 'value']
}

def standardize_columns(df):
 """Map column names to standard names using fuzzy matching"""
 column_mapping = {}
 
 for col in df.columns:
 col_lower = str(col).lower().strip()
 
 # Try exact match first
 for standard, variations in standard_columns.items():
 if col_lower in variations:
 column_mapping[col] = standard
 break
 
 # Try fuzzy match (80% threshold)
 if col not in column_mapping:
 best_match = None
 best_score = 0
 
 for standard, variations in standard_columns.items():
 for variation in variations:
 score = fuzz.ratio(col_lower, variation)
 if score > best_score and score > 80:
 best_score = score
 best_match = standard
 
 if best_match:
 column_mapping[col] = best_match
 
 return df.rename(columns=column_mapping)

# Test
test_df = pd.DataFrame(columns=['Acct_Num', 'Desc', 'DR', 'CR'])
standardized = standardize_columns(test_df)
print(standardized.columns.tolist())
# Output: ['Account_Number', 'Description', 'Debit', 'Credit']

import pandas as pd
from tqdm import tqdm # Progress bar

def process_all_files(file_type='Trial_Balance'):
 """Process all files of a given type"""
 # Filter inventory
 files = inventory[inventory['File_Type'] == file_type].copy()
 
 print(f"Processing {len(files)} {file_type} files...")
 
 all_data = []
 failed_files = []
 
 for idx, row in tqdm(files.iterrows(), total=len(files)):
 try:
 # Read file
 df, sheet = read_excel_robust(row['File_Path'])
 
 if df is None:
 failed_files.append(row['File_Name'])
 continue
 
 # Standardize columns
 df = standardize_columns(df)
 
 # Add metadata
 df['Source_File'] = row['File_Name']
 df['Source_Sheet'] = sheet
 df['File_Modified_Date'] = row['Modified_Date']
 
 # Extract date from filename (if possible)
 # Pattern: "TB_01_2024" or "Trial Balance January 2024"
 import re
 date_match = re.search(r'(\d{1,2})[_\s-]?(\d{4})', row['File_Name'])
 if date_match:
 month, year = date_match.groups()
 df['Report_Month'] = int(month)
 df['Report_Year'] = int(year)
 
 all_data.append(df)
 
 except Exception as e:
 failed_files.append(f"{row['File_Name']}: {str(e)}")
 
 # Consolidate
 if all_data:
 combined = pd.concat(all_data, ignore_index=True)
 print(f"\nConsolidated {len(combined):,} rows from {len(all_data)} files")
 print(f"Failed: {len(failed_files)} files")
 
 # Export
 combined.to_parquet(f'{file_type}_consolidated.parquet') # Faster than Excel!
 
 if failed_files:
 pd.DataFrame({'Failed_Files': failed_files}).to_excel(
 f'{file_type}_failed.xlsx', index=False
 )
 
 return combined
 else:
 print("No data extracted!")
 return None

# Process all trial balances
tb_data = process_all_files('Trial_Balance')

# Check for missing months
expected_months = pd.date_range(start='1995-01', end='2024-12', freq='MS')
actual_months = tb_data.groupby(['Report_Year', 'Report_Month']).size().reset_index()
actual_months['Period'] = pd.to_datetime(
 actual_months['Report_Year'].astype(str) + '-' + 
 actual_months['Report_Month'].astype(str).str.zfill(2) + '-01'
)

missing_months = set(expected_months) - set(actual_months['Period'])

print(f"Missing months: {len(missing_months)}")
if missing_months:
 print(sorted(missing_months))

# Check for duplicate months (multiple versions)
duplicates = actual_months[actual_months.duplicated(subset=['Report_Year', 'Report_Month'], keep=False)]

if len(duplicates) > 0:
 print(f"Duplicate months found: {len(duplicates)}")
 print(duplicates[['Report_Year', 'Report_Month', 'Source_File']].head(20))
 
 # Keep most recent file (by modified date)
 tb_data = tb_data.sort_values('File_Modified_Date').drop_duplicates(
 subset=['Report_Year', 'Report_Month', 'Account_Number'],
 keep='last'
 )

# Verify debits = credits for each month
validation = tb_data.groupby(['Report_Year', 'Report_Month']).agg({
 'Debit': 'sum',
 'Credit': 'sum'
}).reset_index()

validation['Difference'] = validation['Debit'] - validation['Credit']
validation['Balances'] = abs(validation['Difference']) < 0.01 # Allow $0.01 rounding

print(f"Months in balance: {validation['Balances'].sum()} / {len(validation)}")

if not validation['Balances'].all():
 print("\nMonths out of balance:")
 print(validation[~validation['Balances']])

# Transform to target format
tb_export = tb_data[[
 'Report_Year',
 'Report_Month',
 'Account_Number',
 'Description',
 'Debit',
 'Credit'
]].copy()

# Create period field (YYYYMM)
tb_export['Period'] = (
 tb_export['Report_Year'].astype(str) + 
 tb_export['Report_Month'].astype(str).str.zfill(2)
)

# Format amounts (2 decimal places, no commas)
tb_export['Debit'] = tb_export['Debit'].round(2)
tb_export['Credit'] = tb_export['Credit'].round(2)

# Reorder columns
tb_export = tb_export[[
 'Period',
 'Account_Number',
 'Description',
 'Debit',
 'Credit'
]]

# Export as pipe-delimited CSV
tb_export.to_csv(
 'trial_balance_MIGRATION.csv',
 sep='|',
 index=False,
 header=True
)

print(f"Exported {len(tb_export):,} rows to trial_balance_MIGRATION.csv")
print(f"File size: {os.path.getsize('trial_balance_MIGRATION.csv') / 1024 / 1024:.1f} MB")

inventory_files.py

read_excel_robust.py

standardize_columns.py

consolidate_files.py

validate_data.py

export_to_new_system.py