Diagnose a Broken CSV Before You Try to Repair It

“Invalid CSV” is rarely a useful diagnosis. The file may use a semicolon delimiter, contain one unclosed quote, repeat a heading, mix encodings, or simply exceed the destination's limits. Each problem needs a different response.

Start by preserving the evidence. Make a copy of the failing file and do not open and save it in Excel. A spreadsheet application may reinterpret dates and identifiers or rewrite the quoting, leaving you with a different file from the one that failed.

Capture the failure first

Record the exact error message, the destination system, and the step where it occurred. “Import failed” is not enough. A line number, column name, rejected-row file, or parser message can reduce hours of guessing to one inspection.

Also record:

• filename and byte size;
• expected number of rows;
• where the export came from;
• whether the first row contains headings;
• expected delimiter and encoding; and
• whether the failure affects every row or only some.

Run a structural diagnostic

Open CSV Diagnostic and select the untouched copy. The tool examines the file locally and produces a JSON report covering the likely delimiter, row and column counts, headings, line endings, malformed row shapes, and basic value patterns.

The report does not certify that the business data is correct. It helps answer a narrower question: does the text have a consistent tabular structure, and where should investigation begin?

Check the delimiter

Despite the name, a CSV does not always use commas. Semicolons are common in regions where the comma is used as a decimal separator. Tabs and pipes also appear in exports.

If every line appears in one column, the parser probably chose the wrong delimiter. If decimal values split into extra fields, the delimiter or quoting may be wrong.

Do not replace every comma with another character. Commas can be legitimate data inside quoted fields.

Compare row shapes

For a table with eight headings, each parsed data row should normally have eight fields. A row with seven or nine needs inspection.

Common causes include:

• an unquoted comma inside an address;
• a missing closing quote;
• an unexpected line break inside a field;
• an extra delimiter at the end of some rows; and
• manual edits made without CSV-aware software.

Open the reported line in a plain-text editor and compare it with the rows immediately before and after it. Remember that a quoted field can legally contain a line break, so a physical line number is not always the same as a CSV record number.

Inspect the headings

Blank and duplicate headings may be accepted by one application and rejected by another. They also create ambiguity in JSON objects, database tables, and joins.

Rename headings deliberately. Avoid silently discarding one duplicate column. If two columns are both named Email, determine whether they mean personal email, work email, or repeated data.

Whitespace matters too. customer_id and customer_id look similar on screen but are different strings.

Separate encoding from structural errors

Encoding problems usually appear as replacement characters, mojibake, or a failure to decode the file. They do not usually cause every row to have the wrong number of fields unless the delimiter bytes were also misread.

Determine the source encoding before converting. UTF-8 is a sensible destination for modern systems, but decoding a Windows-1252 file as UTF-8 and then saving it can permanently damage characters.

Use Fix CSV Encoding only after identifying the likely source encoding, then re-run the diagnostic on the output.

Check data types after structure

A structurally valid file can still fail an import because a value violates the destination schema:

• text appears in an integer column;
• dates use several formats;
• a required field is blank;
• decimal commas conflict with the parser locale;
• identifiers exceed a numeric limit; or
• a category contains a value the destination does not recognise.

Profile the whole column, not just the first few rows. A parser may infer a type from an early sample and fail much later.

Repair one class of problem at a time

Choose the smallest relevant repair:

• use Repair Broken CSV for quoting or row-shape problems;
• use Fix CSV Encoding for a known encoding mismatch;
• use Change CSV Delimiter when the destination requires another delimiter; and
• use Remove Empty Rows only when blank records are truly unwanted.

Save each result as a new file. Re-run the diagnostic and compare counts after every transformation. Several simultaneous “clean-ups” make it difficult to identify which step lost data.

Validate against the destination

The final authority is the system receiving the file. Test a small, representative subset that includes:

• an ordinary row;
• the longest text field;
• a blank optional field;
• non-ASCII characters;
• the largest and smallest numbers; and
• a date that could be misread by locale.

After a successful full import, reconcile record counts and rejected rows. “The progress bar reached 100%” does not prove that every record arrived unchanged.

Good CSV repair is investigative work. Preserve the source, name the actual defect, apply one controlled change, and verify the result.