Bank statement to CSV: how to get a file that actually imports

A CSV is just text: values separated by commas, one transaction per line. That simplicity is why every accounting app accepts it, and also why so many imports fail. The software reading your file has to guess what each column means, which way the signs go, and whether 03/04/2026 is March or April. This guide is about getting those decisions right.

If you need the data in a spreadsheet for analysis rather than an import, start with bank statement PDF to Excel instead.

Step 1: Decide where the CSV is going

There is no single "bank statement CSV format". The right layout depends on the destination:

So the same statement may need two different CSVs: a single signed Amount column for Xero, and either layout for QuickBooks Online. Detailed walkthroughs: QuickBooks and Xero.

Step 2: Get the sign convention right

This causes more bad imports than anything else.

• One Amount column: money out is negative, money in is positive. Intuit's 3-column format and Xero both work this way.
• Two columns: money out in one, money in in the other, both positive numbers. Intuit's 4-column format works this way, and its help page tells you to leave cells that would only contain zero blank and to remove the word "amount" from the Credit and Debit headings (Intuit).
• Credit cards are reversed. On a card account, a payment reduces what you owe. Intuit warns that card CSVs can show payments as negative and says to choose the credit card account, not a bank account, when uploading (Intuit).

Watch for statements that print withdrawals with a minus sign inside a "withdrawals" section. If your conversion also negates the withdrawal column, you get a double negative and every expense imports as income.

Step 3: Make every date unambiguous

04/05/2026 is 4 May in the UK and India and April 5 in the US. A CSV can't say which it means. Rules:

• Use one date format for the whole file. Intuit recommends dd/mm/yyyy and asks for all dates in the same format (Intuit).
• If a bank adds the weekday (20/11/2022 TUE), split it into its own column; Intuit calls this out specifically.
• Xero accepts many formats (DD/MM/YYYY, MM/DD/YYYY, YYYY/MM/DD, DD-MM-YYYY, DD.MM.YYYY and more) and asks you to confirm when a date could be read either way (Xero Central).
• Danger case: a day-first file mapped as month-first imports without errors for every date where the day is 12 or less, and silently swaps day and month. Spot-check a date like the 3rd of a month after import.

Step 4: Keep amounts plain

• No currency symbols. Intuit's troubleshooting page says to keep amounts "as simple as possible" and switch currency-formatted cells to General (Intuit).
• No thousands separators (1,234.56 becomes two fields in a comma-separated file unless quoted). Indian-style grouping (1,23,456.78) has the same problem.
• Convert accounting negatives (250.00) and trailing minus 250.00- to -250.00.
• Strip Cr/Dr suffixes from balances.

Step 5: Don't let Excel "help"

Opening a CSV by double-clicking lets Excel reformat it: long reference numbers become 4.12E+11, leading zeros vanish, and dates flip to your computer's locale. If you need to inspect a CSV in Excel, use Data > From Text/CSV and set the reference and date columns to Text. If you edit and save, check the file in a plain text editor before importing. (Intuit also notes that Mac users must save as a Windows CSV for QuickBooks.)

Step 6: Prove the CSV before you import it

An import screen checks the file's shape, not whether every transaction is there. Before importing:

1. Row count. Count the transactions on the PDF (or at least on the first and last pages) and compare.
2. Totals. opening balance + sum of money in − sum of money out must equal the closing balance on the statement.
3. Spot-check three rows: first, last and one in the middle, against the PDF.

Deleting a bad import is usually more work than this. Our reconciliation guide shows the formulas.

Getting from PDF to CSV

If your bank gives you a CSV, use it, but check it: bank CSVs often drop the running balance, which removes your easiest proof of completeness. If you only have the PDF:

• Excel's Get Data from PDF, then save as CSV (Microsoft Support). Fine for simple, single-table statements.
• A statement converter that outputs CSV directly. Check where it sends your PDF and whether it verifies the result against the statement's balances.

Footed opens the PDF in your browser, sends only the extracted text for parsing (processed in memory, not stored), reconciles the result against the statement's opening, closing and running balances, and lets you download CSV, Excel or OFX.

FAQ

What columns should a bank statement CSV have? At minimum Date, Description and Amount (signed), or Date, Description, Money in, Money out. Add Balance if you want to verify the file later.

Comma or semicolon? Comma for QuickBooks and Xero. Some European spreadsheet setups default to semicolons because the comma is their decimal separator. Check before importing.

Why does my CSV import show zero transactions? Usually a header row the app didn't recognise, a date format that doesn't match what you selected at mapping, or amounts stored with currency symbols.

Is OFX better than CSV? For accounting software, often yes: OFX carries account details and unique transaction IDs, so the app can map fields automatically and spot duplicates. See the QuickBooks guide for when to choose which.

Need a clean CSV from a PDF? Footed keeps the PDF in your browser, checks every row against the running balance, and exports CSV, Excel or OFX. Free for 5 pages a day.

Published 2026-10-07.