Remove Empty Rows and Columns from CSV

You exported data from a legacy system. You open it, and it looks like Swiss cheese.

• Rows 5, 6, and 7 are totally blank.
• Column F is empty for every single record.
• Some rows have data but are missing the ID.

This "sparse" data breaks import scripts. You need to condense it.

Removing Empty Rows

Method 1: Excel Filter

1. Select all data.
2. Data > Filter.
3. Click the dropdown on a key column (like ID or Name).
4. Uncheck (Blanks).
5. Note: This just hides them. To delete:
   • Select the blank rows (using Find & Select > Go To Special > Blanks).
   • Right-click > Delete Sheet Rows.

Method 2: Text Editor (Regex)

If you have truly empty lines (just \n): Find: ^\n (or \n\n) Replace: (nothing)

If you have lines with just commas ,,,: Find: ^,+$ Replace: (nothing)

Removing Empty Columns

Method 1: Excel

1. Look for columns that seem empty.
2. Use a formula to check: =COUNTA(F:F).
   • If result is 1 (just header) or 0, it's empty.
3. Right-click Column > Delete.

Method 2: Python (Pandas)

import pandas as pd

df = pd.read_csv('data.csv')

# Drop rows where ALL elements are missing
df.dropna(how='all', inplace=True)

# Drop columns where ALL elements are missing
df.dropna(axis=1, how='all', inplace=True)

df.to_csv('clean.csv', index=False)

The "Partially Empty" Problem

What if a row has some data but is useless? , , , , 2024-01-01

You need to define a Threshold. "Delete row if ID is missing."

In Pandas:

df.dropna(subset=['id'], inplace=True)

Summary

• Empty Rows: Use Excel Filter or Pandas dropna.
• Empty Columns: Check with COUNTA before deleting.
• Ghost Rows: Sometimes files have 1000 empty rows at the bottom due to Excel formatting. Always check the file end.

---

Too many blanks? HappyCSV can automatically strip empty rows and columns to compact your data.