Convert an X12 820 remittance to Excel
One payment, many invoices, and a file that will not tell you which is which at a glance. Drop the 820 and get the remittance detail as rows you can reconcile against your open items.
| # | Description | Qty | Unit | Unit price | VAT % | Net amount |
|---|
| Category | Rate | Taxable base | VAT amount |
|---|
| # | Qualifier | Reference | Amount | Paid |
|---|
| Segment | Element | Value |
|---|
The document that closes the loop
The 820 says what was actually paid and against which invoices, which is the file that decides whether your receivables ledger is right. It carries a payment total in the BPR segment and then a set of RMR references, each with the invoice number and the amount applied, frequently with adjustments attached. Reading those in a spreadsheet is the difference between a reconciled month and a guessed one.
What you get out
Every field below is read from the file itself. Nothing is inferred, and a field the invoice does not carry is left empty rather than filled in with a plausible guess.
- BPR payment amount, method, date and bank details
- TRN trace number for matching against the bank statement
- Payer and payee identification from the N1 loop
- RMR remittance rows: invoice reference, amount paid, gross amount
- ADX adjustment reasons and amounts where they are present
Questions
Do the remittance rows add up to the payment?
The export shows both, so the comparison takes a second. Short payments and deductions usually explain a gap, and the adjustment rows carry the stated reason.
Is this an invoice?
No, it is a payment advice, and the invoice-specific checks do not run on it. What you get is the remittance detail, which is what the document is for.
Can it read the bank's own file instead?
If the bank sends OFX or QFX, the tool reads those too. Drop the file on this page and the format is detected.