Bank statement converter: PDF to CSV

You may not need a bank statement converter at all: many banks let you download transactions from online banking as a CSV file, or as QFX or OFX for accounting software, for a date range you choose. When all you have is the PDF, you can import it into Excel, copy it out, or retype it. Each of those takes checking.

Ask the bank first. A download from online banking comes from the bank’s own records, so there is nothing to transcribe. Look for “Download”, “Export” or “Download transactions” next to the account history. If the statement belongs to a client, a transaction export for the same dates is a reasonable thing to ask for alongside the PDF.

Import the PDF into Excel. Excel for Microsoft 365 on Windows can read tables from a PDF: Data > Get Data > From File > From PDF, then pick the table in the Navigator and select Load or Transform Data. Microsoft documents it in its Power Query import guide; the Mac instructions do not list it. Microsoft’s own PDF connector notes warn that rows spread over several lines may not be recognized, which is how many statements print a long description.

Copy and paste from a text PDF. Select the transaction lines in your PDF reader and paste them into a spreadsheet. It works on a PDF downloaded from the bank, not on a scan. Expect to fix:

• descriptions that wrap onto a second line and land in their own row;
• the date, description and amount merged into a single cell;
• minus signs or “CR”/“DR” markers lost, so money in and money out look the same;
• dates printed without a year, like “Aug 3”, which a spreadsheet reads as the current year;
• page headers and running totals pasted in between transactions.

Retype it. For a short statement, typing can be faster than cleaning a paste. Whichever route you take, finish with the reconciliation below: it is how you know no amount was dropped.

Before you convert anything, it helps to know the file is the right document. The bank statement checker tells you whether a file is a usable statement, flags a missing account holder or period date, and catches a balance screenshot or a receipt sent in its place.

This converter exports one format: a CSV file, built in your browser. Excel, Numbers and Google Sheets open it as it is. QuickBooks Online and Xero accept it after a few edits, because each expects its own layout. DokuTrak does not produce an .xlsx, QBO, QFX or OFX file.

What the CSV looks like

Five columns, in this order, one row per transaction line, in the order the statement prints them:

Amounts are plain numbers with a decimal point: no currency symbol, no thousands separator. The file is UTF-8 with a byte-order mark, which is what makes Excel show accented payee names correctly. A description that starts with =, +, - or @ gets a leading apostrophe, so a spreadsheet treats it as text and never as a formula; delete it if it shows up after an import.

Excel and Google Sheets

Double-click the file, or in Excel use Data > Get Data > From File > From Text/CSV. In Google Sheets, use File > Import and upload the CSV. To keep a workbook, save it from Excel as .xlsx.

QuickBooks Online

Intuit’s guide, Manually upload transactions into QuickBooks Online, accepts CSV, QBO and QFX files. A CSV can have three columns (Date, Description, Amount) or four (Date, Description, Credit, Debit). To match the four-column layout:

1. Delete the Balance column.
2. Fill in any empty Date cell from the PDF, and move Credit before Debit if you want the columns in the order Intuit lists them.
3. Keep the dates as they are or change them to one format for the whole file (Intuit’s guide suggests dd/mm/yyyy), and pick that same format when you map the columns.
4. Save the file as CSV (File > Save As > CSV UTF-8), not as a workbook, before you upload it.

The same guide caps an upload at 1,000 lines and 350 KB, and asks for cells that would only hold a zero to be left blank, which is how the converter leaves them.

Xero

Xero Central’s article Import a bank statement in CSV format only needs a Date and an Amount, but the Amount is one column: income positive, expenses negative. It also asks you to delete opening and closing balance columns, because Xero works the balance out from the transactions. So:

1. Fill in any empty Date cell from the PDF; a row without a date will not import.
2. Add an Amount column with =D2-C2 (Credit minus Debit) and fill it down, then delete the Balance column.
3. Change the dates to one of the formats Xero lists, such as DD/MM/YYYY, MM/DD/YYYY or YYYY/MM/DD.
4. Save as CSV, then map Date, Amount and Description on Xero’s import screen.

For the other tools a practice uses around statements, see AI tools for bookkeepers.

A bank statement downloaded from online banking converts best, because the text is already in the file. A photo works too: the image is read by a vision model. A scanned statement saved as a PDF is the weak case. When a PDF carries almost no text, the converter reads only its first page, so the table stops there.

What to do with a scan:

• Ask for the statement as a download from online banking, with the transaction export alongside it if you need the lines.
• If the scan is all there is, convert it page by page as photos or images, and put the tables together yourself. Pages converted on their own rarely reconcile, since the two balances and the lines between them sit on different pages; add the tables together and run the check below by hand. Each page counts toward your three conversions a day.
• Keep photos flat, straight on and in daylight, with the whole page in frame, including the balances.

The other limits, as the tool enforces them:

• 10 MB and 20 pages per file. A longer statement is refused rather than half-read. Split it by month.
• 600 transaction lines. Past that, the table stops and the reconciliation says there is nothing to check against, because a table we cut ourselves cannot be judged complete.
• One account and one currency per file. The converter returns one list of transactions with one opening and one closing balance. A combined statement for several accounts is unlikely to reconcile; split it by account.
• Password-protected PDFs. The converter has no step for a password. Open the file with its password and save an unprotected copy first, if you are allowed to.
• Credit card statements. They are read as statements, but their balance is what is owed, so the check can report that the lines do not add up even when every line is right. Read that table against the statement.
• Not a statement, no table. A receipt, a screenshot of a balance, a pay stub or an invoice returns nothing. To see what a file actually is, run it through the document checker.

Bank statement reconciliation comes down to one sum: the opening balance, plus every credit, minus every debit, should equal the closing balance the statement prints, to the cent. If the numbers meet, no amount is missing or in the wrong column, though a misread date or description can still slip through. If they miss, something was dropped, doubled or put in the wrong column, and the size of the gap tells you where to look.

The converter runs this check for you and shows it under the table, but it is worth knowing how to do it by hand, for a table you built yourself or one from another tool:

1. Find the opening (or beginning) balance and the closing (or ending) balance on the statement. Banks use different labels for the same two figures.
2. Sum the Debit column and the Credit column.
3. Compute opening balance + total credits − total debits.
4. Compare the result with the printed closing balance. A difference of zero means every amount is accounted for.

When there is a gap, read it like this:

• The gap equals one transaction amount. A line is missing, often the first or last one on a page, or a line under a page break.
• The gap is twice a transaction amount. A line landed in the wrong column: a deposit read as a withdrawal, or the reverse.
• The gap equals a line that appears twice. A line was repeated where a page ends and the next one begins.
• The gap divides evenly by 9. Two digits were likely swapped in one amount, 54.00 read as 45.00.
• The table stops early. A page is missing from the file itself. Check the page numbers on the PDF.

The running balance helps you find the spot. Walk down the table and check that each Balance equals the previous Balance plus the credit or minus the debit on that line. The first row where it doesn’t is where the problem starts. The converter transcribes the running balance but does not use it in its own check, so this step is left to you, along with a read of the dates and descriptions against the PDF.

A reconciliation shows the amounts add up to the statement’s own balances. Two errors can cancel each other out, and it says nothing about months you don’t have. If you are collecting statements for a year, the missing bank statement tracker shows which months you still have to ask for, and the monthly bookkeeping checklist lists what else usually comes with them. To reconcile the statement against your books, with deposits in transit and outstanding checks, use a full bank reconciliation template.