Why a CSV File Won’t Open in Excel, and How to Fix It

The common causes are more than 1,048,576 rows, another program holding the file open, a delimiter that does not match your Windows list separator, a UTF-8 file without a byte order mark, a value longer than a cell can hold, not enough memory, or Excel’s security settings. To look inside the file without Excel, open it in RowSlice’s free CSV Viewer, which pages through and searches a CSV of any size in your browser without uploading it.

You double-click a CSV and Excel shows an error, or opens a window with half the data, or puts everything in one column. CSV is plain text, and almost every “won’t open” problem comes from what Excel assumes when it reads that text, or from another program holding on to the file.

Find your symptom below. Where a message is quoted, it is either what Excel 2021 on Windows 11 showed when we recreated the problem, or Microsoft’s own wording, and the guide says which.

“This Data Set Is Too Large for the Excel Grid”

Microsoft documents the full warning as “This data set is too large for the Excel grid. If you save this workbook, you’ll lose data that wasn’t loaded.” Its explanation is that the data set “exceeds the number of rows or columns that’s available in Excel”, so a file with too many columns gets the warning as well as one with too many rows. A worksheet holds 1,048,576 rows by 16,384 columns, as Microsoft’s Excel specifications and limits page lists. When we opened a CSV with 2.5 million rows, Excel loaded exactly 1,048,576 rows — the header and 1,048,575 records — and the rest simply was not there.

Take the second sentence of the warning literally. The workbook is incomplete, and saving it over the CSV deletes more than half the records from the file. Close without saving. If you need to keep what Excel did load, Microsoft’s advice is File › Save a Copy under a name that makes clear the copy is truncated. Where the menu has Save As instead, use that: Microsoft says the two work the same. To read the rows Excel left out, open the same CSV in the CSV Viewer and go to row 1,048,576.

To work with every row, either split the CSV into parts that each fit, or follow Microsoft’s route for analyzing all of it in Excel for Windows: open a blank workbook, go to Data › From Text/CSV, import the file, and choose Load To › PivotTable Report, opening the arrow next to Load first if there is no Load To button. The PivotTable then summarizes the entire data set. Opening a CSV that is too large for Excel covers other routes, and Excel’s row limit explains where the number comes from.

“Cannot Be Accessed” or “Locked for Editing”

We held a CSV open in another program, the way an export tool does while it is still writing, and asked Excel to open it. Excel said:

“'locked.csv' cannot be accessed. The file may be corrupted, located on a server that is not responding, or read-only.”

Of the three explanations, none was true: the file was complete and local, just locked. Wait for the export or sync to finish, or close the other program, then open the file again. While the lock lasts, copying the file usually fails too, which is itself a clue that the problem is a lock and not damage.

“Locked for editing” is the related message when Excel itself has the file open. Microsoft’s support article on it notes that “sometimes the file is locked by another user, and other times the ‘another user’ is actually you”, so look for the same file already open in another Excel window before blaming a colleague. It works the other way too: while a CSV was open in Excel, a program trying to overwrite it failed with “The process cannot access the file … because it is being used by another process.” If a scheduled export keeps failing, check that nobody left its output open.

A Message About a SYLK File

SYLK is an old spreadsheet exchange format whose files begin with the letters ID. Microsoft’s original support article describes the error “SYLK: File format is not valid” and gives the cause: it happens when “the first two characters of the file are the uppercase letters ‘I’ and ‘D’”, and “does not occur if the first two letters are lowercase”. Plenty of exports start with a column called ID, so Excel takes the CSV for a SYLK file and fails to read it.

Recent versions may have moved on. When we opened a CSV beginning ID,name in Excel 2021 by double-clicking it, it opened normally with no message at all. So if you see a SYLK message, you are probably on an older version, or on a setup that behaves like one; the cause and the fix are the same.

The fix is to change the first two characters. Rename the column to id or Id in a text editor, or ask whoever produces the export to do it. The rest of the file does not need to change.

Everything in One Column

Excel opened the file but did not split it into columns, so each row sits in column A with its commas or semicolons still visible. Excel splits a double-clicked CSV using the list separator in your Windows regional settings, and the file uses a different character. Why a CSV opens in one column covers the setting and four fixes; the quickest is to change the CSV’s delimiter to the one Excel expects.

Strange Characters Instead of Accents

Café shows as Café, or Zoë as Zoë. The file is UTF-8, but it lacks the three-byte mark at the start that tells Excel so. When we opened the same UTF-8 file with and without that mark, Excel showed Zoë and Zoë respectively. Nothing in the file is damaged, as long as you do not save it from Excel while it looks wrong. Converting the CSV to UTF-8 with RowSlice adds the mark, and CSV encodings explained covers the other combinations.

Rows That Break Partway Down the Sheet

Excel limits a cell to 32,767 characters. A CSV has no such limit, and exports that include email bodies, logs or long JSON can exceed it. We opened a CSV whose second column held 40,000 characters, followed by a third column containing END:

Excel did not warn about it. A new row appeared that does not exist in the file, and everything after the long value moved. If a sheet has a stray row whose first cell is a fragment of text, look for an overlong value in the row above. Excel cannot display such a value in one cell, so shorten that column before opening, or work with the file outside Excel. The characters are all still in the CSV, as long as you do not save over it.

Excel Freezes or Runs Out of Memory

A file can fit within the row limit and still be too much. Microsoft lists messages such as “Excel cannot complete this task with available resources. Choose less data or close other applications.” and “There isn’t enough memory to complete this action.” Its explanation is that “in any 32-bit application, there’s a 2-GB limitation”, while “the 64-bit version allows all available physical RAM on the machine to be used.”

Close other workbooks and try again. If that does not help, the lasting fixes are a 64-bit version of Office, or less data: split the file into parts, or keep only the rows you need by splitting the CSV by column value, for example one region or one customer at a time.

Protected View and File Block

Two settings in Excel’s Trust Center can stop a CSV from opening normally, and both are more common on work computers, where an administrator sets them.

Protected View opens a file read-only with a yellow bar across the top. For CSV files, Microsoft describes an Excel setting, “Always open untrusted Text-Based files (.csv, .dif and .sylk) in protected view”: when it is on, a CSV from an untrusted location opens this way. It is not always on: in our test, a CSV marked by Windows as downloaded from the internet opened in Excel 2021 normally, without Protected View. If you do see the bar and trust the file, select Enable Editing.

File Block can prevent text files from opening at all. The blockable types include “Text files”, covering *.txt, *.csv and *.prn, and the message reads “You are attempting to open a file type <File Type> that has been blocked by your File Block settings in the Trust Center.” The setting is under File › Options › Trust Center › Trust Center Settings › File Block Settings. On a managed computer it may be locked, in which case ask IT.

When Nothing Here Matches

Open the file in a text editor. If you see rows of text with a consistent separator, the file is fine and the problem is in how it is being opened; import it with Data › Get Data › From File › From Text/CSV, which lets you choose the delimiter and encoding instead of letting Excel guess. If you see a wall of unreadable symbols, it may not be a CSV at all — an .xlsx or a compressed file that was renamed. If you see braces and square brackets instead, the file is JSON with a .csv name on it, which no CSV reader can make columns out of: convert the JSON to CSV first, which flattens nested keys into columns such as address.city. If the text editor also struggles, the file is simply large, and opening a CSV that is too large for Excel is the guide you want.