skip to Main Content

Remove whitespace from CSV with PowerShell

How to remove whitespace from CSV file? Before we explain that, why do we want to do that? The reason is that a PowerShell script is not reading the values from the CSV file because of the whitespace. Remove leading and trailing spaces from CSV file with PowerShell to get the PowerShell script to work. In this article, you will learn how to remove whitespace from CSV file with PowerShell.

Introduction

Let’s have a closer look at what’s happening in the first place. We have a CSV file with a list of email addresses.

Remove whitespace from CSV file with PowerShell before

After we run the PowerShell script Add users to group with PowerShell, not all the users end up in the security group.

Remove whitespace from CSV file with PowerShell couple of users

Why did only three users get added to the security group and the other users didn’t?

Solution to whitespace in CSV file

After opening the CSV file, we can see what’s going on. There are empty spaces before and after the values. The blank spaces are known as whitespace.

Remove whitespace from CSV file with PowerShell before highlighted

A blank space in front of the value did not give any issues, but the whitespace after the value did.

An excellent way and what we recommend is to use quotation marks to surround the field.

Manually adding quotation marks to every field will take time. Is there another way to automate this task? Yes, with PowerShell. Let’s look at the next step how to use PowerShell to remove leading and trailing spaces from CSV file.

Remove whitespace from CSV with PowerShell

Run PowerShell ISE as administrator. Copy the below code and paste it into PowerShell ISE. Do you have the CSV file in another place? Change the import path on line 2 and the export path on line 10. In this example, it’s the folder path C:\Temp.

# Import the data from CSV file and assign it to variable
$Csv = Import-Csv "C:\Temp\Users.csv"

# Trim values in CSV file
$Csv | Foreach-Object {
    $_.PSObject.Properties | Foreach-Object { $_.Value = $_.Value.Trim() }  
}

# Export CSV
$Csv | Export-Csv C:\Temp\UsersNew.csv -NoTypeInformation

After we run the script, a new CSV file is generated and shows up in C:\Temp folder. Let’s open the file UsersNew.csv. There is no more white space in CSV file. Also, the quotation marks are added.

Remove leading and trailing spaces with PowerShell after

Let’s select all the text to see how it looks.

Remove whitespace from CSV file with PowerShell after highlighted

We can use the UsersNew.csv, and run the script add users to group with PowerShell.

Add trim values in PowerShell script

Do you like to add the trim values commands in the Add-ADUsers.ps1 script? See line number 7-10.

# Import AD Module
Import-Module ActiveDirectory

# Import the data from CSV file and assign it to variable
$Users = Import-Csv "C:\Temp\Users.csv"

# Trim values in CSV file
$Users | Foreach-Object {
    $_.PSObject.Properties | Foreach-Object { $_.Value = $_.Value.Trim() }  
}

# Specify target group where the users will be added to
# You can add the distinguishedName of the group. For example: CN=Pilot,OU=Groups,OU=Company,DC=exoip,DC=local
$Group = "Pilot" 

# Retrieve UPN
foreach ($User in $Users) {
    $UPN = $User.UserPrincipalName
    Get-ADUser -Filter "UserPrincipalName -eq '$UPN'" |

    # Add user to group
    ForEach-Object { Add-ADGroupMember -Identity "$Group" -Members "$_" } 
}

After we run the script, all the users are added to the security group.

Remove whitespace from CSV file with PowerShell all users

Did this help you to remove whitespace from CSV file with PowerShell?

Read more: Import CSV delimiter PowerShell »

Conclusion

In this article, you learned how to remove whitespace from CSV with PowerShell. The PowerShell script will not run against some of the fields due to formatting issues. In this case, whitespace is available. Run the script to trim whitespace with PowerShell. After that everything will work as you expect.

Did you enjoy this article? You may also like PowerShell remove quotation marks from a text file. Don’t forget to follow us and share this article.

ALI TAJRAN

ALI TAJRAN

ALI TAJRAN is a passionate IT Architect, IT Consultant, and Microsoft Certified Trainer. He started Information Technology at a very young age, and his goal is to teach and inspire others. Read more »

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top