CSV has extra empty rows or columns? How to clean it
The file looks fine until you import it. Then the tool complains about blank records, your row count is off by hundreds, or there are columns with no header and nothing in them. Open the CSV in a text editor and you'll often see this at the bottom:
Ana,[email protected],Lisbon,,, ,,,,, ,,,,,
Why it happens
- Excel remembers formatting. If a cell far below your data was ever formatted, filled or had content deleted, Excel treats it as part of the "used range" and writes those rows as lines of commas.
- Trailing separators. Some exporters end every line with a comma, which creates an extra empty column.
- Blank lines from copy and paste, or from joining two exports together.
- Hidden or filtered content that was cleared but not deleted.
Fix it in Excel
- Press Ctrl+End. It jumps to the last cell Excel considers used. If that's far below or to the right of your data, you have stray cells.
- Select the empty rows below your data: click the first empty row number, then press Ctrl+Shift+Down. Right-click the row numbers and choose Delete. Clearing contents isn't enough; the rows must be deleted.
- Do the same for empty columns with Ctrl+Shift+Right.
- Blank rows scattered in the middle: select the column, use Home ▸ Find & Select ▸ Go To Special ▸ Blanks, then Home ▸ Delete ▸ Delete Sheet Rows. Careful: that removes every row where that one column is blank, even when other columns have data. Use a column that is always filled, such as an ID.
- Save, then press Ctrl+End again to confirm the used range shrank.
Fix it in Google Sheets
Select the empty rows, right-click, Delete rows. For empty columns, right-click the column letters and choose Delete columns. Then download as CSV; Sheets exports only the used range.
Fix it in a text editor
For trailing commas, use find and replace with regular expressions on:
,+$ replaced by nothing, but only when every row has the same
number of trailing commas, otherwise you will shift real empty fields.
Delete blank lines at the end by hand.
How to tell if it's causing real trouble
Empty trailing rows rarely matter for a quick look, but they matter to anything that counts: a database import that rejects a row with a missing required field, a script that stops at the first blank line, a mailing tool that reports hundreds of invalid contacts. Compare the number of data rows you expect with the number the importer reports. If it's higher by a round amount, empty lines are the usual explanation. The same applies to columns: an unnamed trailing column can shift a mapping by one position.
CSV Fixer's "Remove empty rows and columns" is on by default. It deletes
every row where every cell is empty, and every column with no data whose
header is blank (or was blank and got a placeholder name like
column_4). A column that has a real header but no data is
kept, because removing it could change your structure; delete it yourself
if you don't want it. Preview first, then download. Nothing is uploaded;
it all runs in your browser.