Here is a solution that should be more robust (and propably faster) than replacing characters from the CSV file.
It uses the .NET TextFieldParser class from Microsoft.VisualBasic assembly. It has a TrimWhiteSpace attribute which, when set to $false, preserves any leading and trailing whitespace of each field, even if the field is not enclosed in double quotation marks.
I've encapsulated the .NET code in a function named Import-CustomCsv. It supports some additional options of TextFieldParser through various parameters.
Function Import-CustomCsv {
[CmdletBinding()]
param (
[Parameter(Mandatory, ValueFromPipeline)] [String] $Path,
[String[]] $Delimiter = ',',
[String[]] $CommentTokens = '#',
[switch] $TrimWhiteSpace,
[switch] $HasFieldsEnclosedInQuotes
)
# Load Visual Basic assembly if necessary. Seems to be required only for PS 5.
if (-not ([System.Management.Automation.PSTypeName]'Microsoft.VisualBasic.FileIO.TextFieldParser').Type) {
Add-Type -AssemblyName 'Microsoft.VisualBasic, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'
}
# Create a CSV parser
$csvParser = New-Object Microsoft.VisualBasic.FileIO.TextFieldParser $Path
try {
# Set CSV parser options
$csvParser.SetDelimiters( @($Delimiter) )
$csvParser.CommentTokens = @($CommentTokens)
$csvParser.TrimWhiteSpace = $TrimWhiteSpace
$csvParser.HasFieldsEnclosedInQuotes = $HasFieldsEnclosedInQuotes
# Read the header
$header = $csvParser.ReadFields()
while( -not $csvParser.EndOfData ) {
# Read current line fields, pointer moves to the next line.
$fields = $csvParser.ReadFields()
# Associate each field with its name from the header
$namedFields = [ordered]@{}
for( $i = 0; $i -lt $header.Count; $i++ ) {
$namedFields[ $header[ $i ] ] = $fields[ $i ]
}
# Finally convert fields to PSCustomObject and output (implicitly)
[PSCustomObject] $namedFields
}
}
finally {
# Make sure to close the file even in case of an exception.
$csvParser.Close()
}
}
Usage example: Parse CSV, preserving whitespace:
Import-CustomCsv test.csv -Delimiter ';'
Output:
ResourceId GermanTranslation EnglishTranslation Check
---------- ----------------- ------------------ -----
0 Deutscher Text English text OK
1 mit Leerzeichen with spaces OK
Usage example: Parse CSV, trimming whitespace (like Import-Csv):
Import-CustomCsv test.csv -Delimiter ';' -TrimWhiteSpace
Output:
ResourceId GermanTranslation EnglishTranslation Check
---------- ----------------- ------------------ -----
0 Deutscher Text English text OK
1 mit Leerzeichen with spaces OK
