How to remove rows where specified columns are blank in csv?


Easily remove rows that contain empty columns in CSV without using a formula. Only upload your CSV file and get results in seconds. Effortless & efficient processing - try it now!

How to remove rows where specified columns are blank in csv?

How to remove rows where specified columns are blank in csv?

Removing rows from a large file is not very simple. For Example, I have a CSV file with millions of lines that are storing access logs. Some lines need to be removed because the specified documents are blank. I need to remove these lines and do data analysis. How can I do it?

Method A:Excel or Google Spreadsheet

Yes, you can use Excel to remove rows where specified columns are BLANK. Here's how:

  • Open your CSV file in Excel.
  • Select the columns that you want to check for BLANK cells.
  • Click the "Data" tab in the ribbon.
  • Click the "Filter" button in the "Sort & Filter" group.
  • Click the dropdown arrow in the header of the selected column and uncheck the "Blanks" option.
  • Press "Ctrl + -" to delete the visible rows.
  • Save the file as a new CSV file.

Note: This method assumes that the BLANK cells in the specified columns are truly blank and not just spaces. You can use the "Find and Replace" feature to remove spaces before applying the filter.

Method B:Python

To remove rows where specified columns are BLANK in a CSV file, you can use a programming language such as Python. Here's one way to do it:

import pandas as pd

# Read the CSV file
df = pd.read_csv('input.csv')

# Remove rows with blank values in the specified columns
df.dropna(subset=['column1', 'column2'], inplace=True)

# Write the cleaned dataframe to a new CSV file
df.to_csv('output.csv', index=False)

Here's the code in Python:

Note: Replace 'input.csv' and 'output.csv' with the actual file names. Also, replace ['column1', 'column2'] with the actual column names you want to check for BLANK values.

The best way:Use QuickTran function

  • Visit our website and find the online tool.
  • Open your csv file by clicking the "Choose File" button and selecting the file you want to change.
  • Once the file has been uploaded, you can select the specified columns you want to be processed , and in seconds, you'll remove rows where all columns are BLANK.
  • You can then download the files and save it to your computer.
Meet our more Transformation tools
Transform data: Text, Date/Time, Location, Json, etc.