Home › Blog › Excel, CSV & Google Sheets › How to Clean Up a Converted Bank CSV
Remove header blocks, page-title rows, subtotals, blank rows, and stray characters from a converted bank CSV so it imports cleanly the first time.
By StatementVerified Editorial · November 4, 2026 · 6 min read · Filed under Excel, CSV & Google Sheets
A converted bank CSV is rarely ready to import the moment it appears. Bank statements are documents designed for reading, not data files, so conversion carries over the furniture of the page: account header blocks, repeated page titles, section subtotals, blank spacer rows, and stray characters from logos and footers. None of that is transaction data, and all of it will confuse an importer or, worse, import as transactions with blank dates and odd amounts. Cleaning the file is not cosmetic work. It is the step that decides whether the import is trustworthy.
The good news is that statement clutter follows patterns. Once you know the five or six kinds of rows and characters that do not belong, you can clear them in a consistent order, verify the result against the statement totals, and reuse the same routine every month. This guide gives you that routine, in the order that experienced bookkeepers actually apply it, plus the checks that tell you the cleaned file still agrees with the bank.
Statements open with an account summary: the bank name, the account holder, the period, opening and closing balances, and sometimes a summary of deposits and payments. Converted naively, those lines become the first rows of the file, above the real column headings. Delete everything above the true header row so the file begins with the column names themselves. Importers expect the first row to name the columns; a summary block above it either breaks the import or shifts every row that follows.
Page furniture repeats through longer statements. Each new page may carry the bank name, a page title, the account number line, and the column headings again. Those repeated heading rows appear in the middle of the data with text where dates should be. Sort or filter the date column to find them: any row whose date cell holds words instead of a date is furniture, not a transaction. Remove those rows wherever they occur, and check the end of the file for footer text, disclaimers, and summary tables that converted after the last real transaction.
Many statements group transactions and print subtotals: total deposits for a section, total card payments, daily balance summaries. A subtotal row often has an amount but no date, or a date and an amount that duplicates the transactions above it. Importing one double-counts that section. The reliable test is the running balance where the statement provides one: a real transaction moves the balance by its amount, while a subtotal row either has no balance or repeats a balance already shown. Delete subtotal and section-heading rows, then confirm the file still walks from opening to closing balance.
Blank rows are less dangerous but still worth removing, because some importers stop reading at the first fully blank line and silently import a partial file. Delete empty rows rather than leaving tidy spacing in the data; a data file needs no spacing. Also watch for rows that hold only a continuation of the previous description, where a long bank memo wrapped onto its own line. Merge that text back into the transaction above it so the description stays complete and no orphan row remains to fail on a missing date.
Conversion leaves artifacts: stray symbols where a logo sat, odd spacing inside amounts, thousands separators in large numbers, currency symbols attached to some values and not others, and non-breaking spaces that make a number read as text. Clean amounts to plain numerals with a decimal point, one format for the whole column. Remove commas inside numbers, because many importers read a comma as a column break and split one amount into two fields, shifting the rest of the row.
Be surgical with find and replace. Replacing characters across the whole file can damage descriptions, where symbols may be legitimate parts of a payee name or reference. Work column by column: dates get date cleaning, amounts get numeric cleaning, descriptions get only a tidy of leading and trailing spaces. If a description contains a comma and your file is comma-delimited, the value needs to be properly quoted in the CSV, which a good export handles for you; check one such row in a text editor to confirm the quoting survived.
After the clutter is gone, bring the real data into one convention. Every date in the same format, ideally an unambiguous one with a four-digit year. Amounts either as one signed column or as separate debit and credit columns, consistently from the first row to the last. Column order matching the mapping you saved for this bank and account. This standardization is what makes the file boring, and boring is the goal: an importer should find exactly what it expects in every single row.
While standardizing, scan for duplicates created during conversion, where a transaction at a page break appears at the bottom of one page and the top of the next. The tell is two identical rows, same date, same description, same amount, sometimes with the running balance repeating. Delete one copy only after confirming against the balance walk that the statement itself counted the transaction once. Duplicates from page breaks are common enough that this scan belongs in every cleanup, not just suspicious ones.
Cleaning is only finished when the file proves itself. Count the transaction rows and compare with any transaction count the statement prints. Total the credits and the debits separately and compare both with the statement summary. Walk the running balance if one is present: opening balance, plus each credit, minus each debit, ending exactly on the closing balance for the period. Any disagreement means a row was removed that should have stayed, a duplicate survived, or an amount was altered during character cleaning, and it is far easier to find now than after import.
Save the cleaned file as the import version, and keep the original conversion untouched in the same folder. That pair documents your work: the source as the bank presented it, and the import file as you prepared it, with the verification totals bridging the two. On recurring monthly work this routine takes minutes. On a year of catch-up work it is the difference between twelve confident imports and one long afternoon of reconciliation archaeology.
Check the date and the running balance. Subtotal rows often lack a date or a description, and they do not move the running balance in the way a real transaction does. If removing the row makes the file walk exactly from the opening to the closing balance, it was a subtotal.
You can fix individual records there, but structural problems such as header rows, repeated page titles, and split amounts import as broken records that are slower to find and remove. Cleaning the flat file first, then verifying totals, is faster and leaves a clean audit trail.
A fully blank row, an unquoted comma inside a description, or a row with the wrong number of fields are the usual causes. Open the file in a text editor at the point where the import stopped and inspect that exact line as raw text.
Excel, CSV & Google Sheets · November 2, 2026
Understand the two amount conventions in statement files, which importers expect each one, and how to convert between them without flipping signs.
Excel, CSV & Google Sheets · November 2, 2026
Map bank statement CSV columns to your accounting importer fields correctly, keep one convention per file, and avoid silent import errors every time.
Excel, CSV & Google Sheets · November 3, 2026
Excel workbooks and plain CSV each win in different bookkeeping jobs. Learn the formatting risks of workbook round-trips and when to use each.
Convert a statement now and watch the reconciliation check verify it against the statement itself.
Your first conversion is free — no card, no sign-up — and every result is reconciled against the statement itself before you download it.