Updated 2026-10-21

CSV Not Importing Properly? Why Rows Merge and Columns Shift

When a CSV imports without an error but the data is wrong, the fault is almost always in the file's quoting or in a field count that changes partway down. The importer followed the bytes it was given. An unclosed quote makes the next records part of one field, a stray quote splits one field into two, and a row with one field too many either stops the import or gets discarded without a message.

Quick answer

Before you change any import setting, count three things in the raw file: the number of fields in the header, the number of fields in the longest data row, and the number of double quotes. If the field counts differ, the file is malformed and no importer will guess correctly. If the quote count is odd, one quote is unclosed and every record after it is being swallowed. Both checks take under a minute in a text editor, and they separate a bad file from a bad setting before you spend an hour on settings.

1. A quote that never closes merges the records after it

A quote opens a field. Until a second quote closes it, commas and line breaks inside it are data, not structure. Miss the closing quote and the file keeps one enormous field running to the end of the document, so the rows that should have followed become text inside it.

We built that case on 2026-10-21 and read it with Python 3.13.5. The file had a header and three data rows spread over four physical lines; the reader returned two records. The second record was the header plus one row, and the last field contained the text 2, 3,4 and 5,6 joined by CRLF sequences. Four data rows arrived as one. pandas is less forgiving on the same bytes and stops with ParserError: Error tokenizing data. C error: EOF inside string starting at row 1.

The fix is one character. Find the field that opens with a quote and does not close on the same logical record, then either close it or remove both quotes if the text does not need them. Text editors with CSV highlighting colour the quoted region, which makes the missing quote visible immediately.

2. A quote in the middle of an unquoted field adds a column

RFC 4180 section 2.5 is explicit: if fields are not enclosed with double quotes, then double quotes may not appear inside the fields. A value like he said "hi, there" breaks that rule twice over. The quote in the middle ends the field as far as a lenient reader is concerned, the comma inside what the writer thought was text becomes a separator, and the row gains a field that the header does not have.

Our test row came back as three fields instead of two: he said "hi and there". The record count was still correct, which is what makes this failure hard to spot: nothing is missing, the values have simply shifted one cell to the right from that row onward. In a spreadsheet you see a column of notes sitting under a numeric heading.

Proper quoting is the answer, and it has a precise form. RFC 4180 section 2.7 says a double quote inside a quoted field is escaped by doubling it, so the value becomes "he said ""hi, there""". Writing that by hand across a large file is where most people give up; a CSV writer does it automatically, every time.

3. The field count changes mid-file, and the importer picks a side

RFC 4180 section 2.4 asks that each line contain the same number of fields throughout the file, and that the header contain the same number of fields as the records. When that is violated, importers differ in how they fail, and the difference decides whether you lose rows.

• pandas stops with Expected 2 fields in line 3, saw 3 in our 2026-10-21 run on pandas 2.3.3. A hard error is the good outcome, because nothing is silently altered.
• The same call with on_bad_lines="skip" returned one of the two data rows. The import succeeds, the row is gone, and nobody is told.
• Spreadsheet importers often pad the short row with empty cells and keep going, which preserves the count and hides the defect until someone sums a column.

Fix the row that disagrees, not the setting. Find the line number in the error, open that line, and look for an unquoted comma in a value such as an address, a product name, or a number written with a thousands separator.

4. A trailing comma becomes a phantom column

The same RFC paragraph ends with a sentence worth memorising: the last field in the record must not be followed by a comma. A file saved from an export template that ends every line with a separator produces a header with a trailing comma and rows to match. Readers that follow the grammar add an empty final field, so a two-column file arrives with three columns. pandas labels it Unnamed: 2, which we saw in the same test run.

If the extra column is empty in every row, strip it before the import rather than after. Removing it afterwards works too, but any formula or join you wrote against the wrong column index will have to be redone.

5. Blank lines are dropped by one reader and kept by another

A blank line in the middle of the file is not a record under the format's grammar, but the two readers we tested disagree about what to do with it. Python's csv.reader returned three records for a header, one data row, a blank line and a second data row, and the middle entry was an empty list. pandas returned a single data row, because it skips blank lines by default.

The same line can therefore mean two different row numbers depending on the tool, and row 3 in your spreadsheet may be record 2 in the script. Align them by removing blank lines once, at the source, instead of translating row numbers in your head during a debugging session.

6. The import worked and the file was cut off at a cap

Some losses have nothing to do with syntax. Microsoft's import and export article states that you can import or export up to 1,048,576 rows and 16,384 columns, which is the worksheet grid rather than a property of CSV. When the row count in the destination is exactly 1,048,576, the file was read to the limit and the rest was never loaded.

Python has a separate ceiling inside the parser. csv.field_size_limit() returned 131,072 characters in our environment, and a single field of 140,000 characters raised Error: field larger than field limit (131072) until we raised the limit. One long text field, such as a scraped description or a JSON blob in a cell, is enough to stop an otherwise ordinary import. Our guide to CSV character limits covers that ceiling in detail.

The 60-second check before you import again

1. Open the file in a plain text editor, not a spreadsheet. A spreadsheet hides the quotes and newlines that cause all six failures above.
2. Look at the first line and count the commas outside quotes. Compare it with the widest data row.
3. Count the double quotes. An odd number means one field never closes.
4. Search for ," and ", where a quote is jammed against a separator with no escaping. Those are the rows that shift.
5. Re-emit the file with a CSV writer, then compare the row count of the new file with the row count of the old one. Equal counts mean the rewrite only normalised bytes.

If you want to see the structure without writing code, drop the file into the CSV analyzer and read the detected column count against the preview. For a file whose delimiter is the problem rather than its quoting, the delimiter converter rewrites it with consistent separators.

When the importer rejects the file instead of mangling it

A refusal is a different problem with a shorter list of causes. If the upload never starts, the file's extension, MIME type or size is the suspect, and our eight causes of an upload that fails covers those in order. If the data arrives in a single column, the delimiter was misread rather than the quotes mismatched, and why a CSV opens in one column walks through that case. If the values look like random symbols, the encoding is wrong and CSV file encoding explains which byte sequence each code page produces.

Sources

• IETF, RFC 4180: Common Format and MIME Type for CSV Files, section 2, items 1, 3, 4, 5, 6 and 7 (CRLF records; header fields should match record fields; each line should contain the same number of fields; quotes may not appear inside unquoted fields; fields containing line breaks, double quotes or commas should be quoted; an inner double quote is escaped by doubling it).
• Microsoft Support, "Import or export text (.txt or .csv) files" (you can import or export up to 1,048,576 rows and 16,384 columns; a .csv opens straight into the grid while a .txt forces the Import Text Wizard).
• Python 3 documentation, csv module, csv.field_size_limit() (parser field ceiling, default 131,072 characters).
• Our own measurements, 2026-10-21, Python 3.13.5 with pandas 2.3.3, scripts and output kept under seo_geo_ops/nc_measure_20261021/: an unterminated quote turned four physical lines into two records; a mid-field quote turned one field into two; a three-field row against a two-field header raised ParserError: Error tokenizing data. C error: Expected 2 fields in line 3, saw 3 while on_bad_lines="skip" kept one of two rows; a trailing comma produced Unnamed: 2; pandas read one data row wherecsv.reader returned three records because of a blank line; csv.field_size_limit() was 131,072 and a 140,000-character field raised Error: field larger than field limit (131072).

Related reading

Normalise the file before the next import

A rewrite with a real CSV writer fixes three of the six causes above at once: quotes get escaped, fields that need quoting get them, and the separator is applied consistently. The cleaner below runs in your browser, so the file never leaves the machine.

Frequently asked questions