Home › Blog › Excel, CSV & Google Sheets › CSV Column Mapping for Accounting Imports
Map bank statement CSV columns to your accounting importer fields correctly, keep one convention per file, and avoid silent import errors every time.
By StatementVerified Editorial · November 2, 2026 · 7 min read · Filed under Excel, CSV & Google Sheets
Column mapping looks like a thirty second step at the end of a conversion, and it is where a surprising number of bookkeeping errors are actually born. The importer does not know what your columns mean. It only knows the labels you assign, so a deposit column mapped as a payment, or a memo field mapped as a payee, creates records that look plausible in a list and wrong everywhere in the ledger. The fix is not speed. The fix is deciding one clear convention for each file before you import, and then checking the first few records against the statement itself.
This guide walks through mapping the way an experienced bookkeeper handles it in practice: identify what the source file really contains, decide the destination fields in advance, map once, save the mapping for that bank and that client, and verify totals before moving on. The details matter more than the tool. A clean mapping on a small account saves a quick check. A careless mapping on a year of catch-up work can bury sign errors, split transactions, and duplicated deposits through twelve reconciliations before anyone notices.
Open the converted CSV on its own before you open any import screen. Note the exact columns present: date, description, amount, debit, credit, balance, check number, and any bank-specific fields such as transaction type or reference. Some converters produce a running balance column and some do not. Some split the description into payee and memo parts, and some keep the full bank text in one field. You cannot map well until you know which of these you actually have, because mapping a field that does not exist in a clean form leads to guesses the importer will happily accept.
Next, count the rows and add the amount column in a spreadsheet. Compare that total, and the count, to the statement totals for the same period. Mapping should never begin on a file whose totals are unverified, because a mapping error layered on top of a conversion error is almost impossible to untangle later. If the statement shows an opening and closing balance, check that the opening balance plus all credits minus all debits lands on the closing balance. That one arithmetic check, done before mapping, is the cheapest quality control in the whole workflow.
Every accounting importer expects a small set of core fields: a transaction date, a description or payee, and either one signed amount or separate money-in and money-out columns. Write down, for this client and this bank, which source column fills each destination. Date maps to date and nothing else. The full bank description usually maps to the memo or description field, because truncating it to a short payee name during import throws away the detail you will need when a client asks what a charge was eight months later.
Treat optional fields with suspicion. Check numbers matter for accounts that still issue paper checks, because the number is how you match a cleared item in a reconciliation. Reference numbers from the bank rarely help inside the ledger and often clutter it. A running balance column should almost never be imported as a transaction field at all; its job is verification before import, not data entry. Deciding these points in advance turns mapping from a series of on-screen hunches into a repeatable convention you can hand to another bookkeeper without a long explanation.
The most common mapping failure is a file that mixes conventions halfway down. The first half uses negative amounts for money out and the second half, pasted in from another export, uses positive amounts in a separate debit column. Importers map columns, not intentions, so a mixed file imports the second half backwards. Keep each import file to a single account, a single period style, and a single amount convention. If a client gives you two accounts at the same bank, map and import them as separate files even if the column layout looks identical, because the saved mapping is tied to the account, not just the layout.
File naming supports this discipline. Include the client, the bank, the last four digits of the account as shown on the statement, and the period in every file name. When you return to a cleanup three months later, the name tells you which saved mapping belongs to which file without reopening everything. Consistency sounds clerical, but in mapping work the clerical layer is the control layer. Most expensive import errors are not exotic; they are the right mapping applied to the wrong file on a busy afternoon.
Most importers let you save a mapping profile. Use it, and name it for the bank, the account, and the convention, not with a generic label. A profile saved from a checking account with separate debit and credit columns will quietly mis-map a savings account from the same bank that exports a single signed amount. Saved mappings are a genuine time saver on monthly work because bank layouts rarely change, but they should be rechecked whenever the bank redesigns a statement, changes its export format, or merges with another institution.
After importing, do not trust the success message. Open the account register and compare the first three and last three transactions, line by line, against the statement PDF. Check that a known deposit appears as a deposit, a known card payment appears as money out, and the dates match the statement rather than the day the file was created. Then compare the register balance movement for the period to the statement movement. This five minute review catches the sign flips and field swaps that totals alone can miss when errors happen to offset each other.
If every amount imports reversed, the mapping or the sign convention is wrong for the whole file; delete the imported batch if your system allows it, correct the convention, and re-import rather than fixing hundreds of lines by hand. If only some rows reverse, the source file mixes conventions and needs to be rebuilt. If dates cluster on one day, the date column was probably a posting-batch field rather than the transaction date. If descriptions are blank while payees are crowded, the description was mapped to the wrong destination and the original text may still be recoverable from the source file.
Keep the original converted file untouched in the client folder alongside whatever corrected version you import. That habit makes every mapping decision auditable: anyone can open the source, open the imported version, and see exactly what changed. Mapping is a translation step, and like any translation it should be possible to check the result against the original without asking the translator to remember what they did.
Only if both accounts export exactly the same column layout and the same amount convention. Check the first import from the second account line by line, because savings, credit card, and checking products at one bank often export differently despite looking similar on screen.
No. The balance column is a verification tool before import. Use it to confirm that the transactions walk from the opening balance to the closing balance, then leave it out of the imported fields so it does not create meaningless data in the register.
Pick two transactions you know well, one deposit and one payment, and find them in the register. Then compare the total movement for the period with the statement. A whole-file sign error shows up immediately in both checks, while totals alone can hide offsetting mistakes.
Excel, CSV & Google Sheets · November 5, 2026
Ambiguous date orders in statement files put transactions in the wrong month. Use one clear format per file and check the rows most at risk.
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.
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.
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.