Bank Statement CSV Format Explained (With Examples)

TL;DR: There's no single "bank statement CSV" standard. Most files use one of three layouts: date, description and a signed amount; date, description, debit and credit columns; or one of those plus a running balance. Imports fail mostly because of date formats, amounts stored as text, extra header or footer rows, and encoding. Decide the target software's layout first, then build or convert the file to match.

CSV looks simple: values separated by commas, one row per line. In practice, we spend more time fixing CSV files than any other format, because every bank exports a slightly different flavor and every accounting tool expects a slightly different one. This guide explains the moving parts so you can read any statement CSV, convert between layouts, and make it import on the first try.

What CSV actually is

CSV stands for comma-separated values. It's plain text. Each line is a record; fields are separated by commas. If a field contains a comma, a quote or a line break, it's wrapped in double quotes, and any double quote inside it is doubled ("").

A minimal example:

Date,Description,Amount
2026-03-01,"ACME, INC PAYMENT",1500.00
2026-03-02,COFFEE SHOP,-4.75

The second row's description contains a comma, so it's quoted. That's the most important rule, and the one hand-edited files most often break.

There's a formal description of the format (RFC 4180), but real-world files vary. Some use semicolons instead of commas (common in regions where the comma is the decimal separator). Some use tabs. Some have no header row. Your job is to recognize which variant you have.

The three common layouts

Layout A: signed amount

Date,Description,Amount
03/01/2026,Client payment,2400.00
03/03/2026,Office supplies,-84.12

One amount column. Positive for money in, negative for money out (from the account holder's point of view). This is the most compact and the easiest to work with in formulas. Xero's statement import and most budgeting tools prefer it.

Layout B: separate debit and credit

Date,Description,Debit,Credit
03/01/2026,Client payment,,2400.00
03/03/2026,Office supplies,84.12,

Money out in one column, money in in the other, both as positive numbers. This mirrors how many printed statements look, and some people find it easier to read. QuickBooks Online accepts this as its four-column layout (it labels them Credit and Debit; check the order on the mapping screen).

A note on wording: on a bank statement, "debit" means money leaving your account and "credit" means money entering it. That's from the bank's perspective of your account. In double-entry bookkeeping, the bank account in your own ledger works the opposite way (a deposit is a debit to your cash account). Don't let the words trip you up. Look at which column the deposits are in.

Layout C: with running balance

Date,Description,Amount,Balance
03/01/2026,Opening balance,,1000.00
03/01/2026,Client payment,2400.00,3400.00
03/03/2026,Office supplies,-84.12,3315.88

Adds the balance after each transaction. Importers usually ignore it, but it's extremely useful for verification: you can check every single row.

Optional columns you'll see

• Reference / Check number: useful for matching checks and invoices.
• Type: ACH, card, check, wire, fee, transfer. Helps with categorization.
• Payee: a cleaner version of the description.
• Status: posted vs. pending. Filter out pending for reconciliation.
• Category: some banks add their own. Rarely matches your chart of accounts.
• Transaction ID: a unique identifier. Great for de-duplication, but rare in CSVs (more common in OFX/QBO files).
• Currency: for multi-currency accounts.

Date formats

Dates cause more import failures than anything else. The same date can be written many ways:

Format	Example	Notes
ISO	2026-03-14	Unambiguous; best for scripts and databases
US	03/14/2026	Expected by most US software
UK/EU	14/03/2026	Expected by UK, EU and many other regions
Short year	3/14/26	Avoid; ambiguous century
Text month	14-Mar-2026	Readable; some importers reject it

The danger zone is days 1 to 12, where 03/04/2026 could be March 4 or April 3. An importer set to the wrong format will happily accept it and put the transaction in the wrong month. Always check a date after the 12th in your preview, where a swap would be obvious.

Excel adds a twist: when you open a CSV, it converts dates using your computer's regional settings, and when you save, it writes them back in that format. So a file can change format just by being opened and saved. If precision matters, check the saved file in a text editor.

Amount formats

Rules that keep amounts importable:

• Use a period as the decimal separator for US and UK software (unless your software's region expects a comma).
• No currency symbols: 1250.00, not $1,250.00.
• No thousands separators.
• Negative numbers with a leading minus: -45.00, not (45.00) or 45.00-.
• Two decimal places for currencies that use cents.

If your amounts are text in Excel (left-aligned, or a green triangle in the corner), convert them before saving. Text to Columns with no changes, or multiplying by 1, does the trick.

Signs: whose point of view?

Signs are relative. For a bank account, deposits are positive and withdrawals negative. For a credit card, exports differ: some show purchases as positive (increasing what you owe) and payments as negative; others show purchases negative (money out of your pocket).

What matters is what the destination expects. Accounting software importing into a credit card account typically interprets amounts relative to that account, so check two transactions after import: a purchase should increase the card balance, a payment should reduce it. If they're reversed, flip the signs (=-[@Amount]) and re-import.

Encoding and line endings

Save CSVs as UTF-8. Older Windows encodings can garble characters like é, ü or ñ in merchant names. In Excel, use Save As > CSV UTF-8 (Comma delimited).

Some importers dislike the invisible "byte order mark" that Excel adds at the start of UTF-8 files. It's rare, but if a header like Date isn't recognized, that hidden character may be why. Saving from a text editor as "UTF-8 without BOM" fixes it.

Line endings (Windows CRLF vs. Unix LF) rarely matter for modern tools, but if a file shows everything on one line in some program, that's the cause.

CSV versus OFX, QFX, QBO and QIF

Banks usually offer other download formats next to CSV, and converters can produce them too. Here's how they differ, in plain terms.

OFX (Open Financial Exchange) is a structured format designed for financial data. Besides transactions, it carries the account identifier, the account type, the statement date range and, importantly, a unique ID for each transaction. Software uses those IDs to skip transactions it has already imported. That makes OFX far safer than CSV for repeated or overlapping imports.

QFX is a variant of OFX used by Quicken. QBO is a variant used by QuickBooks for its Web Connect imports. Under the hood they're close relatives of OFX with a few product-specific fields, such as an identifier for the financial institution.

QIF (Quicken Interchange Format) is an older plain-text format. Some software still accepts it, but it lacks transaction IDs and is gradually being retired by many tools.

CSV has none of that structure. No account information, no IDs, no date range, just rows. That's its strength (you can open it, edit it and build it from anything) and its weakness (the importer has to guess, and you have to map columns).

Our rule of thumb: use OFX/QBO when importing into software that supports them, especially for ongoing imports. Use CSV when you need to edit, combine or analyze the data, or when the destination only accepts CSV. Our article on QBO vs IIF vs CSV goes into the QuickBooks-specific trade-offs.

Semicolons and decimal commas

If you work with European banks or colleagues, you'll meet CSV files like this:

Datum;Omschrijving;Bedrag
14-03-2026;Supermarkt;-23,45

Semicolons separate fields because the comma is the decimal separator. Excel opens these correctly if your computer's regional settings match; otherwise, everything lands in column A.

To fix it, use Data > From Text/CSV, choose semicolon as the delimiter, and set the locale so -23,45 is read as a number. Before importing into US or UK software, convert the decimals to periods and the delimiter to commas. Don't use find-and-replace blindly on the whole file; you'll also change commas inside descriptions. Convert the amount column specifically.

Tabs, pipes and other delimiters

Occasionally you'll get tab-separated files (often with a .txt or .tsv extension) or pipe-separated files from older systems. The same principles apply: identify the delimiter, import with it explicitly, and save as a proper CSV for the destination. QuickBooks Desktop's IIF format, for instance, is tab-delimited even though it's a text file, which is one reason it's easy to break by editing in the wrong tool.

Layouts expected by popular software

Software	Typical CSV expectation
QuickBooks Online	3 columns (Date, Description, Amount) or 4 (Date, Description, Credit, Debit); you map columns and choose the date format on upload
QuickBooks Desktop	No native CSV import for bank transactions; use QBO (Web Connect) or IIF
Xero	Date and Amount required; Payee, Description, Reference optional; mapped on import
Wave	Date, Description, Amount; mapped on import
Sage Accounting	CSV with date, reference/description and amount(s); mapped on import
FreshBooks	Supports bank connections; file import options vary, so check current help docs

These change occasionally, so always look at the importer's own instructions. Our guides cover each one: QuickBooks Online CSV import errors, Xero CSV import format, and QBO vs IIF vs CSV.

Converting between layouts in Excel

Debit/Credit to signed Amount:

=N([@Credit])-N([@Debit])

N() treats blanks as zero.

Signed Amount to Debit/Credit:

Debit:  =IF([@Amount]<0,-[@Amount],"")
Credit: =IF([@Amount]>0,[@Amount],"")

Add a running balance (opening balance in a named cell Opening):

=Opening+SUM(INDEX([Amount],1):[@Amount])

Normalize dates to ISO for scripts:

=TEXT([@Date],"yyyy-mm-dd")

After converting, copy the result and paste as values before saving to CSV, so the file contains numbers and text rather than formulas.

Validating a CSV before import

Our pre-import checklist:

1. Open the file in a text editor. Confirm the delimiter, the header row and the date format.
2. Check there are no title rows above the header or totals below the data.
3. Confirm amounts are plain numbers.
4. Check a date after the 12th of a month to rule out day/month swaps.
5. Sum the amounts and compare with the statement: opening balance + sum = closing balance.
6. Import into a test or sandbox if your software has one, or import one week first.

Step 5 is the one that catches incomplete data. Our balance checker does it for you.

Getting a CSV from a PDF statement

If you only have PDF statements, you need to convert them first. Copy and paste tends to mangle columns. A statement converter extracts each transaction into a row and checks the math. With SheetStatement's bank statement to CSV converter, you can export Layout A, B or C, or go straight to QuickBooks or Xero format. For scanned paper statements, see our notes on OCR accuracy for scanned statements.

A worked example: fixing a broken file

A client sends this CSV, which QuickBooks Online rejects:

Account Statement March 2026
Date,Details,Money Out,Money In,Balance
1-Mar-26,"Opening Balance",,,"$1,000.00"
3-Mar-26,"PAYMENT, ACME",,"$2,400.00","$3,400.00"
4-Mar-26,"STAPLES",($84.12),,"$3,315.88"
Total,,($84.12),"$2,400.00",

Problems, in order:

1. A title line above the header. Delete it.
2. The opening balance row isn't a transaction. Delete it (note the 1,000.00 for verification).
3. Dates use a two-digit year and text month. Reformat to 03/01/2026.
4. Amounts have currency symbols, commas and parentheses. Convert to plain numbers.
5. Money Out is in parentheses and money in is positive, in separate columns. Either keep two columns (QuickBooks' four-column layout) or convert to one signed amount.
6. A totals row at the bottom. Delete it.

Fixed file:

Date,Description,Amount
03/03/2026,"PAYMENT, ACME",2400.00
03/04/2026,STAPLES,-84.12

Verification: 1,000.00 + 2,400.00 − 84.12 = 3,315.88, matching the last balance. Now it imports.

Our take

Treat CSV as a contract between two systems. Know what the receiving system expects, build exactly that, and verify the totals before importing. Five minutes of checking beats an hour of deleting a bad import and redoing it.

Edge cases that break CSV files

Line breaks inside fields. A description with an embedded line break, if not quoted properly, splits one transaction into two broken rows. If an import complains about the wrong number of columns on a particular line, open the file in a text editor and look at that line.

Encoding. Accented characters (é, ü, ñ) and currency symbols can appear as garbled text if a file saved in one encoding is opened as another. UTF-8 is the safest choice. Some older tools expect a different encoding; if names appear garbled after import, try re-saving.

Leading zeros. Account numbers, sort codes and references lose leading zeros when a spreadsheet treats them as numbers. Format those columns as text before saving, or quote them in the file.

Scientific notation. Long reference numbers may be displayed as 1.23457E+15 and saved that way, losing digits. Format as text before saving.

Trailing delimiters and empty columns. Some exports end every line with a comma, implying an extra empty column. Most tools cope; a few don't.

A mini checklist for any bank CSV

1. One header row with clear column names.
2. One row per transaction, no totals or balance lines.
3. Dates in one unambiguous format.
4. Amounts as plain numbers, with one sign convention.
5. Text fields quoted if they contain commas or line breaks.
6. Saved as UTF-8.
7. Total of amounts equals the statement's movement.

A second worked example: the same transactions, three formats

The same three transactions, laid out for three destinations:

Generic signed:      2026-03-04,DD ELECTRIC CO,-82.10
Debit/credit split:  04/03/2026,DD ELECTRIC CO,82.10,
Type column:         03/04/2026,DD ELECTRIC CO,82.10,DEBIT

All three describe the same payment. The first suits most tools, the second matches many UK statements, and the third appears in some US exports. Knowing which one your destination expects is half the work of a clean import.

FAQ

What is the standard format for a bank statement CSV?

There's no single standard. The most common layouts are date, description and a signed amount; or date, description, debit and credit. Some include a running balance. Match the layout your destination software expects.

Should money out be negative in a CSV?

In a single amount column, yes: money out negative, money in positive from the account holder's point of view. With separate debit and credit columns, both are positive.

Why does my CSV import put transactions in the wrong month?

The importer is reading the date format differently, swapping day and month. Check the date format setting on import and confirm using a date after the 12th of the month.

How do I save a CSV in UTF-8 from Excel?

Use Save As and choose CSV UTF-8 (Comma delimited). This keeps special characters in merchant names readable.

Can QuickBooks Desktop import a bank CSV?

Not natively for bank transactions in the way QuickBooks Online can. Use a QBO (Web Connect) file or, for direct register entries, an IIF file.

How can I tell if my CSV is missing transactions?

Add the opening balance to the sum of all amounts and compare it with the closing balance on the statement. If they differ, rows are missing or wrong.