Trimming Whitespace with Powershell

A few days ago I was working with a client that was providing an export of data from Oracle. The file being produced was choking my SSIS package due to various formatting issues. After working with the client and getting a file that looked good to the naked eye I discovered that a large amount of whitespace on the end of each line was making things break at my end.

I need to import this file quickly so I decided to fix it myself. As usual Powershell was up to the task! Here’s the script I came up with. This script will process a file called test.csv located in e:\ and produce a new file called test2.csv with the trailing whitespace removed from each line.

$content = "";
$file = "e:\test.csv";
$count = 0;
Write-Progress -Activity "Processing file" -CurrentOperation "Line = 0" -PercentComplete 0 -Status "Starting" -Id 1;
$lines = Get-Content -Path $file;
$percent_complete = 0;

# trim line by line
foreach($line in $lines)
{
 	$line = $line.TrimEnd();
 	$content += "$line`n" # Add a newline
 	$count++;
 	$percent_complete = [int][Math]::Ceiling((($count / $lines.Count) * 100));
	 Write-Progress -Activity "Processing file" -CurrentOperation "Line = $count" -PercentComplete $percent_complete -Status "Running" -Id 1;
}

$content | Set-Content -Path "e:\test2.csv";
Write-Progress -Activity "Finished processing file" -CurrentOperation "All lines processed" -PercentComplete 100 -Status "Complete" -Id 1;
Sleep(5);

After this quick fix the file was easily imported!