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.

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

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.

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.

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

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 10-13.

# Start transcript
Start-Transcript -Path C:\Temp\Add-ADUsers.log -Append

# 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" 

foreach ($User in $Users) {
    # Retrieve UPN
    $UPN = $User.UserPrincipalName

    # Retrieve UPN related SamAccountName
    $ADUser = Get-ADUser -Filter "UserPrincipalName -eq '$UPN'" | Select-Object SamAccountName

    # User from CSV not in AD
    if ($ADUser -eq $null) {
        Write-Host "$UPN does not exist in AD" -ForegroundColor Red
    }
    else {
        # Retrieve AD user group membership
        $ExistingGroups = Get-ADPrincipalGroupMembership $ADUser.SamAccountName | Select-Object Name

        # User already member of group
        if ($ExistingGroups.Name -eq $Group) {
            Write-Host "$UPN already exists in $Group" -ForeGroundColor Yellow
        }
        else {
            # Add user to group
            Add-ADGroupMember -Identity $Group -Members $ADUser.SamAccountName -WhatIf
            Write-Host "Added $UPN to $Group" -ForeGroundColor Green
        }
    }
}
Stop-Transcript

The -WhatIf parameter is added in the script. If you run the script, nothing will happen in the environment. You will get an output showing what will happen. Remove the -WhatIf parameter from the above PowerShell script on line 40 and rerun the script.

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

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.