CSV comparison, joins and pivots: check records before reporting

Example: reconcile two order exports

An inventory team receives a fresh product export every Monday. Last week product 001 belonged to Design; this week it belongs to Operations and the rows arrive in a different order. A line-by-line diff produces noisy changes. The useful question is whether the record with key 001 changed, which fields changed, and whether any products were added or removed.

Agree on keys and empty values

Start by writing down the key contract. It must identify one record, remain stable across exports and stay in the same representation. Turning 001 into the number 1 breaks that contract even if a spreadsheet displays both similarly. Blank or repeated keys need a deliberate resolution; a tool that arbitrarily chooses one matching row may produce a convincing but incorrect report.

Compare before joining and summarizing

Use keyed comparison before joining the lookup table. For small UTF-8 CSV exports, inspect the added, removed and changed-cell lists plus column-name changes, then download the complete JSON and applicable CSV reports. Neatbo limits each side to 1 MiB; larger tables need another workflow. CSV exports guard formula-like values, while the JSON keeps exact strings for audit. After approving the change set, join the department lookup with a left join when every original product must remain visible. An inner join drops unmatched products by design; that is useful for an intersection report but risky when the report is meant to describe the full inventory.

A small, checkable example

001,Ada → 001,Ada Lovelace is a changed record, not a deletion plus insertion. A reordered unchanged table should have no changed cells.

Keep the evidence behind a report

Only then build the pivot. Run count and sum separately: this tool produces one metric and one row dimension per run. Check the count before summing amounts, inspect a group with no matches and retain a real zero as distinct from a missing combination. If the report is split by department, distribute the ZIP with its manifest: each CSV retains the header and the manifest connects numbered filenames, original group labels and row counts. A final reviewer should be able to trace one source record through comparison, joining and aggregation without guessing which transformation happened.

Common assumptions and better checks
Do not rely only on	Also check
Equal row counts	Compare key sets and individual fields
A successful join	Check lookup uniqueness and unmatched records
Equal totals	Check group definitions, empty combinations and numeric precision

Keep identifiers as strings and check leading zeroes.
Sample additions, removals and changes, then reconcile grouped totals.
Use test conditions in JSON Patch and reject partial edits.

Separate syntax migration from a data contract

Consider a device configuration and an order table arriving together. The device INI contains literal Windows paths, while localized Java Properties messages use backslash escapes. Treating both as generic key=value lines can change the messages even if the resulting JSON looks valid. Choose the correct syntax, retain strings, and check a sample in the receiver.

A static HTML export may be easier to deliver as CSV, but extracting cells does not establish that the data satisfies an import contract. After choosing the right table, define the fields that must be present, the permitted values and numeric limits. A blank optional field differs from a missing required value. Preserve rejected records, use physical line positions for multiline cells, and avoid treating unvalidated columns as approved data.

Separate stored declarations, preserved bytes and confirmed schema

A local migration can contain several distinct contracts: fixed-width code-point fields, workbook formulas with untrusted cache freshness, and CSV columns whose types remain unconfirmed. Extract each explicitly, retain originals and keep suggestions separate from approved import rules.

For supporting files, redact selected JSON values while reviewing visible key names, apply patches against exact coordinates and endings, and retain frontmatter body hashes. EditorConfig and npm lock reports explain the supplied declarations only; do not equate them with editor behavior, installed dependencies or an executable database migration.

References

CSV and JSON data reconciliation
Reference for the relevant format and processing rules.
Inspect payloads before applying changes
Reference for the relevant format and processing rules.

Tools used in this article

CSV to JSON →Turn your table into ready-to-use data.JSON to CSV →Turn JSON records into a CSV table.View CSV →A clear look at your data, no spreadsheet app needed.Clean CSV →Choose how to remove duplicate records or empty rows.JSON to YAML →Convert JSON to YAML while keeping its structure.YAML to JSON →Read your YAML and convert it to JSON.Excel to CSV →Choose an Excel worksheet and export it as CSV.CSV to Excel →Make an Excel workbook from a CSV file.JSON formatting workspace →Format or minify strict JSON, sort object keys, and encode or decode strings while preserving raw number tokens.Regex tester →Try a pattern and see what it matches in your text.Compare text →See what changed, side by side.HTML formatter →Format HTML indentation so its structure is easier to read.CSS formatter →Tidy the indentation and line breaks in your CSS.JavaScript formatter →Format JavaScript without running it.SQL formatter →Format PostgreSQL-style SQL indentation and keywords.UUID generator →Generate UUIDs to copy or save.JSON to TypeScript →Turn an API response into nested types with arrays, unions and null values.NDJSON ↔ JSON →Convert line-delimited logs to a JSON array, or split an array into records.CSV delimiter converter →Switch between commas, tabs, semicolons and pipes while preserving quoted cells.Select CSV columns →Keep the exact CSV fields you need and put them in the required order.Transpose CSV →Swap rows and columns in a rectangular delimited table.Sort CSV rows →Sort whole CSV records by one header using text or exact decimal values.Filter CSV rows →Filter CSV by a named column with exact, contains or not-equals text matching.Merge CSV files →Append two pasted CSV tables or 2–20 files with matching headers into one table.Statistics calculator →Calculate list mean, median, variance and standard deviation, or exact decimal statistics for a CSV column.Markdown table converter →Convert CSV or JSON records to Markdown tables and export Markdown tables back to CSV.Flatten and restore JSON →Flatten nested JSON into reversible JSON Pointer paths, or restore a dot-key map as nested objects.JSON Pointer extractor →Read one value using a precise RFC 6901 path.Merge JSON objects →Apply an override object while retaining untouched nested keys.JSON to typed XML →Convert JSON to XML with type attributes for objects, arrays and scalar values.XML to JSON tree →Convert XML with mixed text and elements to an order-preserving JSON tree.XML formatter →Indent XML structure while keeping mixed text, CDATA and comments intact.SQL IN list →Turn a pasted spreadsheet column or one-value-per-line TXT file into a quoted SQL IN fragment.CSV to SQL INSERT →Generate reviewable PostgreSQL or MySQL INSERT scripts from CSV for an existing table.cURL to fetch →Convert a copied cURL API request into browser fetch code.HTTP headers parser →Inspect a copied HTTP header block and preserve repeated fields in JSON.ENV and JSON converter →Convert .env assignments to a string-valued JSON object or environment list, with exact value checks.Cron expression checker →Check a five-field Unix cron schedule and preview its next matching minutes in a chosen time zone.chmod calculator →Translate octal permissions including setuid, setgid and sticky bits.CSV comparison by key →Compare two small CSV exports by a unique key and inspect added, removed and changed cells.CSV lookup and join →Join two small CSV tables by selected key columns. Preview matches and choose left, inner or full output.CSV pivot table →Group records into a cross-tab and calculate sums, counts, means, minima or maxima for each group.Split CSV by column →Separate one CSV into files for each distinct group and download a manifest mapping group values to output filenames.JSON structural comparison →Compare JSON values structurally and locate changes with JSON Pointer paths, without losing large integer precision.JSON Merge Patch →Apply an RFC 7396 merge patch locally: update object fields, delete fields with null and replace arrays as whole values.JSON Patch workspace →Apply RFC 6902 JSON Patch operations in order, review array index shifts and download the complete result.NDJSON validation and quarantine →Validate pasted or imported NDJSON line by line; download valid records and a report with every rejected original line.INI and JSON converter →Move single-line INI configuration to a structured JSON document, or rebuild INI from string values.Java Properties and JSON converter →Decode Java .properties escapes and continuations, or export a string-valued JSON map as an ASCII properties file.HTML table and CSV converter →Extract a selected static HTML table as CSV, or build a safe HTML table from CSV cells.CSV field constraint checker →Check CSV fields against local column rules and download valid records, rejected records and a cell-level issue report.Fixed-width and CSV converter →Split fixed-width records by explicit Unicode code-point widths, or pad CSV fields into a fixed-width text file.XLSX formula inventory →List ordinary formulas across workbook sheets with cell addresses, visibility and raw cached values, without recalculating.JSON structure redactor →Replace all JSON scalar values or explicit object keys with a mask while retaining the document structure and a value-free path report.Exact unified diff applier →Apply one text-file unified diff at its declared lines with exact context and explicit line-ending handling.Markdown frontmatter extractor →Separate supported JSON or YAML frontmatter from Markdown and verify that the remaining body bytes stay unchanged.EditorConfig path inspector →Resolve declared properties for explicit paths under one EditorConfig file and trace section order, overrides and unset.npm lock declared-copy inventory →Inspect package-lock v2/v3 installation declarations and separate repeated names, aliases, links, workspaces and unknown versions.CSV to SQL DDL draft →Draft a quoted CREATE TABLE statement from CSV headers and review sample-based type suggestions without executing SQL.