🧹 Data Cleaning · 7 min read

CSV File Not Loading or Showing Data? Six Causes and Fixes

Almost always one of six things: the file is empty or truncated, the delimiter does not match, the encoding is misread, the rows exceed the program's limit, a filter is hiding the data, or the file is not a CSV at all.The symptom tells you which one. An empty window points at size or encoding. One fat column points at the delimiter. A file that stops at exactly 1,048,576 rows is Excel's ceiling, not corruption. Diagnose by symptom instead of reinstalling the program.

First, three checks that take ten seconds

Before changing any settings, confirm the file is what you think it is. These three answers rule out half the causes.

• Size. A 0-byte file is a failed export or a download that never finished. Nothing in the file can fix that; download it again.
• Extension. Files named .csv that are really HTML (a saved report page) or XLSX (a renamed workbook) will open to a mess or to nothing.
• First two lines. Open in a text editor. You should see header names with separators between them, and one record per line. This single look distinguishes content problems from display problems.

Cause 1: the file is empty or cut short

Downloads over flaky connections stop partway, and some export jobs write a header then fail. Both produce a file that opens, looks wrong, and contains less than it should.

Open the file as text and check the final line. A complete CSV ends with a full record plus a line break; a truncated one ends mid-field, often mid-quote. RFC 4180, the document that registers the CSV format, requires CRLF line breaks and specifies that a field containing a separator or a line break must be wrapped in double quotes — a rule that matters here, because an unclosed quote swallows everything after it.

If the file ends inside a quoted field, you have two options: re-export from the source, or repair by deleting the partial trailing record. Deleting is safe when the last row is incomplete, and risky when the quoting broke earlier in the file.

Cause 2: the delimiter does not match

This is the most common cause of the "everything in column A" complaint, and it is a locale problem rather than a data problem. Excel uses the system list separator: comma in US and UK locales, semicolon in much of Europe and Latin America. A semicolon-separated export opened on an English machine has no comma to split on, so each line lands in one cell.

The clean fix is a one-pass conversion rather than a settings change, because the next file from the same source will arrive with the same delimiter. Our guide to a CSV stuck in one column walks through the import dialog, and the delimiter converter rewrites the file in place.

Cause 3: the encoding is misread

A file saved as UTF-16 or as UTF-8 with a byte order mark can open as a single column of garbled text, or as nothing at all, because the first bytes look like a control character rather than a header. Excel reads unmarked files as the system codepage, which on a Western Windows machine means Windows-1252 — and that is where accented names become something else entirely.

Opening through Data → From Text/CSV shows a preview with an encoding selector, and the preview is the point: you can see the columns split correctly before committing. If the preview is wrong in every encoding you try, the file may not be text at all. The encoding guide lists the byte markers for UTF-8, UTF-16 and Windows-1252, and fixing garbled text covers the repair path once the encoding is identified.

Cause 4: the row count passes the program's limit

Excel holds 1,048,576 rows and 16,384 columns per worksheet, and 32,767 characters per cell, per Microsoft's published limits. A CSV is not bound by any of that. So a 3-million-row export opens, shows its first 1,048,576 rows, and stops without an error message. People discover this when a total is short by two thirds.

The row count check is simple: the status bar shows the last row number, and a text editor shows the total line count. If those disagree by a factor of two or three, you have hit the ceiling. Split the file into parts, or load it through Power Query, which streams rather than pasting into cells. For the full set of workarounds, see opening a CSV that is too big for Excel.

Cause 5: the data is there but hidden

A file can load perfectly and still look empty. Three settings do this: an autofilter left active on a value that matches nothing, rows or columns collapsed by a group outline, and a frozen pane scrolled to a blank region. Each takes one click to undo, and each has fooled someone into re-exporting a file that was never broken.

Check the filter icons in the header row, then the row numbers down the left edge for a gap in the sequence. A jump from 12 to 400 means rows 13 through 399 are hidden, not missing.

Cause 6: it is not a CSV

Two impostors cause most of the remaining cases. The first is an HTML table saved with a .csv extension, which happens when a web report is saved rather than exported; it opens as markup in a text editor. The second is a zipped XLSX renamed to .csv, which produces binary garbage from the first byte.

The text-editor look answers this in seconds: readable headers and commas mean a real CSV, angle brackets mean HTML, and an unreadable first line means a binary format. Renaming the extension back to .xlsx usually recovers the workbook.

If the browser is the one refusing

Sometimes the file is fine and the upload is the failure. A 200 MB CSV posted to a form with a 2 MB limit returns an error that reads like a data problem, and a browser that downloads instead of displaying is obeying a content-type header rather than reporting damage. The download-instead-of-open case and the failed upload case are both covered separately, because neither needs a repaired file.

Frequently asked

My CSV opens fine in Google Sheets but not in Excel. Which one is wrong?

Neither. Sheets guesses the delimiter and encoding for you, Excel defers to the system locale. The file is ambiguous, and both programs resolved the ambiguity differently. Import explicitly in Excel and the behaviour matches.

Does a CSV support multiple sheets?

No. The format holds a single table with no sheet structure. If you need more than one table in one file, that file is a workbook, not a CSV, and splitting it into separate files is the usual fix.

Why are some cells blank that were filled in the source system?

Often a quoting problem: a value containing a comma, quote or line break was written without wrapping quotes, so the parser split it and shifted the rest of the row. Checking a known blank cell against the raw text tells you whether the value is missing or merely misplaced.

Sources for this article: RFC 4180 for the CSV delimiter, quoting and CRLF line rules; Microsoft Support's Excel specifications and limits for the 1,048,576 row, 16,384 column and 32,767 character figures; and Microsoft's documentation for the Text/CSV import dialog and its encoding selector. The locale behaviour of the list separator follows the Windows regional setting. All figures checked on the day this page was published.