How to Clean Bank Statement CSVs for QuickBooks, Xero, and Wave

Your bank lets you export transactions as CSV. Your accounting software lets you import transactions from CSV. Easy, right? Then you actually try it: the dates are in the wrong format, deposits and withdrawals are in separate columns when QuickBooks wants one, there's a balance column you don't need, the header has summary rows your software can't parse, and the description column is full of cryptic bank codes. Every bank's export is different, and every accounting tool's importer is picky in its own way.

Why Bank CSVs Don't Just Work

Banks design their CSV exports for humans opening them in Excel, not for software ingestion. They include header rows with the account number and statement period, footer rows with running balances, and split debits and credits into separate columns because that's what bank statements look like on paper. Accounting software wants the opposite: a single transactional row format with one Amount column where deposits are positive and withdrawals are negative.

What QuickBooks Online actually expects

What Xero expects

What Wave expects

Wave accepts a four-column format: Date, Description, Amount, and Notes. Wave is more forgiving than QuickBooks about date formats (it auto-detects), but it's strict about amount: a single signed column, no separate debit/credit columns. Wave will silently skip rows it can't parse, which is worse than failing loudly — always verify the imported count matches your statement.

Bank-by-Bank: What You'll See in Each Export

Chase

Chase CSV exports use seven columns: Details, Posting Date, Description, Amount, Type, Balance, Check or Slip #. The Amount column has the right sign convention already (deposits positive, withdrawals negative), which is good. The Details column ('DEBIT' or 'CREDIT') is redundant with the sign on Amount and should be dropped. The Posting Date uses MM/DD/YYYY format. Chase exports don't have header summary rows in the CSV (only the column headers), which makes them easier to clean than most.

Chase raw export:
Details,Posting Date,Description,Amount,Type,Balance,Check or Slip #
DEBIT,05/15/2026,STARBUCKS #1234,-4.75,ACH_DEBIT,2847.33,
CREDIT,05/14/2026,DEPOSIT,1500.00,ACH_CREDIT,2852.08,

QuickBooks-ready:
Date,Description,Amount
05/15/2026,Starbucks #1234,-4.75
05/14/2026,Deposit,1500.00

Wells Fargo

Wells Fargo's CSV is famously minimal: five unnamed columns. No header row. The columns are: Date, Amount, asterisk-padded code, blank, Description. Half the work with Wells Fargo is just figuring out which column is which. Once you add headers, the date format is MM/DD/YYYY and the Amount sign is correct (deposits positive). Drop the third column entirely — it's an internal Wells Fargo code that accounting software can't use.

Bank of America

BoA's CSV starts with a summary block: Account, Period, Beginning Balance, Ending Balance — each on its own row before the transaction table. Importers choke on these rows. You have to delete the entire summary block before the column headers begin. BoA also includes a 'Running Bal.' column that QuickBooks and Xero don't want. Date format is MM/DD/YYYY, Amount has the right sign convention.

Capital One, US Bank, PNC, and others

Most smaller and regional banks use one of two formats: signed single-column amount (like Chase) or split debit/credit columns. If you have separate Debit and Credit columns, you'll need to consolidate them: subtract the Debit value from the Credit value into a single signed Amount column. Capital One uses a Debit Amount and Credit Amount split; Citi uses a single signed column. Always check before assuming.

Step 1: Strip the Header and Footer Junk

Open the CSV in a text editor (not Excel — Excel will reformat dates and lose leading zeros). Delete every row above the column header row. Delete every row below the last transaction: summary lines like "*** End of Statement ***," running totals, and disclaimers. The file should start with the column headers and end with the last transaction.

Step 2: Consolidate Debits and Credits

If your bank gives you separate Debit and Credit columns, combine them into one signed Amount column. The standard convention: deposits (credits) are positive, withdrawals (debits) are negative. In a spreadsheet, the formula is straightforward — replace empty cells with 0, then subtract Debit from Credit. The result is a single Amount column with the correct sign.

Split debit/credit:
Date,Description,Debit,Credit
05/15/2026,Coffee,4.75,
05/14/2026,Deposit,,1500.00

Consolidated signed amount:
Date,Description,Amount
05/15/2026,Coffee,-4.75
05/14/2026,Deposit,1500.00

Step 3: Drop the Running Balance

QuickBooks and Xero both expect a Balance column to be optional. If you include it, they validate that the running balance is internally consistent (each row's balance = previous balance + amount). If any row's balance is off by a penny — rounding error, missing transaction, anything — the whole import fails. Drop the Balance column unless you have a specific reason to need it; your accounting software will compute its own balances from the transactions.

Step 4: Normalize the Date Format

Match the format your accounting software expects. QuickBooks (US): MM/DD/YYYY. Xero (default): DD/MM/YYYY for non-US users, MM/DD/YYYY for US. Wave: auto-detects but prefers MM/DD/YYYY. If your bank exports as YYYY-MM-DD (ISO), convert. If you have timestamps (05/15/2026 14:32:05), strip the time component — most importers reject mixed date/datetime formats.

Step 5: Clean the Description Column

Bank descriptions are full of noise: internal transaction codes, location IDs, repetitive prefixes like "DEBIT POS PURCHASE." These don't help you categorize transactions in your accounting software. Use find-and-replace to strip common prefixes, normalize merchant names ("STARBUCKS #1234" → "Starbucks"), and trim trailing whitespace. Cleaner descriptions mean better auto-categorization in QuickBooks and Xero.

Common Errors and What They Mean

"Date column has invalid values"

Some rows have a different date format than the others. This usually happens when a date column has a mix of MM/DD/YYYY and MM/DD/YY values, or when one row has "Pending" instead of an actual date. Sort by date and look for outliers.

"Amount column contains non-numeric values"

Look for currency symbols, parentheses around negatives, or empty cells. Strip all $ symbols. Convert (50.00) to -50.00. Replace empty cells in Amount with 0 (or remove the row if there's truly no amount).

"Balance column is inconsistent"

Drop the Balance column entirely and re-import. Reconstructing a consistent running balance from a partial export is fragile; let your accounting software do it.

"Too many columns" or "Column count mismatch"

A description field contained a comma and wasn't quoted, so the parser split that row into too many fields. Either properly quote every text field or run the file through a CSV-aware tool that re-quotes the descriptions correctly.

Why Building One Pipeline Per Bank Pays Off

Every month you export the same format from the same bank. The cleanup steps are identical month to month. Building a pipeline once turns a 20-minute manual cleanup into a one-click operation. If you do bookkeeping for multiple clients across different banks, you build one pipeline per bank and reuse it across every client with an account there.

Prevention: Use Direct Bank Feeds Where You Can

QuickBooks Online and Xero both support direct bank feeds for most major US and international banks. If you can connect your bank directly, you avoid the CSV cleanup loop entirely. CSV cleanup is for the cases where direct feeds don't exist (smaller banks, business accounts with restricted access) or when you need to import historical data from before you set up the feed.