How-to
How to convert a bank statement to CSV
Get bank transactions into CSV from online banking, a PDF or scan, or an OFX or QIF file. Column layouts, date and amount rules, and checking it adds up.
7 min read · Last reviewed · by the stmtai team
The quickest CSV is the one your bank exports. Log in, open the account activity, pick a date range and choose CSV (some banks call it comma delimited or Excel). If the months you need are outside that download window, or you only have PDF statements, you convert: copy the table into a spreadsheet, export from Acrobat, or run the PDF through a converter. Whatever route you take, the CSV should have one row per transaction, one consistent date format, plain decimal amounts, and it should add up to the closing balance printed on the statement.
This guide covers each route, the layout accounting software expects, and the checks that stop a bad file getting into your books.
Why CSV and not Excel
A CSV is plain text: rows of values separated by commas. Every spreadsheet, accounting package and scripting language reads it, and there is nothing hidden in it. An .xlsx file can hold formulas, merged cells and formatting that import tools trip over. If you want a formatted workbook for your own use, take the Excel export; if you are feeding another system, CSV is the safer handoff. The Excel conversion guide covers the spreadsheet side.
Route 1: download from online banking
Most banks let you export transactions from the account activity screen. The button is called Download, Export, or Download account activity, and the format list usually includes CSV alongside QuickBooks (QBO), Quicken (QFX) and sometimes OFX. The Chase guide walks through one bank's version; the pattern is the same elsewhere.
The download window
This is where the bank export runs out. Each bank keeps a different amount of activity available for download: some default to the last 90 days, Wells Fargo lets you search up to 18 months of activity, others go further. Beyond that window the only record is the monthly PDF statement, which banks generally keep available online for several years. So for a tax year that ended eighteen months ago, or for a client who has just handed you a folder of statements, you will be converting PDFs.
What you get
| Good | Not so good |
|---|---|
| Free and comes straight from the bank's records | Column layout differs from bank to bank |
| No conversion errors to check for | Descriptions are sometimes truncated or differ from the PDF |
| Fast for recent months | Limited history |
| Often no opening and closing balance to check against |
That last point matters. A bank CSV is a list of transactions, not a statement. There is no printed closing balance in the file, so if a transaction is missing (it happens with pending items near the range boundaries) you have nothing inside the file to tell you. Compare the sum of the rows against the PDF statement for the same period before relying on it.
Route 2: convert a PDF statement
Copy and paste
Open the PDF, select the transaction table, paste into a spreadsheet, then fix the columns and save as CSV. It works for a page or two. Beyond that the problems pile up: multi-line descriptions land in separate rows, amounts and balances swap columns when the description is long, and negative numbers shown in brackets come through as text. Scanned statements cannot be copied at all because there is no text layer.
Acrobat export
Acrobat Pro has Export PDF to Spreadsheet, with CSV as one of the outputs. On a text-based PDF with a simple grid it does a reasonable job. It struggles when descriptions wrap onto a second line, when the statement has multiple tables per page (cheques listed separately from electronic payments, for example), and with any scanned page. Expect to clean up afterwards.
A converter
A purpose-built converter reads the transaction table, handles wrapped descriptions and scanned pages, and writes the columns you ask for. The feature worth insisting on is arithmetic checking: does the tool take the printed opening balance, apply every row, and confirm it reaches the printed closing balance? stmtai does this for every file and flags the first row where the running total stops matching, so a misread digit shows up as a specific line rather than a total that is off by an unexplained amount. It reads PDF, scanned image, CSV, QIF and OFX statements and exports CSV, Excel, QuickBooks .qbo and Xero CSV; the first five pages are free without an account. Start at /convert/bank-statement-to-csv.
Whatever you use, be careful with free web converters for financial documents. Read what they say about retention and deletion before uploading a statement with your account number on it.
Route 3: convert OFX, QFX or QIF to CSV
If your bank offers OFX or QIF but not CSV, you have structured data already; it just needs reshaping.
OFX (and QFX, which is Quicken's variant) is XML-like. Each transaction is a block with a date, amount, type, ID and name. QIF is older and simpler: each transaction is a few lines starting with a letter code (D for date, T for amount, P for payee, M for memo), ended with a caret.
Options:
- A converter that reads OFX or QIF and writes CSV. stmtai does, applying the same balance check where the file carries a ledger balance.
- A short script. Python's ofxparse library reads OFX; QIF is simple enough to parse in a few lines.
- A text editor and patience, for a dozen transactions at most.
Keep the original OFX or QIF. Its transaction IDs are what let accounting software detect duplicates, and a CSV loses them.
The layout that imports cleanly
Columns
For general use, and for a file you might later feed to accounting software, this is a safe minimum:
Date,Description,Amount,Balance
2026-01-15,AMAZON MKTPLACE,-125.99,4532.01
2026-01-16,PAYROLL DEPOSIT,2500.00,7032.01
2026-01-17,CITY WATER UTILITY,-45.67,6986.34
Balance is optional but useful: it lets you check the file against the statement without a calculator. Remove it before importing into software that wants only three or four columns.
Some systems want money in and money out in separate columns instead of a signed Amount:
Date,Description,Debit,Credit
2026-01-15,AMAZON MKTPLACE,125.99,
2026-01-16,PAYROLL DEPOSIT,,2500.00
Both layouts are valid. Do not mix them in one file.
Dates
| Format | Example | Reads as |
|---|---|---|
| YYYY-MM-DD | 2026-01-15 | Unambiguous everywhere; sorts correctly as text |
| MM/DD/YYYY | 01/15/2026 | US software defaults |
| DD/MM/YYYY | 15/01/2026 | UK, Australia, New Zealand, most of Europe |
Pick one and use it for every row. The failure mode is a file that mixes formats, or a US-format file read by software set to day-first: dates up to the 12th quietly swap month and day, and the rest error. If the destination lets you choose, YYYY-MM-DD avoids the question.
Amounts
- Plain decimals: 1250.00, not $1,250.00 or 1.250,00.
- A leading minus for money out in a signed layout. Brackets, (125.99), are display formatting and will be read as text.
- Two decimal places throughout.
- Blank, not 0, in the unused Debit or Credit cell.
Descriptions
Wrap any description containing a comma in double quotes, or that row will gain an extra column. Most spreadsheet programs do this automatically when saving; hand-built files often do not. Save as UTF-8 so accented characters survive.
Check the file before you use it
A CSV that opens without error is not the same as a CSV that is correct. Three checks, in order:
- Row count. Count the transactions on the statement (many print the number of debits and credits in the summary) and compare with the rows in the file.
- Totals. Sum the Amount column, or the Debit and Credit columns separately, and compare with the statement's total withdrawals and deposits.
- Balance walk. Opening balance plus the sum of the rows should equal the closing balance. If it does not, and the row count matched, a sign is flipped or a digit is wrong. Add a running-balance column and find the first row where it disagrees with the printed balance.
Five minutes here saves an hour of untangling later.
Loading the CSV into common tools
- Excel: Data, then From Text/CSV, rather than double-clicking the file. Double-clicking lets Excel guess column types and it will turn long account numbers into scientific notation and reformat dates.
- Google Sheets: File, Import, upload the file, and choose the separator. Turn off automatic type detection if dates come through wrong.
- QuickBooks Online: three or four columns only, 350 KB and 1,000 rows per file at most. The full requirements are in importing bank transactions into QuickBooks.
- Xero: map the columns on import and confirm the date format. See importing bank statements into Xero.
Fixing the usual problems
- Dates show as text or the wrong month: select the column, Data, Text to Columns, choose Date and the order your file actually uses (MDY, DMY or YMD).
- Amounts will not sum: they are stored as text, often because of a currency symbol or a trailing space. Find and replace the symbol, then multiply the column by 1 or use VALUE().
- Columns shift on some rows: an unquoted comma in a description. Open the file in a text editor, find the row, quote the field.
- Accented characters come through as symbols: the file was not saved as UTF-8, or was opened with the wrong encoding. Re-save as UTF-8 CSV and use the import dialog instead of double-clicking.
- Leading zeros vanish from reference numbers: Excel treated the column as a number. Import with that column typed as text.