Invoice Reconciliation

This example walks through reconciling an Excel export of orders against a CSV of line items — a common accounting workflow where IDs appear as substrings inside description fields.

The problem

You have two files:

orders.csv — order IDs from your system:

Invoice,Amount,Date
100154612,685.00,2025-01-15
100154874,1200.50,2025-01-16
100154312,450.00,2025-01-17

line_items.csv — line items from a vendor, where order IDs appear inside descriptions:

id,description,amount,status
1,"Payment for order #100154612 - Shipping",685.00,paid
2,"Overweight charge ref 100154874",45.00,pending
3,"Invoice 100154874 main charge",1155.50,paid
4,"Credit memo 100154312",450.00,paid
5,"Service fee Q1 2025",75.00,paid

The challenge: order 100154874 appears in two line items (rows 2 and 3), and the IDs are buried inside free-text descriptions.

Step 1: Direct diff with contains-column

vgrid diff orders.csv line_items.csv \
  --key Invoice \
  --match contains \
  --contains-column description \
  --key-transform digits \
  --on-ambiguous report \
  --save-ambiguous ambiguous.csv \
  --no-fail \
  --out json

What each flag does:

Flag	Purpose
--key Invoice	Left key column: the order ID
--match contains	Substring matching mode
--contains-column description	Search the right description column (not the right id column)
--key-transform digits	Extract only digits — "Payment for order #100154612" becomes 100154612
--on-ambiguous report	Include ambiguous matches in output instead of failing
--save-ambiguous ambiguous.csv	Export ambiguous matches for manual review
--no-fail	Exit 0 regardless of diffs (agent-friendly)

Step 2: Review ambiguous matches

Order 100154874 matches two line items. The ambiguous export shows:

left_key,candidate_count,candidate_keys
100154874,2,Overweight charge ref 100154874|Invoice 100154874 main charge

This tells you: one order split across two line items (an overweight surcharge + main charge). The amounts should sum to the order total: 45.00 + 1155.50 = 1200.50.

Step 3: Verify amounts with calc

Pipe the ambiguous items into calc to check the sum:

vgrid convert line_items.csv -t csv --headers \
  --where 'description~"100154874"' \
  --select amount | \
  vgrid calc '=SUM(A:A)' -f csv

Output: 1200.5 — matches the order amount.

Step 4: CSV output for downstream processing

For a full reconciliation report:

vgrid diff orders.csv line_items.csv \
  --key Invoice \
  --match contains \
  --contains-column description \
  --key-transform digits \
  --on-ambiguous report \
  --no-fail \
  --out csv \
  --output recon-report.csv

When to use which key transform

Data shape	Transform	Example
Clean numeric IDs	digits	Order #100154612 → 100154612
Mixed alphanumeric codes	alnum	INV-2025-X42 → INV2025X42
Already clean	trim (default)	" 100154612 " → 100154612

Common pitfalls

• Don’t use --key-transform digits if IDs contain meaningful letters. Use alnum instead.
• Ambiguous matches are expected in contains mode. Don’t treat them as errors — they often represent split transactions or multiple line items per order.
• Always use --no-fail in automated workflows. Parse the JSON summary to determine results.