CSV Ledger Reconciliation: Matching a Bank Export to a General Ledger Export
CSV ledger reconciliation works even when your bank and your ledger only give you files, and you can see exactly how a match is made. This page runs a small synthetic bank export and a synthetic ledger export through four ordered rules, shows every matched and unmatched row, and states what the demonstration does not prove.
CSV ledger reconciliation matches the rows of a bank export to the rows of a general ledger export using rules. This demonstration, for example, tries an exact match first, then a date window, then an amount tolerance, then one-to-many groups. Each row is used once, and whatever stays unmatched becomes an exception for review.
On this page
At a glance
- Two CSV exports, one from the bank and one from the general ledger, can be matched by rules applied in a fixed order, with every row used at most once.
- Tolerances widen what counts as a match; a wider date window absorbs timing, but a larger amount tolerance does not fix a real discrepancy.
- In this demonstration, for example, a match that depended on an amount tolerance is never cleared automatically; it goes to a person.
- The output is the contents of two CSV files: every match, and every exception for a person to work.
- The demonstration is synthetic and shows the method, not a connector or real-volume performance.
What goes in: the two CSV exports
Each export needs a unique id, a date, a signed amount and a reference shared with the other export. A CSV file is plain text, one record per line. RFC 4180 notes that "there is no formal specification in existence, which allows for a wide variety of interpretations of CSV files."[1] The demonstration reads optional double-quoted fields; RFC 4180 says a double-quote inside one "must be escaped by preceding it with another double quote."[1] It needs a header line, because it finds columns by name.
| Export | Columns |
|---|---|
| Bank export | bank_txn_id, posted_date, description, amount, reference |
| General ledger export | gl_entry_id, gl_date, memo, amount, reference |
Dates are YYYY-MM-DD. Amounts are signed decimals with up to two places, payments negative, held as integer cents so rounding never decides a match. The reference links the two sides and is normalized by removing case, spaces and punctuation, so inv1042 and INV-1042 compare equal. A date not in YYYY-MM-DD form, a bad amount or a duplicate id stops the run with the row number.
Illustrative example (synthetic data, not client data or real transactions): the two tables below are the complete input of the demonstration.
| bank_txn_id | posted_date | description | amount | reference |
|---|---|---|---|---|
| B001 | 2026-03-02 | ACH PAYMENT VENDOR A | -1250.00 | INV-1042 |
| B002 | 2026-03-03 | ACH PAYMENT VENDOR B | -480.25 | INV-1043 |
| B003 | 2026-03-05 | CARD SETTLEMENT | 2310.40 | DEP-0305 |
| B004 | 2026-03-06 | WIRE IN CUSTOMER C | 3000.00 | DEP-0306 |
| B005 | 2026-03-09 | ACH PAYMENT VENDOR D | -75.00 | INV-1051 |
| B006 | 2026-03-10 | BANK SERVICE FEE | -15.00 | FEE-0310 |
| B007 | 2026-03-11 | ACH PAYMENT VENDOR E | -620.00 | INV-1060 |
| B008 | 2026-03-12 | CHECK 4471 | -900.00 | CHK-4471 |
| B009 | 2026-03-13 | ACH PAYMENT VENDOR F | -310.00 | INV-1070 |
| B010 | 2026-03-16 | ACH PAYMENT VENDOR A | -1250.00 | INV-1042 |
| B011 | 2026-03-17 | ACH RECEIPT CUSTOMER G | 1500.00 | DEP-0317 |
| B012 | 2026-03-18 | ACH RECEIPT CUSTOMER H | 820.00 | DEP-0318 |
| gl_entry_id | gl_date | memo | amount | reference |
|---|---|---|---|---|
| G001 | 2026-03-02 | Vendor A invoice 1042 paid | -1250.00 | inv1042 |
| G002 | 2026-03-01 | Vendor B invoice 1043 paid | -480.25 | INV-1043 |
| G003 | 2026-03-05 | Card settlement 5 Mar | 2310.42 | DEP 0305 |
| G004 | 2026-03-05 | Customer C receipt part 1 | 1800.00 | DEP-0306 |
| G005 | 2026-03-06 | Customer C receipt part 2 | 950.00 | DEP-0306 |
| G006 | 2026-03-06 | Customer C receipt part 3 | 250.00 | DEP-0306 |
| G007 | 2026-03-20 | Vendor E invoice 1060 paid | -620.00 | INV-1060 |
| G008 | 2026-03-12 | Check 4471 Vendor K | -900.00 | CHK-4471 |
| G009 | 2026-03-13 | Vendor F invoice 1070 paid | -301.00 | INV-1070 |
| G010 | 2026-03-17 | Customer G receipt | 1500.00 | DEP-0317 |
| G011 | 2026-03-18 | Customer H receipt | 820.00 | DEP-0318 |
| G012 | 2026-03-19 | Vendor Z invoice 1088 accrued | -2200.00 | INV-1088 |
The match rules, in order
A match rule states what must agree between a bank row and a ledger row before they are paired. The demonstration applies four rules in order, strictest first. A row paired by an earlier rule is not offered to a later one. For the strategy behind ordering rules this way, see reconciliation match rules and exceptions; this page is the runnable example.
| Rule | What must agree | Outcome |
|---|---|---|
| R1 exact | Amount, date and normalized reference | matched |
| R2 date window | Same amount and same reference; dates within the date window | matched |
| R3 amount tolerance | Same reference; date within the window; amount within the tolerance | needs_review, never auto-cleared |
| R4 one-to-many | One bank row against ledger rows sharing its reference, within the window, whose amounts sum exactly to the bank amount | matched |
- Amounts are compared with their sign: -620.00 and 620.00 are different amounts, so R1 and R2 never pair a payment with a receipt.
- In R1 to R3, each bank row in file order takes the free ledger row with the smallest date gap; ties go to file order.
- A row with an empty reference never matches, and R4 needs at least two ledger rows in the group.
One-to-many matching is the pairing of one bank line with several ledger transactions. One ERP and accounting-software vendor's documentation defines it as "one bank statement line to many system side transactions."[3] An example is a single deposit that the ledger records as several receipts.
Tolerances: what widening a window or an amount changes
A date tolerance is a number of days within which two dates still count as the same event; an amount tolerance is a small difference within which two amounts count as the same payment. One vendor's documentation describes date tolerances as validating that the source transaction date or dates are "within a certain number of days before and after the bank statement line date or dates."[2] The same documentation states that "If no date or amount tolerance is defined within a rule, it requires an exact match."[2]
That documentation also limits amount tolerances: "Reconciliation amount tolerances can only be used in one to one matching scenarios," and for one-to-many scenarios "the amount of the bank statement line or lines must be equal to the amount of the transaction or transactions."[2] The demonstration follows the same shape: its amount tolerance applies only in R3; R4 requires an exact sum.
| Setting changed | Rows | Result |
|---|---|---|
| Date window 3 days to 10 days | B007 and G007 (same reference INV-1060, 9 days apart) | Matched by R2 date window with a 9-day gap. At 3 days they stay unmatched. |
| Amount tolerance 0.05 to 1.00 | B009 and G009 (same reference INV-1070, amounts differ by 9.00) | B009 stays unmatched; a 1.00 tolerance does not cover a 9.00 difference. |
A wider window absorbs a timing difference; a larger tolerance does not absorb a real discrepancy.
The result on the synthetic fixture
Illustrative example (synthetic data): with the default parameters, the 12 bank and 12 ledger rows produce 7 matches, 4 of them R1 exact, 1 by date window, 1 by amount tolerance flagged for review and 1 one-to-many, leaving 5 bank rows and 3 ledger rows unmatched. These counts are not a typical rate.
- Read and check both files
Stop with the row number on a date not in YYYY-MM-DD form, a bad amount or a duplicate id.
- Normalize references
Remove case, spaces and punctuation from each reference.
- Apply R1, R2 and R3 in order
Pair one bank row with one ledger row; mark every R3 pair needs_review.
- Apply R4
Group free ledger rows sharing a bank row's reference within the window; pair them only on an exact sum.
- Label what is left and produce the two files' contents
Give each unmatched row a reason hint; produce the contents of the matches and exceptions CSV files.
| Rule | Bank | GL | GL minus bank | Max day gap |
|---|---|---|---|---|
| R1 exact | B001 | G001 | 0.00 | 0 |
| R1 exact | B008 | G008 | 0.00 | 0 |
| R1 exact | B011 | G010 | 0.00 | 0 |
| R1 exact | B012 | G011 | 0.00 | 0 |
| R2 date window | B002 | G002 | 0.00 | 2 |
| R3 amount tolerance (needs_review) | B003 | G003 | 0.02 | 0 |
| R4 one-to-many | B004 | G004+G005+G006 | 0.00 | 1 |
In the synthetic fixture, B001 and G001 match under R1 because normalization makes INV-1042 and inv1042 the same key. B003 and G003 differ by 0.02, inside the 0.05 tolerance, so they are paired but held for review.
| Side | Row | Reason hint | What it means in this fixture |
|---|---|---|---|
| bank | B005 | no_counterpart_with_this_reference | No ledger row has INV-1051. |
| bank | B006 | no_counterpart_with_this_reference | No ledger row has FEE-0310. |
| bank | B007 | same_reference_outside_tolerance | G007 is 9 days away, outside the 3-day window. |
| bank | B009 | same_reference_outside_tolerance | G009 differs by 9.00. |
| bank | B010 | reference_already_matched | INV-1042 already matched G001: a possible duplicate payment. |
| gl | G007 | same_reference_outside_tolerance | The other half of B007. |
| gl | G009 | same_reference_outside_tolerance | The other half of B009. |
| gl | G012 | no_counterpart_with_this_reference | An accrual with no bank counterpart yet. |
The human review step
Software pairs rows; a person decides what leftovers mean. In the synthetic fixture, the R3 pair needs a decision on a 0.02 difference, B007 and G007 a decision on a timing difference, B009 and G009 an investigation, and B010 a check for a duplicate payment. B005, B006 and G012 each have an entry on one side only, so someone must find or post the missing entry. To route and work such a queue, see payment reconciliation exception automation.
- Each exception row carries a reason hint, so the reviewer starts from a likely cause.
- An R3 pair is listed even though it was paired, because an amount tolerance can pair rows that are not the same transaction.
- The demonstration marks the R4 group matched; a team may prefer to send grouped matches to review until grouping is validated on its own data.
- A correcting entry or a bank query happens in the ledger or with the bank, outside this matcher.
What the output looks like: two files
A run produces the text of two CSV files; the matcher does not read or write files itself. Matches file columns: rule, status, bank_ids, gl_ids, diff, max_day_gap (one line per match). Exceptions file columns: side, id, reason. In the synthetic fixture, the exceptions file holds nine lines under the header: the R3 pair as one review line (review, B003+G003, R3_amount_tolerance), then the eight unmatched rows with the reason hints shown above.
Limitations and when not to use this approach
This demonstration shows how a match is made and reviewed, not how it performs on your data.
- All data is synthetic and small. Nothing here was tested on real transactions or at real volumes.
- It is a method over two CSV files, not a connector to any product.
- Matching depends on a shared reference. Without one on both sides, these rules leave rows unmatched and you need a different key first.
- Many-to-one and many-to-many matches are not covered; only one bank row against several ledger rows is.
- Do not automate when a person already clears the volume without a backlog.
- Do not automate when the team has not agreed its rules.
- Do not automate when an existing reconciliation tool already does the job.
Frequently asked questions
How do you reconcile two CSV files, a bank statement and a general ledger export?
Parse and check both files, normalize the shared reference, then apply match rules from strictest to loosest, using each row once. Pairs that relied on a tolerance go to review, and rows left over become exceptions that a person works.
What columns does a CSV ledger reconciliation need?
Each side needs a unique id, a date, a signed amount and a reference that links the two sides. Descriptions or memos help a reviewer but are not matched on. The demonstration names these columns bank_txn_id, gl_entry_id, posted_date and gl_date.
How do date and amount tolerances work in matching?
A date tolerance accepts dates within a set number of days; an amount tolerance accepts a small difference. In the demonstration, a pair that needs the amount tolerance is always held for review. Widening a window can absorb a timing difference, but a larger tolerance does not erase a genuine discrepancy.
How does one-to-many matching work?
One bank line is compared with a group of ledger rows that share its reference and fall inside the date window. If the group's amounts sum exactly to the bank amount, all of them are paired with that line, as with a deposit booked as several receipts.
What happens to rows that do not match?
They are never forced into a match. Each unmatched row receives a reason hint, such as no counterpart with its reference, and goes to the exceptions file for review. A person investigates it and corrects the ledger or queries the bank.
When should you not automate a CSV reconciliation?
Skip it when volume is low enough that manual review already keeps up, when the matching rules are not yet agreed, when the two files share no common reference, or when an existing reconciliation tool already covers the job. A match does not decide accounting treatment.
Related reading
- The wider financial reconciliation workflow includes this pattern.
- How to choose and order match rules is the strategy behind this page.
- Handling reconciliation exceptions covers the leftovers.
Sources
- IETF, RFC 4180: Common Format and MIME Type for Comma-Separated Values (CSV) Files (rfc-editor.org) (accessed 2026-10-30)
- Oracle, Overview of Tolerance Rules, Oracle Fusion Cloud Financials 25D (docs.oracle.com) (accessed 2026-10-30)
- Infor, Creating reconciliation match rules, Infor Lawson Financials 10.1 (docs.infor.com) (accessed 2026-10-30)