How to Clean a Bank Export CSV: Dates, Duplicates, and Merchant Names

Start with an untouched export

A clean bank export is a prepared copy of the original transaction file, not a replacement for it. Download the file in a format that preserves transaction rows and available identifiers, then save an unchanged copy in the household's records folder. A CSV file is useful because spreadsheet applications and data tools can read its rows and columns, but its appearance depends on the exporting bank and the software opening it.

For example, the fictional Chen household receives an export with 126 synthetic rows, including purchases, incoming payments, transfers, and refunds. Those figures are illustrative and require human checking. Before editing, record the account name, export date, period covered, and currency shown by the source. If the bank provides a transaction reference, retain it as a separate field throughout the cleaning process.

• Save an unchanged source file.
• Record the account and covered period.
• Keep transaction identifiers where available.
• Create a separate working copy.

Standardize dates, amounts, and column names

Date fields are a common source of spreadsheet errors. A date such as 04/05/2026 can mean April 5 or May 4 depending on the export convention. Confirm the bank's date format before converting the column. Keep transaction date and posting date separate when both are supplied. A transaction may occur on one day and appear on the statement on another, so merging those fields can make reconciliation harder.

Next, inspect amount columns. Some exports provide one signed amount, while others separate debits and credits. Choose a consistent working structure, but document the conversion rule and preserve the original columns. For example, an illustrative debit of 32.50 and credit of 32.50 should remain distinguishable even if the cleaned file uses one signed amount field. Confirm currency and decimal separators before treating values as numbers.

• Confirm the source's date convention.
• Preserve transaction and posting dates separately.
• Check decimal and thousands separators.
• Document the sign convention for debits and credits.

Find duplicates without deleting legitimate transactions

Duplicate detection works best as a candidate list. Compare transaction identifiers first when they exist. Without identifiers, examine a combination of date, amount, merchant description, account, and transaction type. Identical values can still represent separate purchases, especially when a household makes two visits to the same store on one day. A repeated row may also result from exporting overlapping date ranges.

Consider two synthetic rows from the Chen household: MARKET NORTH, 24.80, April 8, and MARKET NORTH, 24.80, April 8. They look identical in a short spreadsheet view. One might be a genuine second purchase, while the other might be a duplicated export row. The example amounts require human checking. Keep both rows in the working file until the bank reference, original export, or statement provides enough evidence to resolve the difference.

• Compare identifiers before descriptive fields.
• Flag matching date-and-amount combinations.
• Inspect overlapping export periods.
• Keep unresolved candidates visible.

Preserve merchant names while creating useful labels

Merchant descriptions often contain location codes, terminal identifiers, abbreviations, and payment processor wording. A household can create a normalized merchant column to make repeated descriptions easier to group. The original bank description must remain available beside it. For example, CITY BOOKS 014 and CITY BOOKS ONLINE might be grouped under a working label such as City Books, provided the household understands that the normalized label is an organizational convenience.

Do not remove unfamiliar text just because it looks untidy. A payment processor can appear in a statement description alongside a merchant name, and a marketplace description may not identify the final seller. Preserve enough detail to trace the transaction to its original source. If the merchant is unclear, use a neutral label such as Unidentified merchant and leave a note for follow-up.

• Keep the original description unchanged.
• Add a separate normalized merchant field.
• Retain useful location or reference codes.
• Flag descriptions that do not identify the seller.

Give AI tools a controlled categorization task

Once the export is structurally consistent, provide the tool with the permitted columns and a short category dictionary. Explain how the household distinguishes groceries, dining, transport, utilities, household supplies, subscriptions, income, transfers, refunds, and uncategorized entries. Ask for one suggested category per transaction and a separate uncertainty note. This makes it possible to compare suggestions row by row without allowing a tool to silently replace the source information.

For example, an entry reading STREAMLINE MEDIA 12.00 might receive a subscription label, but the merchant wording alone does not identify the service or establish its household purpose. A supermarket purchase might include cleaning supplies as well as food. Check receipts or other source records when a transaction could reasonably belong to more than one category. Categorize bank transactions with AI as a draft classification exercise, not as an automatic final entry.

• Supply a defined category list.
• Request one row-level suggestion per transaction.
• Require uncertain entries to remain flagged.
• Retain the source description in the returned file.

Check the cleaned file before using its totals

A final cleaning pass should compare the working file with the original export. Confirm that the row count has not changed without explanation, that dates remain within the intended period, and that debit and credit values retain their meanings. Compare totals with the source export and the corresponding statement, accounting for differences in coverage or posting dates. A matching total is useful evidence, but it does not prove every category is correct.

The Chen household can keep a review log with transaction reference, original description, suggested category, final category, reason for the change, and reviewer initials or a household member's name. Record the number of unresolved duplicate candidates and uncertain categories separately. The next step is to save the cleaned working copy, preserve the original CSV, and send only the necessary columns to the categorization tool.

• Compare original and cleaned row counts.
• Reconcile debit and credit totals.
• Count unresolved duplicate candidates.
• Save the review log with the working file.

Questions readers ask