How to prepare files for Reconciliation

Preparing clean, consistent files before running reconciliation is the single most effective way to reduce manual review and speed up matching. This guide walks finance and operations teams through a repeatable process of reconciliation file preparation so that uploads run smoothly and the reconciliation engine produces usable matched, partially matched, and unmatched results.

Good reconciliation file preparation saves time, prevents avoidable exceptions, and produces clearer audit trails. The steps below are practical, tool-agnostic, and aligned with how modern reconciliation products accept and process Side A and Side B data.

The primary aim is to help you standardize inputs, choose the right columns, enrich records where needed, and avoid common mistakes that create noise and ambiguity during reconciliation.

Why this topic matters

Finance teams routinely spend disproportionate time cleaning and interpreting source files before they can reconcile. Poorly prepared files cause:

• False unmatched items because identifiers are formatted differently.
• Skipped or rejected rows due to missing headers, dates, or invalid amounts.
• Extra manual matching work when descriptions or references don’t align.

A predictable file-preparation routine reduces these problems, shortens the review cycle, lowers operational cost, and produces audit-ready reports faster. Whether you are doing bank reconciliation, marketplace settlement reconciliation, or PSP vs books matching, upfront work on files pays dividends during review.

Core components

Reconciliation file preparation rests on a few repeatable components: correct file format, required columns, supporting data, derived columns for business rules, and consistent cleaning or standardization.

File formats and uploads

• Use CSV, XLS, or XLSX for primary reports. These formats are widely supported by reconciliation platforms.
• Maintain one consistent file layout per report type. If you upload multiple files under the same report, ensure they follow the same header and column structure.
• Keep file sizes reasonable. Split very large exports into logical chunks (daily or weekly) if your system or process prefers smaller uploads.

Required columns: date, amount, identifier

• Date column: Use a consistent date format (ISO YYYY-MM-DD is preferred). Normalize time zones if timestamps vary.
• Amount column: Ensure amounts are numeric, with decimal separators standardized (prefer dot as decimal point). Remove currency symbols from the amount column and include currency as a separate column if needed.
• Identifier/reference column(s): Provide a unique reference where possible — order ID, transaction ID, invoice number, bank UTR, or settlement ID. When multiple identifiers exist, upload them in separate columns.

Supporting data and derived columns

• Supporting data: Upload lookups such as product masters, fee schedules, or return reports as supporting files. These enrich primary records (e.g., add SKU, map partner codes, or compute fees) but are not directly reconciled.

• Derived columns: Use derived or calculated columns to express business rules. Example derived formulas:

  • Net amount after fee: "Payment Amount - Payment Fee".
  • Use payment only if status = Delivered, otherwise 0.

  Derived columns reduce manual pre-processing. Many platforms let you describe the logic in plain language and generate the formula.

Standardization and cleaning

• Trim whitespace, remove invisible characters, and normalize case for identifier fields.
• Remove leading zeros or normalize them consistently across systems if IDs use variable padding.
• For names and descriptions, reduce punctuation and common abbreviations (e.g., "Ltd" -> "Limited"), but keep original fields for auditability.
• Flag suspicious values (null dates, zero amounts where not expected) rather than silently correcting them.

Reconciliation file preparation checklist

Use this checklist before uploading files to the reconciliation engine:

• Confirm file format: CSV, XLS, or XLSX.
• Verify header row is present and correctly selected in upload settings.
• Ensure a consistent date format and specify the date column.
• Ensure numeric amount column with standardized decimals and currency column if needed.
• Provide at least one identifier/reference column; include secondary identifiers where helpful.
• Upload supporting data files (product master, fee rates, mapping tables) where they enrich matching.
• Create derived columns for common business rules (net amounts, conditional values).
• Run a small test upload (100–500 rows) to confirm parsing and column mapping.
• Check rejected/skipped rows and fix issues before running a full-period reconciliation.

Practical implementation steps

1. Export and archive raw reports

• Always export the original raw files and keep an archive copy. Raw files are required for audit trails and to troubleshoot parsing issues.

2. Create a canonical template for each report type

• Build a spreadsheet template that shows required columns and formats. Share the template with internal teams and external partners where possible.

3. Map columns during upload

• When configuring a reconciliation, map your header row, date column, amount column, and identifier column(s). Confirm that the platform accepts the selected header row.

4. Add supporting files and create derived columns

• Upload supporting data to enrich fields like SKU, fee rates, or customer codes. Create derived columns for netting fees, applying conditional logic, or concatenating identifiers.

5. Run standardization

• Normalize date formats and amounts. Use automatic trimming and text normalization to reduce false mismatches.

6. Test with a small dataset

• Run reconciliation on a sample to surface parsing errors, skipped rows, or mapping mistakes. Address errors and re-run until the sample produces expected results.

7. Schedule and automate

• Once configuration is stable, schedule regular uploads via API, SFTP, or email automation where supported. Keep manual upload as a fallback for ad hoc periods.

8. Document the process

• Maintain a short SOP that lists templates, expected columns, derived column formulas, and known quirks for each partner or system.

Common mistakes to avoid

• Uploading files without headers or with inconsistent header rows across files for the same report.
• Using mixed date formats or embedding dates in free-text narration fields rather than a dedicated date column.
• Passing amounts with currency symbols in the amount column instead of separate currency columns.
• Omitting identifiers or relying solely on fuzzy descriptions for matching.
• Permanently modifying raw files (overwrite originals) instead of working on copies and keeping raw exports for audit.
• Ignoring skipped records: skipped rows often contain the root cause (invalid amounts, missing mandatory fields).
• Over-reliance on AI matching without reviewing low-confidence matches—use manual matching where necessary.

Key Takeaways

• Start every reconciliation run by verifying file format, header row, date, amount, and identifier columns.
• Use supporting data and derived columns to express business rules and avoid heavy pre-processing outside the reconciliation tool.
• Standardize identifiers, dates, and amounts to prevent false unmatched items and skipped records.
• Test with small samples, keep raw exports, and document templates and mapping for each partner.
• Automate uploads only after stable mapping and test runs reduce manual work and recurring errors.

Conclusion

Consistent reconciliation file preparation dramatically reduces review time and increases the usefulness of automated matching engines. Follow the checklist, create reusable templates, and use supporting data and derived columns to reflect business rules before you run reconciliation. Good preparation helps your finance team get to exceptions faster and keeps audit trails tidy. For a reconciliation engine that supports CSV, XLS, XLSX uploads, derived columns, supporting data, and both rule-based and AI matching, consider streamlining your process with a modern platform.

Start your 14-day free trial with Cointab. No credit card required. 14-day free trial.