Empty lines and too many spaces, fixing formatting with PowerShell

You’ve received a file from your boss, but its formatting leaves much to be desired. There are extra spaces where they shouldn’t be, and it's time to bring in our trusty friend, PowerShell! Whether you’re on Windows, Linux, or macOS, this tool works seamlessly across all operating systems, allowing you to have your cake and eat it too. 🍰

The Scenario

Imagine you have a CSV file named listONames.csv that you need to use for some Active Directory tasks. However, the formatting issues might cause problems in your code, particularly within a foreach loop.

Here’s what the data looks like:

FirstName,LastName,SAMAccount
   Edward,Thomas,215ethomas  
Michael,Thomas,556mthomas 

      Mike,Jakison,369mjakison
Joe,Yaknow,786jyaknow

Time for a Fix!

First, we need to get the content from the CSV file into PowerShell to tackle these formatting issues.

PowerShell is intuitive with its cmdlets (small functions or methods) that follow a "Verb-Noun" structure, making it easy to understand what you’re trying to do. In this case, we want to retrieve content, so we start with "Get".

The cmdlet you need is Get-Content. Here’s how to use it:

Get-Content ./listONames.csv

When you run this command, PowerShell will display the contents of your CSV file on the screen.

Working with Objects

PowerShell treats everything as objects, which means each line of your CSV can be manipulated with built-in methods. To clean up those pesky spaces, we’ll trim them off.

Use the following command:

(Get-Content ./listONames.csv).Trim()

The parentheses () instruct PowerShell to execute the command within, and the dot . at the end indicates that we want to apply the Trim() method to the resulting object. This method removes any leading or trailing spaces from each line.

Removing Empty Lines

Next, we want to eliminate any empty lines that could disrupt your processing logic. While they might not "kill" a foreach loop, they can produce unexpected results.

You can filter out empty lines with the Where-Object cmdlet:

(Get-Content ./listONames.csv).Trim() | Where-Object { $_.Length -gt 0 }

In this command, $_ is a placeholder for the current object being processed in the pipeline. We’re using the Length property to check the number of characters in each line. The -gt operator checks if the length is greater than zero, effectively keeping only non-empty lines.

Saving the Cleaned Data

Finally, let's save the cleaned-up list into a new file. Use the following command to write the output to fixedListONames.csv:

(Get-Content ./listONames.csv).Trim() | Where-Object { $_.Length -gt 0 } | Set-Content ./fixedListONames.csv

Now, you can verify that the cleaned data has been saved correctly:

Get-Content ./fixedListONames.csv

You should see:

FirstName,LastName,SAMAccount
Edward,Thomas,215ethomas
Michael,Thomas,556mthomas
Mike,Jakison,369mjakison
Joe,Yaknow,786jyaknow

Conclusion

And there you have it! With a few simple PowerShell commands, you can clean up a CSV file and make it ready for further processing in Active Directory or any other application. Happy scripting! 🦸‍♂️