Bank transaction CSV cleanup for reconciliation

Separate amount from meaning

A transaction amount should stay numeric, while description, merchant, account, and reference values should remain text. This prevents accidental changes to identifiers that look like numbers.

• Normalize amount separators only in amount columns.
• Keep debit and credit sign conventions consistent across the file.
• Do not remove rows with zero amounts until you know how the bank exports reversals or holds.

Standardize transaction dates

Banks may export posting date, transaction date, and value date separately. Keep them separate and convert each date column to the same ISO format so sorting and matching are predictable.

• Use YYYY-MM-DD for matching against ledgers and accounting tools.
• Check ambiguous dates manually when both day and month are 12 or less.
• Preserve timezone or timestamp fields if the bank provides them.

Preserve the audit trail

Reconciliation workflows need explainability. Download the change log from the cleaner and store it beside the cleaned CSV so reviewers can see which values were normalized.

• Compare original and cleaned row counts before importing.
• Keep duplicate removal disabled if the bank intentionally exports reversal pairs.
• Use reference numbers, dates, and amounts together when matching transactions.

Recommended local workflow

1. Load the bank transaction sample or paste a redacted bank export.
2. Enable whitespace cleanup, null normalization, and date/number normalization.
3. Review highlighted changed cells before downloading.
4. Import a small sample into your accounting tool before processing the full file.