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. That gap is why we built a bank statement CSV cleaner. 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.

QuickBooks' two accepted layouts at a glance

QuickBooks Online accepts exactly two shapes of bank CSV, per Intuit's article on formatting CSV files in Excel. Pick the one closest to what your bank already gives you:

Anything else, a Balance column, a Type column or a second date, has to go before the upload. Our 3-column vs 4-column bank CSV guide covers converting between the two and the sign mistakes that flip deposits into payments.

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; our guide to removing report headers and footers has the patterns banks use. 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. In PipeSheets, the Remove Headers/Footers step can drop a fixed number of rows from the end, or rows whose first column matches a pattern, so footers don't need hand-editing every month. Rows above the real header still need deleting first, because PipeSheets reads the first row as the header.

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 — treat empty cells as 0, then subtract Debit from Credit. The result is a single Amount column with the correct sign. (QuickBooks Online also takes the 4-column layout directly, so if you're only importing to QuickBooks you can keep Credit and Debit and leave empty cells blank.)

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 Online's CSV upload has no field for a running balance: its two layouts are Date, Description, Amount or Date, Description, Credit, Debit. Drop the Balance column before you upload; 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. Remove rows that have no amount at all; in QuickBooks' 4-column layout, leave the unused Credit or Debit cell blank rather than 0.

"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.

If you'd rather not repeat this by hand every month, create a free account and run the Bank Statement Cleaner on your own export; you'll see a preview of every change before you download, and files up to 25MB are free.

Why Building One Cleanup 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 saved cleanup 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 cleanup 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.