There are a number of reasons why a CSV file won't open correctly or fails to parse. Nothing inherently governs CSV files for correct structure or contents. You can label any text file as .csv, so it's possible that your file contains invalid content. Below we outline 10 common CSV errors and the fundamental limits of CSV files. Since CSV files have no data size limit, one of the most common issues is the CSV file is too large to import into your program. If you need to open a big CSV file, try Row Zero, a next-gen spreadsheet built for big data.
Properly formatted CSV file example
To better diagnose CSV errors, it's important to understand what a properly formatted CSV file looks like. CSV files are plain-text files generally structured with fields enclosed by quotes and separated by commas:
"Name","Street","ZIP" "Jane Smith","123 Main St","26584" "Oscar Jones","221 Alpine Dr","67153" "Pat Collins","6787 Oak Ln","31212"
There is typically a header row and a new line for each entry. Each row needs to be the same length in terms of comma separated values. When there is missing or inconsistent formatting or invalid characters, it causes issues.
10 Common CSV Errors
Several common CSV import errors can make your CSV fail to parse or open correctly.
- CSV file is too big for Excel or Google Sheets
- Fields not properly enclosed in quotes
- Unescaped quotes
- Inconsistent or incorrect delimiters
- Inconsistent row lengths
- Missing or misaligned headers
- Corrupt data, invalid characters, and locale differences
- Whitespace issues
- Extra, missing, or inconsistent line breaks
- Encoding issues
1. CSV file is too big for Excel or Google Sheets
While there is no CSV size limit for the format itself, it's common for programs to have CSV import limits. For example, the Excel row limit is 1,048,576 and the Google Sheets limit is 10 million cells and a 100MB CSV import limit. If you need to open a CSV more than 1 million rows, you'll need to use a more powerful spreadsheet like Row Zero.
Solution: Row Zero is a next-gen spreadsheet built for big data that can easily open multi-GB, million row CSVs on a free plan and can even support billion row datasets on Enterprise plans. Row Zero is a good alternative to Excel and Google Sheets for big data users.
2. Fields not properly enclosed in quotes
CSV files should be formatted with fields enclosed by quotes and separated by commas. Enclosing fields by quotes is not required, but it ensures that the CSV importer won't get confused by commas or other punctuation within a field. For example, when a field contains a comma like "Boston, MA" it will cause errors if not enclosed in quotes:
"Name","Street","City_State","ZIP" "Jane Smith","123 Main St","Boston, MA","26584" "Oscar Jones","221 Alpine Dr","Denver, CO","67153" "Pat Collins","6787 Oak Ln","Pittsburgh, PA","31212"
If your CSV does not enclose fields in quotes, Boston and MA in this example, will be considered two separate fields which will likely cause rows to have different lengths and mess up your import.
3. Unescaped quotes
If a field in your file contains quotation marks, they must be escaped, usually by doubling them (""). For example, this CSV would fail because "Bill" adds quotes but does not escape them:
"William "Bill" Jones","345 Cactus Dr","California"
In this instance, the quotes around "Bill" must be escaped by doubling the quotation marks around "Bill":
"William ""Bill"" Jones","345 Cactus Dr","California"
4. Inconsistent or incorrect delimiters
CSV stands for comma separated values, so when programs import CSV files, they expect to see commas used as the delimiter. If a file is missing a comma, has an extra comma, or uses a delimiter other than a comma (e.g., tab, semicolon, or pipe) it can cause the importer to fail to parse the CSV file correctly.
Solution: Ensure your CSV file consistently and correctly uses commas to separate values. If your file uses a different delimmiter, you can specify the correct delimiter in the import settings in many programs. 
5. Inconsistent row lengths
CSV files must have the same number of fields in each row. If one line has more comma separated values than another line, it will lead to CSV parsing errors.
6. Missing or misaligned headers
If you file lacks a header row or the header row is misaligned with the data, it can cause import errors. You should also not use duplicate column names. Repeated column names can lead to ambiguity in parsing CSV files.
Solution: Ensure headers are present and formatted correctly and ensure all column names are unique. Some programs like Row Zero, also allow you to select to ignore the header row and/or skip X number of rows when importing CSV files.
7. Corrupt data, invalid characters, and locale differences
If your CSV file contains corrupted or unexpected data, such as binary data in a text column or invalid characters, it can lead to parsing errors or get interpreted incorrectly and appear as gibberish. You may also experience issues when using number or date formats that differ from the expected locale (e.g. 1,234.56 vs 1.234,56). For locale differences, you may be able to use formulas like NUMBERVALUE to convert to your desired format after import.
Solution: Inspect your file for invalid characters or binary data using a text editor or Python. See troubleshooting CSV errors below for help.
8. Whitespace issues
Leading or trailing whitespace in fields (extra spaces) can cause unexpected results. If your file doesn't import correctly or seems to be missing data, try looking for and removing extra spaces.
9. Extra, missing, or inconsistent line breaks
Extra blank lines, missing line breaks, or improper or inconsistent end-of-line (EOL) characters can disrupt parsing. Here are the two common EOL characters:
- LF (Line Feed) which is \n and used by Linux and macOS
- CRLF (Carriage Return + Line Feed) which is \r\n and used by Windows
Many tools are flexible and accept both LF (\n) and CRLF (\r\n) EOL characters separately but may fail if they both appear in the same file. In most instances, you do not need to worry about EOL characters since most tools produce and ingest CSV files using proper EOL characters. In fact, when you import a CSV file into many text editors and spreadsheets, you will not see the EOL characters by default. Some specialized editors like Notepad++ have features to make EOL characters visible. For example, in Notepad++ you can enable "Show All Characters" by going to View, Show Symbol, Show All Characters.
Solution: Enclose fields in quotes, normalize line breaks, and remove extraneous lines. If you suspect there may be an issue with EOL characters, inspect your file using a specialized editor like Notepad++ (see above).
10. Encoding issues
The most widely used encoding for CSV files is UTF-8. This supports virtually all characters, including special characters and international characters and is recommended for compatibility across applications. Row Zero, Excel, and Google Sheets open CSV files encoded in UTF-8. When you export CSV files from any popular software program like Salesforce or Quickbooks, the default encoding is UTF-8, so you typically won't need to worry about this.
Note: While some of the CSV errors outlined above are unique to the CSV file format, several issues like dataset size, invalid characters, extra whitespace, and inconsistent formatting can also cause import errors for XLSX, TSV, Parquet, JSON, HTML, and other file formats.
Troubleshooting CSV errors
It can be a challenge to troubleshoot CSV errors, especially if your file is big or the issue is unclear. Ideally the program you are attempting to import into will give you some information about why CSV import fails. Generally you have four ways to solve CSV errors:
- Try a different program. Depending on your issue, a CSV file may fail to open in one program but open in another. For example, you may have a big CSV fail to import into Google Sheets or Excel due to data size limits, but import fully into Row Zero. If your CSV file fails to import into Excel, Google Sheets, and Row Zero, then the problem is likely with the file itself.
- Reach out to whoever produced the file and let them know that the file won't import correctly. Share any information you have about why the file failed to import.
- Open the file in a big text editor. CSV files are plain-text files so you can open them in a text editor like Notepad or Notepad++ to inspect them, find and correct issues, and resave the file. After cleaning your file, simply save the file with a name that ends with .csv. If you're not sure where to start, you can try manually inspecting and saving just a small portion of the file as a CSV and seeing if it will import correctly as a way to start narrowing down issues.
- Use a programming language like Python to clean your file by programmatically identifying and solving for potential issues like invalid characters, extra whitespace, or missing values. After cleaning your file, you can try importing your CSV into your program of choice.
The Limits of CSV Files
In some ways, CSV files have no limits. You can add the .csv file extension to any plain text file regardless of whether or not it is a properly formatted CSV file. However, you won't be able to import invalid CSV files into your program without issues and many programs place limits on CSV imports, particularly on the size of the CSV file. Here's a breakdown of some actual and practical CSV limits:
CSV size limits: There is no CSV row limit, but many programs limit CSV import size. The Excel max CSV import is 1,048,576 rows. Google Sheets has a 100MB import limit and supports a maximum of 10 million cells. The Apple Numbers row limit is 1 million rows and 1,000 columns. If you need to open a large CSV file in a spreadsheet, try Row Zero. Row Zero can open million row CSV files on a free plan and a billion rows on Enterprise plans.
Many software programs like Salesforce and Netsuite have CSV import limits in the thousands or tens of thousands of rows.
No formatting or formulas: If you download a CSV from Excel or another program, you'll lose all formatting and formuals, since files are saved as plain-text values.
1 sheet only: Simliary, when you download as CSV in a spreadsheet, you'll only download the sheet you are currently on. CSV files do not support multi-sheet workbooks.
Lack of data validation or standards - CSV files lack built-in data validation, meaning there's no enforcement of the format's own constraints including commas, consistent row length, and proper formatting. This causes a lot of import errors and frustration. There are also no universally agreed-upon CSV standards. For example, there's isn't a standard for represent missing or null values in CSV files. Common practices include leaving the field blank or using specific placeholders like NA or NULL. This lack of standards, means CSV files may be imported differently by different programs.
Lack of data types: CSV files store all data as plain text. They do not support native data types like integers, floats, dates, or booleans. Any type conversion must be handled programmatically during parsing or processing. CSV files also cannot natively handle binary data like images or complex objects.
Limited scalability: As datasets grow, the linear nature of CSV files makes them less efficient for storage, querying, or manipulation compared to databases or other file formats like Parquet.
No hierarchical or relational data: CSV files are inherently flat and do not support hierarchical or nested data structures. Relationships between tables or records must be managed externally.
No built-in metadata: CSV files do not have headers for metadata (e.g., schema, data types, or units) and there is no standard mechanism for including additional metadata.
Alternatives to CSV format
While CSV files are very widely used for their simplicity there are better file formats for larger or more complex datasets including:
- Parquet or ORC for columnar storage of large datasets
- JSON, JSONL, or XML for hierarchical data
- TXT files for unstructured data
- XLSX files for spreadsheet formatting, formulas, and multiple sheets
- SQL databases for relational data
Conclusion
While the CSV file format is widely used and valued for it's simplicity, the format's lack of standards and data validation leads to a lot of errors. You can save any plain-text file as a .csv, whether or not it is a valid CSV file. This leads to issues when importing CSV files into programs and databases and makes troubleshooting CSV errors a challenge. If you need to check a csv file for errors, your best bet is to use a text edtior like Notepad++ or python to diagnose and correct the issue. If you need to open and edit a big CSV file that is too big for Excel and Google Sheets, try Row Zero. Row Zero is spreadsheet specifically engineered to handle big data and is free to try.