Fix ambiguous dates and decimal separators in bank CSV files

When a bank CSV already has the right columns, a plausible-looking date or amount can still mean the wrong thing. Preserve the raw text, establish the source convention, and convert dates and amounts separately. A successful import is not evidence that 03/04/2026 or 1,234 was interpreted correctly.

If your file is still one column or has garbled text, resolve that first with the delimiter and encoding guide. LocalExtract can produce CSV from a PDF, but an existing CSV needs interpretation checks rather than another PDF conversion.

Establish conventions before choosing a locale

Use the bank export instructions, the issued statement or another reliable source to establish date order, decimal mark and grouping mark. The website language, your computer locale and the bank’s country are clues, not proof. An English-language export can use day-first dates; a German-speaking reader can receive dot-decimal data.

All examples here are synthetic. The third date can rule out month-first interpretation for that row, but it does not prove that a whole merged file uses one convention. Stop if source files were combined from different exports or a column contains conflicting patterns. Split by a verified source convention before applying a type; do not swap day and month everywhere to fix a few errors.

When every date is ambiguous and no source convention is available, retain the text and mark it unresolved. Guessing from a majority, transaction description or current display format would create facts the file does not contain.

Keep raw and converted columns side by side

Make a working query or workbook with a stable source-row reference. Keep the raw date and amount as text, and duplicate those columns for conversion. If an automatically inserted type step has already interpreted the values, remove it and return to the source text before making the duplicates. Reformatting an incorrectly interpreted date only changes how the wrong date is displayed.

For Microsoft 365 desktop Excel on Windows, load the CSV through Data → From Text/CSV → Transform Data. In the Power Query Editor, right-click the converted column and choose Change Type → Using Locale; select Date for a date column or the appropriate numeric type for an amount column, together with the locale matching that column’s documented convention. These are the English menu labels; localized wording can differ. Microsoft documents the per-column locale setting and precedence.

That operation’s locale takes precedence over the query locale, which takes precedence over the operating-system setting. Choose dates and amounts independently if their source conventions differ. Do not infer that changing the Windows region will repair previously converted cells, and do not assume this Windows route is identical on Mac or the web.

For the first date in the table, a documented day-first source should yield the date shown in the first row; a documented month-first source should yield the second. An ISO-style review column can make that difference explicit:

raw_date     | confirmed_pattern | review_date
03/04/2026   | DD/MM/YYYY        | 2026-04-03
03/04/2026   | MM/DD/YYYY        | 2026-03-04

This review representation is not a claim that every accounting importer accepts ISO dates. Before exporting, use the recipient’s exact date format and recheck the exported text against this decision record.

Test decimal marks, grouping and signs separately

Once a row is correctly delimited, its amount can still be text or a wrongly converted number. The following cases define their conventions explicitly; none establishes a bank’s actual layout.

Use a known incoming and outgoing entry to establish direction for the particular account and target. Our teaching convention is a deposit account with receipts positive and payments negative. Do not transfer that convention automatically to a credit-card liability, separate debit/credit columns or another importer. A numeric locale recognizes separators; it does not determine the accounting meaning of a sign.

Do not strip every period or comma with a global replacement. Removing periods repairs the grouping in the first row only under its stated convention; it destroys the decimal mark in the second. If spaces, nonbreaking spaces or currency symbols appear, retain the raw value and establish the exact export convention before defining a cleanup rule. Unexplained conversion errors stay visible for review.

Recheck the result before writing an import copy

We verified the date examples using explicit Python date patterns and the amount examples with Decimal arithmetic under the stated separators. Download the synthetic typing cases for inputs, expected results and unresolved cases. This is a reproducible interpretation exercise, not an execution test of Excel or an accounting importer.

Compare each raw date and amount with its converted value; start with ambiguous dates, grouping marks, negatives and blanks. Check source period, source-row references and transaction count as separate controls. A matching net amount cannot prove completeness: omitting an equal receipt and payment leaves the net unchanged. Never add an adjustment to hide an unexplained difference.

Keep the reviewed workbook or query separately from the CSV you export. Reopen that output as text to confirm the saved date strings, decimal marks, signs and field boundaries. Follow the target’s actual mapping and accepted encoding; the accounting CSV guide covers that handoff. Hold any row whose date, amount or direction remains uncertain rather than uploading it as a guess.

For PDF sources, LocalExtract processes one PDF at a time locally, including OCR, and exports date,description,amount CSV. It has no desktop batch queue. Account services and periodic Pro verification need a network. XLSX is created by saving the CSV as a workbook in Excel. Download LocalExtract when extraction from PDF is needed.

Official Microsoft source checked October 15, 2026. Locale selection and raw-value review are data-preparation steps; they do not establish bank compatibility or completed reconciliation.