“The file is broken” usually means one of five specific things. Identifying which one you have is most of the work, because the repairs are different and applying the wrong one makes it worse.
Throughout: do not open the file in a spreadsheet to investigate. Excel’s import will change values before you have looked at them, and then you are diagnosing a second problem on top of the first.
1. Ragged rows
What you see: most rows have the same number of columns and a handful have more or fewer. Values after a certain point in the row are shifted into the wrong columns.
What happened: a cell picked up a tab character, usually pasted in from elsewhere. That single tab reads as a column boundary, so the row gained a column and everything after it slid one place right.
The repair: find the row, find the cell that got split, and rejoin it — either by deleting the tab or by quoting the cell. Rows shorter than the header are the opposite case and usually mean trailing empty cells were trimmed on save; padding them back is safe.
What you must not do is delete the extra column across the whole file. The other rows are fine, and their last column would go with it.
2. Invisible whitespace
What you see: nothing. Two values look identical, a lookup fails, a join produces no match, a translation key is not found.
What happened: one of these is sitting in the cell:
- a trailing space after a key, usually from a copy-paste that grabbed one character too many;
- a non-breaking space (
U+00A0) where a normal space belonged, typically pasted from a web page or a word processor; - an ideographic space (
U+3000), the full-width space used in Chinese and Japanese text, which looks like a wide gap and is not a normal space at all; - a zero-width character (
U+200B,U+FEFF), which has no visual appearance whatsoever.
The repair: you need a tool that renders these visibly, because by definition you cannot spot them by eye. Then trim or replace them. Do it as one operation across the file rather than cell by cell — otherwise you fix the three you found and leave the forty you did not.
3. Mojibake
What you see: é where é belonged, â€" where a dash was, or a scattering of � replacement characters.
What happened: the file was written in one encoding and read in another. é specifically is UTF-8 bytes interpreted as windows-1252 — the most common pairing by a wide margin.
The repair: re-open the file with the correct encoding, do not try to find-and-replace the broken characters. A search-and-replace fixes the examples you can see and leaves every other affected character in place, and it cannot recover a character that was replaced by � on the way in.
If the file has already been saved after being misread, the original bytes may genuinely be gone. Go back to the source export if one exists.
4. Broken quoting
What you see: one row is enormously long, containing what should have been the next twenty rows. Or a quote character appears in the middle of a value where it does not belong.
What happened: a cell opened a double quote and never closed it, so the parser treated everything after it — including newlines — as part of that one cell.
The repair: find the first row that went wrong; the unbalanced quote is at its start. Either close it or remove it. This is one of the few cases where a plain text editor is genuinely useful: you are looking for a single character and you know roughly where it is.
5. Duplicate or empty keys
What you see: two rows with the same identifier, or a row whose key column is blank.
What happened: usually a merge, an append that ran twice, or a manual edit that cleared a cell.
The repair: this one no tool can do for you, and any tool that offers to is guessing. Only you know whether the duplicate is a copy to delete, a newer value that should win, or two genuinely different things that were given the same name. What a tool should do is find them and take you to them — which is exactly where automatic repair should stop.
A working order
- Fix the encoding first. Everything else you do is on top of the text, and if the text is wrong you will repair the wrong characters.
- Then the structure — quoting, then ragged rows. Both change where the column boundaries are, so nothing about the columns is trustworthy until they are settled.
- Then the content — whitespace, then duplicates. These are per-cell and safe to do in bulk.
- Export with the original settings. Same line ending, same byte order mark, same quoting. A repair that also silently converts
CRLFtoLFhas broken the file for whatever reads it next.
Doing it here
The editor on this site was built around exactly this list. Open a file and it reports what it found: ragged rows, invisible characters with their exact code points, encoding, duplicate keys. Whitespace is drawn in the grid where it sits, so a trailing space is something you can see rather than something you infer.
The repairs that are safe to automate are offered as one action across the whole file and land in a single undo step, so trying one costs nothing. The repairs that are not safe — the duplicate keys, above all — are pointed at and left alone.
Nothing is uploaded: the file is read, repaired and written by your browser.