CSV Cleaner

Merge debit and credit columns into one signed amount

Some bank exports put payments in one column and receipts in another, while most accounting imports want a single amount column where negative means money out. The merge itself is simple. The work is in the rows around it: account lines above the header, currency symbols, rows with both columns empty or both filled, and a closing balance line. Choose the file below: the page merges the columns in your browser, shows the first 20 cleaned rows and lists every line it left out, with the reason.

Merge the two columns of your bank CSV

The cleaner looks for a header row with a date column and a pair of columns named Debit and Credit, Money Out and Money In, Paid Out and Paid In, or Withdrawals and Deposits, and writes eight fixed columns: date, description, amount, currency, fee, net, reference and source. The header row does not have to be the first line of the file.

Your file is read in your browser and is never uploaded, stored or logged. It is held in the memory of this tab only, and closing or reloading the page discards it. Operator: Augeo. See the privacy notice.

Banks do not share one layout, and this page makes no promise about your bank's: the free preview shows what it does to your file. The report names the two columns it merged, so you can see whether it picked the right ones.

The cleaning logic is checked by an automated test script that runs the engine file of this site over six synthetic sample files and a set of checks of the parsing rules; it ran in a sandbox before this site was published and finished with no failed check. The script does not open this page in a browser, does not test the licence key step, and nothing was tested against a real export from any provider. Use the free preview to check your own file.

Download the full cleaned file

  • Price: 6 USD, paid once on Gumroad. No subscription. You receive a licence key.
  • What the key unlocks: the download of every cleaned row of the file you chose, as one CSV named after your file with -cleaned.csv at the end, instead of the first 20 rows on screen. The report and the way files are read stay the same.
  • The key is for one buyer. Runs are not counted and no number of runs is promised. The key is kept in the memory of this page only, so you enter it again after closing or reloading the page.
  • Refunds are requested through your Gumroad receipt and follow Gumroad's refund policy.
  • Try the free preview first. If the preview cannot read your file, the full download cannot either.

Get a licence key on Gumroad for 6 USD

What the cleaner changes in a debit and credit export

A bank export has no fee column, so fee stays empty and net equals the amount. The running balance column is not carried over. Because rows that cannot be read are left out and not guessed, compare the sum of the amount column with the change in your balance: a gap is usually the amount of a listed row that you still have to add back.

How to do the same by hand in a spreadsheet

These steps work in Excel, Google Sheets and LibreOffice Calc. The formulas below use placeholder column letters: Date in A, Money Out in D and Money In in E. Read your own header row and change the letters to match. If your spreadsheet uses a decimal comma, write the formulas with semicolons between arguments instead of commas.

  1. Work on a copy and keep the downloaded file untouched.
  2. Remove the lines above the header so the header is in row 1, then delete blank rows and the closing balance line, after writing its balance down.
  3. Strip the currency symbol from the amount columns only. If the amounts stay left-aligned afterwards they are still text; remove the thousands separator in the same columns. Record the currency in its own column as the three-letter code.
  4. Merge the two columns with a guard. =IF(AND(D2="",E2=""),"CHECK",N(E2)-N(D2)) gives a positive number for receipts and a negative one for payments, and writes CHECK when both cells are empty.
  5. Deal with every CHECK by finding the amount in the bank's own record, and look at rows where both columns are filled with =AND(D2<>"",E2<>"").
  6. Find dates that are not dates. =ISNUMBER(A2) returns FALSE for anything the spreadsheet did not understand. Correct those against the bank's record; do not guess the nearest valid day. Then apply the custom number format yyyy-mm-dd.
  7. Compare with the balance. The sum of the merged column should equal the closing balance minus the opening balance. Then paste the formula column back as values, delete the two original columns and save as CSV UTF-8.

Notices

Other exports