Saving an Excel Sheet as CSV Without the Extra Commas

You save a sheet with four columns and three rows as a CSV, open the file in a text editor, and find six commas on every line and two blank rows at the bottom. The data is fine. The file is not what you meant to send. Excel exports the used range of the sheet, which is the rectangle from A1 to the last cell that has ever held anything: a value, a format, a border, a fill. Cells that look empty but carry some leftover formatting count, and each one turns into a field in the output. This is a common surprise and an easy repair once you know where to look. It belongs next to the other things a spreadsheet does to a file you thought you were just exporting, listed in opening CSV files in Excel and Sheets without damage.

What the file looks like

Here is the export of a sheet whose real data sits in A1:C3, in a workbook where someone once formatted columns D to F and rows 4 and 5:

id,name,qty,,,
1,Widget,4,,,
2,Gadget,7,,,
,,,,,
,,,,,

Every line has six fields, which is the width of the used range, and the last two lines are entirely empty fields. A loader that reads this sees six columns, three of them unnamed, and two rows of nothing. Pandas names the empty headers Unnamed: 3 and so on, a database COPY into a three-column table fails with an extra-data error (the PostgreSQL extra data on last column error), and a strict validator counts two missing rows.

You will not see the problem in Excel. A formatted blank cell looks the same as an untouched one, so the sheet appears to end where your data ends. The way to see what Excel believes is the keyboard shortcut Ctrl+End, which jumps to the last cell in the used range. On Mac it is Fn+Control+Right Arrow, or Command+End on some keyboards. If Ctrl+End lands on F5 and your data ends at C3, the file will have six columns and five rows.

Clean the range inside Excel

The fix has one trap, and it is the difference between a repair that holds and one that comes back.

Select the empty columns to the right of your data, for example columns D:F, by clicking the D header and shift-clicking F. Right-click and choose Delete. Then select the empty rows below the data, rows 4 to 5, right-click the row numbers and choose Delete. Press Ctrl+End again to check: it should now land on C3. Save the workbook as well as the CSV, because Excel only recomputes the used range when the file is saved.

Do not press the Delete key. That clears the contents and leaves the formatting, so the cells stay in the used range and the commas come back. Clear All under Home > Clear removes both, and also works. Deleting the whole row or column is the surer way, and it is the one that survives a conditional format or a stray border, which clear-contents does not touch.

A used range far beyond the data also tends to show up as a workbook larger than its contents explain, and the same cleanup shrinks it. If Ctrl+End jumps to a row in the tens of thousands on a sheet with a hundred rows of data, expect tens of thousands of empty lines in the export unless Excel trims them, and do not count on it doing so.

Save as UTF-8 and choose the right sheet

Use File > Save As, choose the location, and under Save as type pick **CSV UTF-8 (Comma delimited) (*.csv)**, not the plain "CSV (Comma delimited)". The second writes a legacy code page and replaces anything that does not fit with a question mark, a permanent loss; the difference is the subject of Excel's CSV UTF-8 versus CSV comma delimited. Two more things happen at this step.

Only the active sheet is saved. A workbook with three sheets gives you one CSV for the one on screen, and Excel reminds you with a warning that the other sheets will not be saved. To export all three, you repeat the save with each sheet active, giving each file its own name, or script the conversion (converting Excel to CSV without losing leading zeros).

Excel also warns that the workbook may contain features that are not compatible with CSV, which is its way of saying formatting, formulas and multiple sheets will be lost. Click Yes to keep the format. The file you save holds cell values as displayed or as stored, depending on the type. Formulas are saved as their results, and a number formatted to show 00123 is saved as the formatted text in current versions or as the raw number in others, so check a sample instead of trusting memory (why Excel drops leading zeros).

Trim a file that already has the commas

Sometimes the workbook is gone and you have the file. A short script removes the surplus without touching real data. It finds the rightmost column that holds any non-blank value, drops everything beyond it, and drops rows in which every cell is blank:

import csv, sys

src, dst = sys.argv[1], sys.argv[2]
with open(src, newline="", encoding="utf-8") as f:
    rows = list(csv.reader(f))

width = max((i + 1 for r in rows for i, c in enumerate(r) if c.strip()), default=0)
kept = [r[:width] for r in rows if any(c.strip() for c in r)]

with open(dst, "w", newline="", encoding="utf-8") as f:
    csv.writer(f).writerows(kept)
print(f"{len(rows)} rows x {max(len(r) for r in rows)} cols -> {len(kept)} rows x {width} cols")

python3 trim.py trailing.csv clean.csv
cat clean.csv

5 rows x 6 cols -> 3 rows x 3 cols
id,name,qty
1,Widget,4
2,Gadget,7

The width comes from the data and not from the header, which matters: a header row with six names and values only in the first three columns should keep all six, because the name carries meaning. The script above would drop three header names that sit above empty columns. If your header can be wider than the data, take the width from the header row instead (len(rows[0]) after stripping trailing blanks), and drop only columns that are blank in every row and unnamed in the header.

A quick sed 's/,*$//' is tempting and wrong. It removes every trailing comma on every line, including the legitimate case of a last field that is empty, so a row 3,Gizmo, would lose its third field and a loader would see a short row. The script reads the structure first.

Confirm the result

Open the saved file in a text editor, not in Excel, since Excel will happily display the commas as empty cells and hide the problem again. Check that every line has the same number of fields and that no line is all commas. The line count and the field count are the two numbers to compare; checking the row and column counts of a CSV shows a one-line check for both, and removing blank rows and stray footers covers the case where the extra lines carry a total or a note instead of nothing.

For a recurring export, do not rely on a person keeping the workbook clean. Put the check in the process: the file is read back, and any line that is entirely delimiters, or any column with an empty header and no data, fails the step with a message that names it.

When the extra commas are intended

A trailing comma is not always a mistake. If the last field of some rows is genuinely empty, the comma is the field separator and the row needs it, as in 3,Gizmo,. Removing it makes the row short. Fixed-layout files for older systems sometimes define exactly six columns, with the last three reserved, and a receiving program reads by position. In that case the blank columns are part of the format, and trimming them breaks the contract.

The practical test is a column count in the specification of whoever receives the file. If they expect six, send six. If nothing says, three columns with names beats six with three of them empty. The format is described in RFC 4180, which asks for the same number of fields on every line and says nothing about trimming, and Microsoft's own export instructions are the reference for the save steps.