Excel Merge and Reconcile

Deliver a new workbook containing usable consolidated data, field differences, unresolved rows, and processing totals.

Procedure

1. Identify append/consolidation versus left-right reconciliation. Establish record grain (order, order line, employee, SKU), composite keys, currency/unit/period scope, and which fields should be compared. An order number alone is not a valid key for multi-item orders.
2. Inventory selected files, sheets, header rows, encodings, and row counts. Inspect representative rows, not entire large files in the model context. Read the input contract to create the configuration.
3. Map headers explicitly when synonyms are ambiguous. Preserve identifier text and leading zeros. Configure numeric/date types, locale separators and exact text normalization. Missing values stay missing.
4. Run scripts/merge_reconcile.py with the configuration and a new output directory. It reads local CSV/XLSX and generates result.json and result.xlsx. Read review rules for interpretation.
5. Inspect unresolved rows before accepting totals. Compare raw input counts, excluded rows, kept rows and duplicate counts. Reconciliation must not choose an arbitrary row for a duplicate key.
6. Deliver the workbook and explain differences, unresolved groups, parsing failures and the exact duplicate policy. Retain source files, sheet names, rows and raw values.
7. Save the configuration for reuse when requested. A later run writes to a new directory, with the same approved mapping and scope.

Practical modes

• Append: stack compatible tables with a union of fields, optionally remove exact duplicate records.
• Reconcile: compare unique composite keys across left/right sources, show missing-side keys and field differences with numeric tolerance.
• Period comparison: use reconcile after aligning dimensions and excluding period from the identity key; explain scope changes separately.
• Workbook export includes 总览, 合并明细, 差异, 待确认, 处理记录.

Runtime and limits

Python 3.10+; openpyxl for XLSX. No external service or extra API key. See scripts/requirements.txt. CSV input is supported; XLS and image/PDF tables must first be converted with an available tool and checked against the original. The script does not OCR, recalculate formulas, or preserve source charts/macros. Formula cells are quarantined for a value export instead of treating a missing cache as zero. It creates a new data workbook, not a style-preserving source edit.

See examples and behavioral tests. Follow the user's chosen key and duplicate policy; never delete the source files.