How to Remove Blank Lines from CSV Files Using PowerShell?

As a data analyst, I often work with large CSV files that contain valuable data. However, sometimes these files can have blank or empty lines, which can cause issues when importing the data into other systems or analyzing it. In this PowerShell tutorial, I will explain some simple and effective methods to remove blank lines from CSV files using PowerShell.

For all the examples and methods here, I am using the CSV file below. You can see it contains a few blank lines. If you want to try it, you can download this file and use it.

Now, let me show you different methods to remove blank lines from this CSV file.

Method 1: Using the Where-Object Cmdlet

In PowerShell, you can use the Where-Object cmdlet to filter data. We can use it to filter out blank lines from our CSV file. Here’s how:

Get-Content -Path "/Users/bijay/Downloads/customers.csv" | Where-Object { $_.Trim() -ne "" } | Set-Content -Path "/Users/bijay/Downloads/output.csv"

Let’s break down the command:

1. Get-Content -Path "/Users/bijay/Downloads/customers.csv" reads the contents of your CSV file.
2. Where-Object { $_.Trim() -ne "" } filters out any blank lines by checking if the trimmed line is not equal to an empty string.
3. Set-Content -Path "/Users/bijay/Downloads/output.csv" writes the filtered content to a new CSV file.

This method is simple and effective for most cases. It’s a great starting point if you’re new to PowerShell or just need a quick solution.

I executed the above PowerShell script using VS code in my macOS and you can see the exact output in the screenshot below:

Check out How to Use PowerShell Export-CSV cmdlet?

Method 2: Using the Import-Csv and Export-Csv Cmdlets

PowerShell provides the Import-Csv and Export-Csv cmdlets specifically for working with CSV files. We can use these cmdlets to remove blank lines during the import and export process. Here is the complete script to do this.

Import-Csv -Path "/Users/bijay/Downloads/customers.csv" | Export-Csv -Path "/Users/bijay/Downloads/output.csv" -NoTypeInformation

In this command:

1. Import-Csv -Path "/Users/bijay/Downloads/customers.csv" imports the CSV file into PowerShell as an array of objects.
2. Export-Csv -Path "/Users/bijay/Downloads/output.csv" -NoTypeInformation exports the array of objects back to a CSV file, automatically removing any blank lines in the process. The -NoTypeInformation parameter ensures that no additional type information is added to the output file.

This method is handy when performing additional operations on your CSV data within PowerShell before exporting it back to a file.

Check out How to Use PowerShell Import-Csv Cmdlet?

Method 3: Using Regular Expressions

Let me now show the third method.

You can use regular expressions to remove blank lines from your CSV files for more advanced scenarios. PowerShell supports regular expressions through the -replace operator. Here’s an example:

(Get-Content -Path "/Users/bijay/Downloads/customers.csv") -replace '(?m)^\s*\r?\n' | Set-Content -Path "/Users/bijay/Downloads/output.csv"

Let’s understand the command:

1. Get-Content -Path "/Users/bijay/Downloads/customers.csv" reads the contents of your CSV file.
2. -replace '(?m)^\s*\r?\n' uses a regular expression to replace any blank lines with an empty string. The (?m) modifier enables multiline mode, ^ matches the start of a line, \s* matches zero or more whitespace characters, and \r?\n matches the line ending.
3. Set-Content -Path "/Users/bijay/Downloads/output.csv" writes the modified content to a new CSV file.

Regular expressions can handle complex patterns in your data. However, they can be a bit more challenging to understand and maintain for those less familiar with regex syntax.

Check out Get the First and Last Line of a CSV File in PowerShell

Remove Blank Lines from CSV Files Using PowerShell Example

Let’s say you’re working on a project for a client in New York City. They’ve provided you with a large CSV file containing taxi trip data for the month of March. However, when you try to import the file into your database, you encounter an error due to a blank line in the file.

To resolve this issue, you can use one of the above mentioned methods. For example, using the Where-Object cmdlet:

Get-Content -Path "/Users/bijay/Downloads/NYC_TaxiTrips_March.csv" | Where-Object { $_.Trim() -ne "" } | Set-Content -Path "/Users/bijay/Downloads/NYC_TaxiTrips_March_Clean.csv"

This command reads the contents of the “NYC_TaxiTrips_March.csv” file, filters out any blank lines, and saves the cleaned data to a new file named “NYC_TaxiTrips_March_Clean.csv”. Now you can successfully import the cleaned file into your database and proceed with your analysis.

Conclusion

In this tutorial, I have explained how to remove blank lines from CSV files in PowerShell using different methods.

• Using the Where-Object Cmdlet
• Using the Import-Csv and Export-Csv Cmdlets
• Using Regular Expressions

You may also like:

• Convert CSV to HTML Table in PowerShell
• Export Table to CSV in PowerShell
• Convert JSON to CSV in PowerShell
• Get Unique Values from CSV Using PowerShell

Bijay Kumar

Bijay Kumar is an esteemed author and the mind behind PowerShellFAQs.com, where he shares his extensive knowledge and expertise in PowerShell, with a particular focus on SharePoint projects. Recognized for his contributions to the tech community, Bijay has been honored with the prestigious Microsoft MVP award. With over 15 years of experience in the software industry, he has a rich professional background, having worked with industry giants such as HP and TCS. His insights and guidance have made him a respected figure in the world of software development and administration. Read more.