Data cleaning before you merge CSV files

Merging two CSV exports without cleaning them first is how you get doubled headers, shifted columns, and dates that turn into serial numbers. Toolsy does not ship a dedicated “CSV merge” product. You clean each file, align columns, then concatenate or join in Excel, Sheets, or a script. This checklist covers the failures that show up before the merge click. Use CSV to JSON to inspect shape, Hidden Characters for invisible junk, and CSV to Excel when you need a workbook for the final stitch.

Why merges fail when the sheets “look fine”

Your eyes skip BOM markers, trailing spaces, and id versus ID. Importers do not. One file from US Excel uses commas; the EU export uses semicolons; a third tool writes TSV and names it .csv. Pasting those stacks into one sheet creates a single column of garbage or an extra phantom field.

OCR table dumps and CRM exports add a second class of pain: merged header rows, notes in column A, and totals rows at the bottom. Those rows must leave before any append. Export OCR tables to CSV: best practices covers the OCR side of that cleanup.

Align headers before you touch row data

Write the canonical header list in a notepad. Rename both files to that list. Do not merge while one side still says email_address and the other says Email. Case and punctuation differences create duplicate columns that look identical in a hurry.

Header QA checklist

Open each CSV in a text editor. Confirm one header row, no title line above it, no blank line before row 1. Strip a UTF-8 BOM if the first header shows  or a weird prefix. Paste a suspicious header into Hidden Characters when imports report duplicate keys that you cannot see.

Delimiter agreement

Agree on comma, semicolon, or TAB for the cleaned outputs. CSV to JSON lets you force a delimiter when Auto guesses wrong. Convert both sources to the same delimiter before you append rows in a spreadsheet.

Clean values that break joins and appends

Normalize casing on join keys if your merge is a true join, not a blind append. Trim leading and trailing spaces on IDs and emails. Replace blank strings with a real empty field policy so "" and missing cells do not fork your dedupe logic.

Dates and numbers

US exports often store dates as M/D/YYYY text; EU exports use D.M.YYYY or ISO. Spreadsheet merges that reopen CSV through Excel can corrupt dates again. Prefer a clean path through CSV to Excel after text cleanup, and read CSV to Excel without broken dates before you trust a re-opened file.

Numbers with thousands separators or currency symbols should become plain numerics if you will chart or sum after the merge. Keep a raw text copy of the originals in case you need to replay the cleanup.

Invisible characters

Zero-width spaces inside keys and non-breaking spaces in “blank” cells survive visual review. Invisible characters breaking your CSV walks the symptoms. Run suspect cells through Hidden Characters before you declare the file merge-ready.

Walkthrough: inspect one file in the browser

Open CSV to JSON. Paste file A or drop it. Set the delimiter if columns collapse. Scan the first JSON objects for weird keys and null-looking strings. Fix the CSV text, then re-paste until keys match your canonical list. Repeat for file B.

When you need a spreadsheet to sort and filter during cleanup, convert the cleaned CSV with CSV to Excel. Conversion stays in your browser; nothing uploads for these tools. For API-bound fixtures after the merge, see CSV to JSON for API mocks. For the reverse direction, JSON to CSV for Excel users keeps Excel-friendly UTF-8 downloads in mind.

How to merge after cleaning (outside Toolsy)

For a simple append, place file A in a sheet, paste file B under it without repeating the header, and save a new CSV. For a join, use spreadsheet VLOOKUP/XLOOKUP, Power Query, or a small script on the shared key. Toolsy stops at cleanup and conversion; the join logic stays in your sheet or code.

Name the output with a date stamp. Keep raw inputs read-only so a bad merge does not overwrite the only copy of the CRM dump.

Check the merged result before you share it

Count rows: cleaned A plus cleaned B minus intentional dedupes should match expectations. Spot-check five join keys from each source. Sort the email column and look for duplicates you meant to drop. Open the merged file in a fresh session so cached Excel types do not hide problems.

Failure modes to re-run

If column count jumps by one, hunt delimiter collision or an unquoted comma. If the first header looks wrong, hunt BOM. If dates become five-digit numbers, replay the date cleanup path from the sibling Excel article. If one vendor’s rows vanish, you filtered on a header mismatch rather than a real empty set.

Related tools when the “CSV” is not ready yet

OCR photos need table export discipline first. JSON API dumps need JSON to CSV before spreadsheet merges. Charts from a clean table belong under Quick charts from CSV in HTML once the merge is stable.

Line-oriented tag lists sometimes pretend to be CSV. Sort and dedupe those with line tools when you only have one column of labels, then paste into a proper sheet if you still need a merge with richer columns.

Limits and honesty

There is no Toolsy one-click CSV merge. Browser converters help you inspect and re-encode; they do not reconcile business keys for you. Files stay on your device for CSV to JSON, JSON to CSV, CSV to Excel, and Hidden Characters. Huge multi-gig warehouse merges belong in proper ETL, not a paste box.

Do not upload regulated personal data to random online “merge CSV” sites when a local spreadsheet will do. Prefer local cleanup for payroll, health, and student exports.

Frequently asked questions

How do I merge two CSV files safely?

Clean headers, delimiters, dates, and invisible characters on each file first. Append or join in Excel, Sheets, or a script. Inspect with CSV to JSON before you combine rows so key names match. Keep raw originals read-only until the merged output passes row counts and spot checks.

Does Toolsy have a CSV merge tool?

No. Wave content and product pages cover conversion and cleanup helpers, not a dedicated merge product. Use the checklist here, then merge in your spreadsheet or code. That honesty keeps you from hunting a button that does not exist.

What should I fix before concatenating CSV rows?

One shared header list, one delimiter, no title rows or totals rows, trimmed join keys, and no BOM on the first header. Remove blank separator lines between sections. Confirm date formats will survive the tool you use to open the result.

How do invisible characters break a CSV merge?

They change header identity and join keys without a visible clue. Imports report extra columns or failed matches. Paste suspect text into Hidden Characters and read Invisible characters breaking your CSV for the common Unicode offenders.

CSV to JSON or CSV to Excel for cleanup?

Use CSV to JSON when you want to see keys and types quickly. Use CSV to Excel when you need filters, sorts, and a workbook for the human merge step. Many workflows use both: JSON inspect, then Excel stitch.

How do I handle comma vs semicolon CSV files?

Detect the delimiter per file, convert both to one convention, then merge. Auto-detect fails on sparse first rows. Force comma or semicolon in CSV to JSON when columns collapse into one field.

Why do dates break after I merge and reopen in Excel?

Excel re-parses date-like text using locale rules and sometimes stores serial numbers. Clean and standardize dates before the merge, prefer a controlled CSV to Excel path, and follow CSV to Excel without broken dates.

Should I dedupe before or after merging?

Dedupe within each source first if duplicates are local export artifacts. Dedupe after append when the same customer appears in both systems. Decide the business key before you delete rows so you do not drop the newer phone number by accident.

Is browser CSV cleanup private on Toolsy?

Yes for the listed converters and hidden-character tool: work stays in your browser and is not uploaded for those pages. You still choose what you paste. Highly regulated files may require offline policy regardless of browser privacy.

What if my “CSV” came from OCR?

Expect broken headers, split cells, and junk rows. Clean the table export before any merge. Use Export OCR tables to CSV: best practices so you are not merging two partially recognized grids into a third mess.

After a clean merge, sibling reads that help next steps include JSON to CSV for Excel users and Quick charts from CSV in HTML.