CSV Cleaner

Fix the date and decimal format of a bank CSV

Many European banks export CSV files with semicolons between columns, a comma as the decimal separator and the day before the month. Software that expects commas, decimal points and year-month-day dates either rejects such a file or reads wrong values from it. Choose the file below: the page converts it in your browser, shows the first 20 cleaned rows and lists every line it left out, with the reason.

Convert your bank CSV

The cleaner detects the delimiter (comma, semicolon or tab), looks for a header row with a date column and an amount column, and writes eight fixed columns, comma-delimited: date, description, amount, currency, fee, net, reference and source. It recognises common header names in English, Spanish, German and French, such as Date, Fecha, Buchungstag, Amount, Importe, Betrag and Montant.

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. If the header names are not recognised, the page says so and outputs nothing.

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 bank 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. Amounts are never converted between currencies.

How to do the same by hand in a spreadsheet

These steps work in Excel, Google Sheets and LibreOffice Calc. The idea is to tell the spreadsheet which conventions the file uses when it comes in, and which ones you want when it goes out.

  1. Work on a copy, and look at the file in a text editor first. Note the delimiter, find one amount above a thousand to see which sign is the decimal one, and find one date with a day above 12. If no date has a day above 12, the file alone cannot tell you the order; compare one transaction with your bank's website or a paper statement.
  2. Import with the file's own conventions. In LibreOffice Calc tick Semicolon only under Separator options, set Language to a decimal-comma language and set the date column to Date (DMY). In Google Sheets set File, Settings, Locale to the country of the bank before File, Import. In Excel use Data, From Text/CSV, set Delimiter to Semicolon, then Transform Data and Change Type, Using Locale for the date and amount columns.
  3. Look at three cells before going further: an amount above a thousand, an amount written with a thousands dot and no decimals, and a date with a day above 12. =ISNUMBER(C2) filled down shows which amounts are real numbers.
  4. Set aside amounts that are not numbers, remove the closing balance line after writing its balance down, and look at repeated rows before removing them. Two equal payments on one day show two different running balances, and both should stay.
  5. Set the output formats: the custom number format yyyy-mm-dd for the date column and 0.00 for the amount column, with no thousands separator.
  6. Compare with the balance. The sum of your amount column should equal the closing balance minus the opening balance.
  7. Export with decimal points. This is where the comma tends to come back: switch the locale to United States in Google Sheets, set the language of the amount column to English (USA) in LibreOffice Calc, or untick Use system separators in Excel's advanced options before saving as CSV UTF-8. Then open the saved file in a text editor and read one line.

A route that does not depend on locale settings

Import every column as text and rebuild the two formats as text, with the date in column A and the amount in column C:

The order of the two replacements matters: swapping the comma first would leave two dots in the number. If your spreadsheet uses a decimal comma, write the formulas with semicolons between arguments instead of commas.

Notices

Other exports