PDF Bank Statement to CSV: What Goes Wrong (Tested)

A bank statement PDF looks like a table, but inside the file it is only text placed at positions on a page. There are no rows and no columns, so any converter has to guess them. We wanted to know how often that guess goes wrong, so we scored ClearConvert's PDF to CSV conversion against statements with a known right answer, found real problems, fixed them, and measured again. This post lists what went wrong, with numbers, what changed, what still goes wrong, and what to check before you trust any converted statement.

How We Tested

We used three kinds of files. First, our own synthetic statements for a fictional "Example Bank", generated by a script so every row and cell was known in advance, in three sizes (30, 120 and 400 transactions), in a realistic version (amounts right-aligned, as statements usually print them) and a control version (amounts left-aligned under their headers). Second, the public synthetic statements of an open benchmark (five of its statements were released at the time, MIT licence; fictional banks, no real customer data) and statements made with an open-source generator (also MIT licence). Third, a frozen "held-out" set of 24 statements that we did not look at while changing the converter: 12 from the generator and 12 of our own, with date formats and languages the converter had not been tuned on.

We compared every CSV with the right answer cell by cell and counted a row as right only if every cell matched. In the default "As printed" mode a minus sign, brackets, CR/DR or a currency symbol kept next to an amount did not count as a difference; in "Plain numbers" mode the comparison was exact. For the public files, which have no published answer, we compared the dates and amounts found in the PDF text with those in the CSV, and checked that the opening balance plus the transactions equals the closing balance. No real bank's own PDF was tested, so the numbers describe how the converter handles layout features, not any particular bank.

Before and After

Share of rows exactly right, in the realistic layout; the ranges cover the sizes we tested. The held-out set shares its vocabulary, such as column labels and month names, with what the converter knows, so treat its 100% as optimistic.

What Went Wrong in the First Version

1. Amounts in the wrong column. Right-aligned amounts landed in the column to their left when they started more than about 5 points left of their header. In the 400-transaction debit and credit test, 167 debits ended up in the description. The status still said "Done", with no warning.
2. Page furniture in the rows. Column-header words and "Page n of N" were glued into the last transaction of each page: 20 of 20 pages in the 20-page test.
3. Signs. Debits written with a trailing minus (100 of 100) or DR (99 of 99) landed in the credit column, with the suffix attached.
4. A transaction read as a header. A first transaction beginning with "Direct debit" was taken for a column header; in one 120-transaction test that left 12.3% of cells right.
5. Side-by-side tables. Two tables next to each other collapsed into one: 60 transactions became 30 rows.
6. Public statements. Date formats the converter did not know, such as "09/01", "3 Sep", "1 mei" and "02 janv. 25", and French column labels meant that four of the five benchmark statements gave no transactions. In the fifth, a "Balance Carried Forward" line was exported as a transaction and doubled the totals, and description lines were lost where a rotated watermark word sat on the same line.

What Changed

We fixed these with general rules, not bank-specific ones:

• Amounts are assigned to a column by how that column's values are aligned, not only by where its header starts.
• Repeated page headers and footers and "Page n of N" are removed, and description lines that wrap join the row above.
• A trailing minus, DR and CR, and brackets stay with the amount, and decide whether a single amount column goes to debit or credit.
• A new option, Numbers: As printed (the default) or Plain numbers, which writes values like -1234.56.
• Dates are recognised in numeric forms and with month names in English, French, Dutch, German, Spanish, Italian and Portuguese, and are exported exactly as printed. Column labels are recognised in the same languages.
• Balance and total lines are kept out of the rows and listed in the notice.
• Rotated text such as watermarks is ignored.
• A cell that starts with = or @, or with + or - followed by anything other than a plain number, gets a single quote in front, so a spreadsheet shows it as text instead of running it as a formula.
• Reference numbers are no longer cut short.

The page now says why when it fails, for example by naming the first values it found below the table header, and it shows a notice when something looks wrong instead of a plain "Done".

The CSV File

The file is UTF-8 with a byte order mark, separated by commas, with every cell in quotes and quotes inside a cell doubled. Lines end with CRLF, with none after the last row. Description lines that wrapped in the PDF are joined with a space. We did not test Excel. In LibreOffice, opening the CSV dropped leading zeros (000123456789 became 123456789) and turned a 19-digit reference into 1.23456789012346E+018. That is spreadsheet behaviour, not something the file can prevent.

What Still Goes Wrong

• Credit-card purchases land in the credit column, because the converter does not infer the card's sign convention.
• A second date column stays inside the description.
• Detail lines with a zero amount and no date are not exported; the notice quotes them.
• A description that continues on the next page is not joined to its row, and no notice says so.
• Without a recognisable amount header, amounts are not placed in a column; a notice is shown.
• Footer text directly under a row, if it does not repeat on other pages, can be glued into that row; a notice is shown.
• Undated lines with text in the date column are skipped; the notice counts them.
• A PDF that is a layered "how to read your statement" guide rather than a statement stops with an explanation.
• Scanned (image-only) and password-protected PDFs show an error.
• We tested no real bank's PDF and no Excel.

What Happens to Your File

During conversion the page made requests only to its own site, carrying 0 bytes of your data. The code has no size or page limit of its own: a 201-page, 10,000-row test converted in about 1.8 to 2.1 seconds. The practical limit is your browser's memory, which we did not measure.

What to Check After Converting Any Statement

1. Count the rows against the statement.
2. Add up the amounts and compare them with the printed totals.
3. Check the sign of every kind of amount, especially on credit-card statements.
4. Read the notice under the result.
5. Look at the last row of each page.
6. Open the CSV as plain text once before you trust a spreadsheet's view of it, especially for references with leading zeros.

For the steps themselves, see how to convert a PDF bank statement to CSV, and for what the converter does, the PDF to CSV page.

We Will Test Again

These results describe the current version; when the converter changes, we test again and update this post.

Try It

To convert a statement of your own, open ClearConvert, drop the PDF on the page and choose CSV. The file stays in your browser and is never uploaded.

Related posts

• How to Convert a Bank of America Statement PDF to CSV (Free)
• How to Convert a Deutsche Bank Statement PDF to CSV (Free)
• How to Convert a Chase Bank Statement PDF to CSV (Free)