Fix DD/MM and MM/DD dates in a CSV of transactions
A date such as 03/04/2024 is 3 April in one country and 4 March in another, and a spreadsheet picks one reading without telling you. Choose the file below: the page works in your browser, takes the order of day and month from the file when a date settles it, stops and asks you when none does, and writes every date as year-month-day.
Clean your CSV export
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.
Exports do not share one layout, and this page makes no promise about yours: the free preview shows what it does to your file.
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.
Report
Warnings
Lines not parsed
Lines dropped
Preview of the cleaned rows
| line | date | description | amount | currency | fee | net | reference | source |
|---|
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
How the order of day and month is decided
- Only all-numeric dates need an order. These are dates of two numbers and a year separated by slashes, hyphens or points, such as
03/04/2024,3-4-24or03.04.2024. Dates that start with a four-digit year and dates with an English month name are read without one. - A date counts as proof only when it fits one order.
25/03/2024can only be day first, because its first number is from 13 to 31 and its second is from 1 to 12.03/25/2024can only be month first. A value that fits neither order, such as45/01/2024, proves nothing. - When the file holds proof for one order and none for the other, that order is used for every all-numeric date of the file. The report's "Input date order" line says it was shown by a day above 12 in the file.
- When no date settles it, the page stops and asks. No rows are shown. The page quotes up to three dates from your file and offers two buttons, day first (DD/MM) and month first (MM/DD). Nothing is guessed, and the report then says the order was chosen by you.
- When the file holds proof for both orders, the page also stops and asks. After you choose, a warning says the file mixes the two, and the rows that do not fit the chosen order are left out and listed with their line numbers.
- Dates that do not exist are not shifted to a nearby day. A row dated
31/02/2024is left out and listed with the reasondate not recognised. Leap years are taken into account. - Two-digit years from 00 to 69 are read as 2000 to 2069 and from 70 to 99 as 1970 to 1999.
- A time after the date is dropped.
03/04/2024 14:05is written as a date only.
The order is decided once for the whole file from its date column, not row by row. Choosing a new file starts again from the file: a choice made for the previous file is not reused.
The cleaner cannot repair a file in which a spreadsheet has already swapped day and month on some rows and saved the result: those rows hold valid dates and look like any other row. Work from the original download.
The output is always the same eight columns (date, description, amount, currency, fee, net, reference and source), comma-delimited. Columns of your file that are not among them are not carried over, so this is a cleaner for files of transactions and not a general CSV editor.
In a spreadsheet instead: import a copy of the file and set the date column to day-month-year or month-day-year in the import dialog, instead of letting the spreadsheet choose from its own locale.
Notices
- Privacy: files are read in your browser and never uploaded, stored or logged. Operator: Augeo.
- Independent tool, not affiliated with or endorsed by Etsy, PayPal, QuickBooks or any bank.
- Check the output before you use it. This is not accounting, tax or financial advice.
- Price of the full download: 6 USD one-time. The key is for one buyer. Refunds are requested through the Gumroad receipt.
- Only upload data you have the right to process. Choosing a file here does not send it anywhere, but you remain responsible for the data in it.
Other pages
- Convert CSV dates to YYYY-MM-DD
- Fix date and decimal format in a bank CSV
- Convert decimal commas to decimal points in a CSV
- Convert a tab-delimited file to a comma CSV
- Remove total and footer lines from a bank CSV
- Line breaks inside quoted fields of a CSV
- Gross, Fee and Net columns of a PayPal CSV
- Convert a semicolon CSV to a comma CSV
- Remove the lines above the header of a CSV
- Remove duplicate rows from a CSV export
- Negative amounts in parentheses in a CSV
- Merge debit and credit columns into one amount
- Any other CSV export of transactions