How to Diagnose a Broken CSV File Before Opening It in Excel

A CSV file that looks broken in Excel is not always damaged. Excel may have chosen the wrong delimiter or encoding, or converted an identifier into a number. Other files really do contain inconsistent rows or unmatched quotes. Distinguish those cases before saving anything: a spreadsheet can hide the original problem and introduce new changes when it writes the file back.

This workflow uses CSV Doctor to inspect a file locally, review proposed changes and export a separate copy. The goal is a defensible import, not a promise that every CSV can be repaired automatically.

Keep a source copy and define the intended structure

Keep the original download or export untouched. Work in a separate folder and give any repaired copy a new name. If a file came from another system, find out which delimiter, encoding and columns that system intended. A specification or a known-good sample is more useful than guessing from the extension.

Suppose you expect an ID, a customer name and a note:

id,name,note
00123,Example Customer,"Delivery, side entrance"
12345678901234567890,Second Customer,"Two lines
in one note"

The leading zero and long ID are intentional text. The comma inside the first note is not a column separator. The line break inside the second quoted note is part of one cell, not a new record. Counting commas or physical lines alone would give the wrong diagnosis.

Check delimiter and encoding first

1. Download CSV Doctor from its product page and read the first-run instructions.
2. Open or drop one CSV, TSV or delimited TXT file into the application.
3. Review the detected delimiter and encoding before interpreting the table.
4. Compare column names and sample rows with the source system’s expected structure.
5. If detection is ambiguous, choose an interpretation explicitly and diagnose again.

CSV Doctor supports comma, semicolon, tab and pipe delimiters. Semicolon-delimited data is common in some export workflows, especially where commas are used in decimal values, but locale alone is not proof of the intended format. A file with one apparent column may simply have been read with the wrong delimiter.

Check readable text as well as column counts. Accented characters turning into unexpected symbols suggest an encoding mismatch. The application reads UTF-8, UTF-8 BOM, UTF-16 LE/BE and Windows-1252 within its documented rules. Suggestions for UTF-16 without a BOM or Windows-1252 require confirmation. It does not support every historical encoding or automatically know what the producer meant.

Review structural findings

Use the diagnostics and source preview together. For uneven rows, determine whether the row is missing a field, contains an unquoted separator, or uses a different structure intentionally. Padding a short row adds an empty field; it cannot recover lost content. Do not remove an extra field just to make the warning disappear.

The first record is treated as a header by default. For a headerless file, turn that option off and diagnose again. Empty or duplicate headers can make imports confusing, but replacement names should reflect the intended columns. Blank records can also carry meaning in some workflows. Neither blank rows nor suspicious columns should be silently deleted.

Quotes deserve special care. A comma-delimited note containing a comma needs quoting, and a double quote within a quoted cell is escaped by doubling it. An unmatched quote may make many later lines look like one record. Failed strict parsing blocks export. Literal-quote mode is a different, explicit interpretation, not a trustworthy reconstruction of the original structure. If a multiline record is ambiguous, request a corrected export rather than guessing.

Separate Excel risks from CSV syntax

A syntactically valid CSV has no cell types. An ID such as 00123 can lose its leading zeros during spreadsheet import; a long integer string may lose significant digits if interpreted as a number. Date-like values and decimal punctuation can also be interpreted differently according to import settings.

Use Excel’s Data → From Text/CSV workflow and set identifier columns to Text before loading them. Depending on your Excel version, you may need to use the transformation step or the text-import wizard to control column types. Microsoft’s Text Import Wizard documentation explains choosing delimiters and column formats. Quoting a CSV value is not a reliable instruction to keep it as text.

Cells beginning with =, +, - or @ can be interpreted as formulas by spreadsheet software. Treat warnings as a reason to review the source, especially for untrusted data. A negative numeric value is not automatically malicious, so a warning is not proof of an attack.

CSV Doctor can optionally prefix formula-risk cells with an apostrophe. That changes cell text and must be an explicit choice. It is not a universal guarantee for every spreadsheet or import configuration. The app itself never evaluates formulas; inspect risky input without pasting it into a spreadsheet cell merely to see what happens.

Review, export and import deliberately

Choose only the repairs you understand. Review the before/after comparison and the listed changes, then export a new file. Choose the delimiter expected by the receiving application, UTF-8 or UTF-8 BOM, and CRLF or LF record separators. Embedded line breaks inside quoted cells are distinct from record separators.

A UTF-8 BOM can help Excel recognize the encoding, as described in Microsoft’s UTF-8 CSV guidance. It does not protect identifiers or prevent formula interpretation. Some other importers expect UTF-8 without a BOM, so follow the destination’s requirements.

Reopen the exported copy in CSV Doctor. Compare record counts, headers and representative cells, including zero-prefixed and long IDs. Read the JSON repair report for applied changes and unresolved risks. If the CSV was saved but its report could not be written, treat the reported partial export accurately rather than assuming both files exist.

Know when to stop

Version 1.0.0 limits inputs to 16 MiB, 100,000 records, 256 fields per record and 1,000,000 cells. It does not silently truncate data to fit. Unsupported quoting conventions, custom delimiters, binary files or badly damaged exports may require the originating system to generate a new file. A repaired CSV cannot prevent Excel from later reinterpreting values; verify the actual spreadsheet import before relying on it.