How to Fix CSV Rows With Extra or Missing Columns Before an Import

A CSV import that rejects rows for having the wrong number of columns is almost always hitting an unquoted comma inside a value. One company name such as "Northwind, Inc." becomes two cells, every cell after it moves one place to the right, and the row is now longer than the header. This post explains the cause, shows how to repair it without guessing blindly, and says what to do before you verify or import the result.

Fix a broken CSV →

What the standard says

RFC 4180 describes the common CSV format. Two of its lines explain nearly every case here. First: "Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes." Second, that each line should contain the same number of fields throughout the file. An export that writes Northwind, Inc. without quotes breaks the first rule, and the result breaks the second. Importers that enforce the second rule refuse the file or skip the rows.

What a broken row looks like

Take a header of Name, Company, Email, Title. A good row has four cells. A row whose company name contains a comma has five: the name, "Northwind", " Inc.", the email, the title. The email now sits in the fourth column, under Title, and the title sits in a fifth column with no header.

The damage is not limited to the company cell. Everything after the comma is out of place, so a naive import may put an email into a title field or, worse, silently load the wrong value into the wrong column. That is why a repair has to be placed correctly, not just trimmed.

A row with too few cells has the opposite problem: a line break in the middle of a value, a truncated export, or a row where trailing empty cells were dropped.

How a safe repair works

For a row with extra cells, the tool tries each place the extra cells could have been joined back together, and keeps the one where the email column still holds an address. Because only one joining gives an email in the email column, that anchors the answer. When several places fit, a column named like Company is preferred. If none can be told apart, or there is no email column to anchor on, the row is marked as a guess so you can check it by eye.

For a row with too few cells, empty cells are added at the end. The tool cannot know whether the cells were lost at the end or in the middle, so it pads at the end and counts them.

If the guess is wrong, you can pick the column yourself from a list. Say every extra comma in your file is in the Address column; choose Address and every broken row is redone with it.

Rows that already match the header are left alone. Every repair is listed.

Step by step

1. Paste the CSV with the header row first, or upload the file.
2. Read the counts of rows with too many and too few cells and the list of repairs.
3. If the tool guessed the wrong column for the extra commas, choose the right one from the list.
4. Download the fixed CSV and try the import again.

The file is read and repaired in your browser. It is not uploaded to SimpleVerifier.

Other things that break an import

• A stray double quote can swallow the lines after it into one cell. The tool counts cells that contain line breaks so you can spot that. Fix a file with a stray quote at the source if you can.
• Garbled letters and hidden characters are a different problem; use the CSV encoding fixer and see fixing garbled characters in a CSV.
• Blank rows can make an importer stop or create empty contacts; see removing blank rows.
• Column names and order that your destination does not recognize can be changed with the CSV column editor. The CSV import formatter reshapes a file to a destination's layout, and cleaning CSV columns before import covers the habits.

The right order of operations

1. Fix the structure with the broken row fixer, so every row has the same number of cells.
2. Fix characters if letters are garbled.
3. Remove blank rows.
4. Verify the email column. Structure repair makes sure the right column is read; it says nothing about whether the address exists.
5. Import.

Verification matters after the repair because a repaired row can still hold a dead address. A row that was shifted by an unquoted comma may also have been sitting in a file that came from a scrape or an old export, which is where stale addresses live. Upload the Email column to /verify or use the check button under the tool's result. See also removing invalid emails from a CSV and verifying a CSV with the API if you do this on a schedule.

When to treat a repair as suspect

• Rows marked as guesses. Check them by eye.
• A file where a large share of rows was broken. If most rows are wrong, the export itself is the problem, and re-exporting with quoting turned on is better than repairing.
• A file with no email column. The anchor is missing, so every merge of extra cells is a guess.
• A file where cells contain line breaks. A stray quote may have merged several records.

Limits

• A merge is a guess when more than one column fits; the list marks those rows.
• It cannot tell whether a short row lost its cells from the middle or the end; it pads at the end.
• A file with a stray quote may need fixing at the source first.
• Only the first sheet or pasted text is read.

Cost

The fixer is free. Verifying the repaired list is where the paid step is: SimpleVerifier Pro is $39 a month, with the first 100,000 checks a month at full speed, then slower, never cut off. See pricing.

The short version

Unquoted commas shift cells and break imports. Repair by joining the extra cells where the email column still holds an address, check the guesses by eye, then verify the addresses before you import.

Verify the repaired list →